Hello again,
Attached is a test application which replicates the problem.
I expected the transactions to block each other exactly like they do in
the beginning (one connection successfully begins and the other receives
SQLITE_BUSY), but I didn't expect the blocked connection to never get
unlocked in the end. What's holding the lock on the database so that the
"begin" can't proceed?
Sample console output:
sqlite3_libversion: 3.7.13
sqlite3_sourceid: 2012-06-11 02:05:22
f5b5a13f7394dc143aa136f1d4faba6839eaa6dc
sqlite3_libversion_number: 3007013
sqlite3_threadsafe: 1
Creating thread 0
Creating thread 1
conn addr status query
--------- ------ -----
0x6a6278 Success PRAGMA journal_mode=wal;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6bd678 Success PRAGMA journal_mode=wal;
0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6a6278 Success BEGIN IMMEDIATE TRANSACTION;
0x6a6278 Success INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);
0x6a6278 Success COMMIT;
0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6bd678 Failed BEGIN IMMEDIATE TRANSACTION; (return code: 5)
(... And so on)
Thank you in advance,
Daniel
#include <iostream>
#include <sstream>
#include <stdlib.h>
#include <string.h>
#include <pthread.h>
#include "sqlite3.h"
#define DATABASE_PATH "/tmp/test.db"
#define THREAD_COUNT 2
struct thread_data
{
int thread_id;
};
struct thread_data thread_data_array[THREAD_COUNT];
void execSQL(sqlite3* db, std::string query){
bool keep_trying = true;
while(keep_trying){
char* szError=0;
int return_code = sqlite3_exec(db, query.c_str(), 0, 0,
&szError);
if(return_code == SQLITE_OK){
std::cout<<db<<"\tSuccess\t\t"<<query<<std::endl;
keep_trying = false;
}
else{
std::cerr<<db<<"\tFailed\t\t"<<query<<" (return code:
"<<return_code<<")"<<std::endl;
sleep(1);
}
}
}
void processSQLtest(sqlite3* db, int somedata){
std::stringstream ssInfo;
ssInfo<<" SELECT test_table_2.test1, test_table_2.test2";
ssInfo<<" FROM test_table_2, test_table_3 ";
ssInfo<<" WHERE test_table_2.test1 = test_table_3.test1";
ssInfo<<" AND test_table_3.test2 = "<<somedata;
ssInfo<<" LIMIT 1";
const char* szTail=0;
sqlite3_stmt* pVM;
int nRet = sqlite3_prepare(db, ssInfo.str().c_str(), -1, &pVM, &szTail);
if (nRet != SQLITE_OK)
{
exit(-1);
}
// std::cout<<db<<" Prepared: "<<ssInfo.str()<<std::endl;
nRet = sqlite3_step(pVM);
if (nRet == SQLITE_DONE)
{
// no rows
}
else if (nRet == SQLITE_ROW)
{
// at least 1 row
//insert some data
for(unsigned int i = 0; i < 10; i++){
execSQL(db, "BEGIN IMMEDIATE TRANSACTION;");
execSQL(db, "INSERT INTO test_table_1 (test1, test2)
VALUES ( 1, 2);");
execSQL(db, "COMMIT;");
}
}
else
{
nRet = sqlite3_finalize(pVM);
}
}
void *dbThread(void *threadarg)
{
sleep(1);
struct thread_data *my_data;
my_data = (struct thread_data *) threadarg;
sqlite3* db;
int nRet = sqlite3_open(DATABASE_PATH, &db);
if (nRet != SQLITE_OK)
{
//const char* szError = sqlite3_errmsg(db);
}else{
execSQL(db, "PRAGMA journal_mode=wal;");
//fake a thread processing loop using the opened database
connection
bool sql_processed = false;
while(1){
if(!sql_processed){
processSQLtest(db, my_data->thread_id+1);
sql_processed = true;
}else{
sleep(1);
}
}
//Close the db if the processing loop is done (which won't
happen in this example)
sqlite3_close(db);
}
std::cout<<"Thread "<<my_data->thread_id<<" exiting"<<std::endl;
pthread_exit(NULL);
return NULL;
}
void recreateDB(){
sqlite3* temp_db;
int nRet = sqlite3_open(DATABASE_PATH, &temp_db);
if (nRet != SQLITE_OK)
{
std::cout<<"Unable to open database"<<std::endl;
exit(0);
}
sqlite3_busy_timeout(temp_db, 10000);
std::string CreateTables;
CreateTables.append("DROP TABLE IF EXISTS test_table_1;");
CreateTables.append("DROP TABLE IF EXISTS test_table_2;");
CreateTables.append("DROP TABLE IF EXISTS test_table_3;");
CreateTables.append(" CREATE TABLE test_table_1 (");
CreateTables.append(" test1 INT NOT NULL, ");
CreateTables.append(" test2 INT NOT NULL ");
CreateTables.append(" );");
CreateTables.append(" CREATE TABLE test_table_2 (");
CreateTables.append(" test1 INT NOT NULL, ");
CreateTables.append(" test2 INT NOT NULL ");
CreateTables.append(" );");
CreateTables.append(" CREATE TABLE test_table_3 (");
CreateTables.append(" test1 INT NOT NULL, ");
CreateTables.append(" test2 INT NOT NULL ");
CreateTables.append(");");
char* szError=0;
sqlite3_exec(temp_db, CreateTables.c_str(), 0, 0, &szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_2 VALUES(1, 1);", 0, 0,
&szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_2 VALUES(2, 2);", 0, 0,
&szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_2 VALUES(3, 3);", 0, 0,
&szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_3 VALUES(1, 1);", 0, 0,
&szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_3 VALUES(1, 2);", 0, 0,
&szError);
sqlite3_exec(temp_db, "INSERT INTO test_table_3 VALUES(1, 3);", 0, 0,
&szError);
sqlite3_close(temp_db);
}
int main(int argc, char*argv[]){
std::cout<<"sqlite3_libversion: "<<sqlite3_libversion()<<std::endl;
std::cout<<"sqlite3_sourceid: "<<sqlite3_sourceid()<<std::endl;
std::cout<<"sqlite3_libversion_number:
"<<sqlite3_libversion_number()<<std::endl;
std::cout<<"sqlite3_threadsafe: "<<sqlite3_threadsafe()<<std::endl;
recreateDB();
pthread_t threads[THREAD_COUNT];
int rc;
unsigned int t;
pthread_attr_t attr;
pthread_attr_init(&attr);
pthread_attr_setdetachstate(&attr, PTHREAD_CREATE_JOINABLE);
for(t=0;t<THREAD_COUNT;t++) {
thread_data_array[t].thread_id = t;
std::cout<<"Creating thread "<<t<<std::endl;
rc = pthread_create(&threads[t], &attr, dbThread, (void
*)&thread_data_array[t]);
if (rc) {
std::cout<<"ERROR; return code from pthread_create() is
"<<rc<<std::endl;
exit(-1);
}
}
std::cout<<"\nconn addr\tstatus\t\tquery"<<std::endl;
std::cout<<"---------\t------\t\t-----"<<std::endl;
while(1){
sleep(1);
}
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
