I have a multithreaded application which is exhibiting some unexpected
behaviour in the form of SQLITE_BUSY api return codes. I've traced
this back to a fairly simple way to model the problem, but I can't
seem to find any documentation which explicitly describes this issue.
I'll summarise the problem below, and I also have provided a short
console program which can be used to demonstrate the issue.
To my understanding, the behaviour is:
I have two threads, A and B.
1. A opens the database, and gets an sqlite3*
2. A runs a SELECT, which puts the database in a SHARED lock state
3. The OS interrupts A and allows B to run
4. B opens the database, gets an sqlite3*
5. B attempts an INSERT, but is blocked by the SHARED lock state.
6. B puts the database in a PENDING lock state.
7. The OS interrupts B and allows A to continue
8. A calls some function foo()
9. foo() opens the database, and gets an sqlite3*
10. foo() runs a SELECT, which blocks because the database is in a
PENDING lock state.
11. Because foo() is part of thread A, the system is deadlocked.
In my example code, disabling foo() and replacing it with
bar(sqlite3*), which has a pointer to A's sqlite3* will allow the
application to run normally.
So my questions are:
Is this a known feature?
Should functions called by a thread, which has its own DB handle, be
forbidden from opening a new DB handle?
Any answers much appreciated! This is also my first post here so
scathing criticisms and allegations of being a newbie are also welcome
>.<
Example code follows (beware, it's quick and dirty!); this version
will compile as a windows console app (remember to set OS_WIN and
THREADSAFE in compile options):
__________________________________________________
#include <windows.h>
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>
#include <assert.h>
#include "sqlite/sqlite3.h"
#define ALONGTIME 1000
void dotdotdot() { //simulate 'other' stuff
volatile int stupid = 0;
while(stupid < 10000) stupid++;
}
//use an open connection and select
void bar(sqlite3 *pdb) {
char sql[] = "SELECT * FROM B";
sqlite3_stmt* pstmt;
const char* szTail=0;
int ret;
printf("B");
assert(SQLITE_OK == (ret = sqlite3_prepare(pdb, sql, -1, &pstmt, &szTail)));
ret = sqlite3_step(pstmt);
assert(ret == SQLITE_DONE || ret == SQLITE_ROW);
sqlite3_finalize(pstmt);
}
//open a new connection and select
void foo() {
sqlite3 *pdb;
int ret;
assert(SQLITE_OK == (ret = sqlite3_open("testdb",&pdb)));
sqlite3_busy_timeout(pdb,ALONGTIME);
bar(pdb);
sqlite3_close(pdb);
}
//open a connection and select
DWORD WINAPI ThreadA(LPVOID) {
while(1) {
Sleep(7);
sqlite3 *pdb = 0;
char sql[] = "SELECT * FROM A";
sqlite3_stmt* pstmt;
const char* szTail=0;
int ret;
printf("A");
assert(SQLITE_OK == (ret = sqlite3_open("testdb",&pdb)));
sqlite3_busy_timeout(pdb,ALONGTIME);
assert(SQLITE_OK == (ret = sqlite3_prepare(pdb, sql, -1,
&pstmt, &szTail)));
ret = sqlite3_step(pstmt);
assert(ret == SQLITE_DONE || ret == SQLITE_ROW);
dotdotdot(); //simulate 'other code' that occurs here and may
use the query
foo(); //call a function that may query the db
//bar(pdb);
dotdotdot(); //simulate 'other code' that occurs here and may
use the query
sqlite3_finalize(pstmt);
sqlite3_close(pdb);
}
}
void TouchDB() {
static int counter = 0;
sqlite3 *pdb = 0;
char *sql;
char *err = 0;
int ret;
assert(SQLITE_OK == (ret = sqlite3_open("testdb",&pdb)));
sqlite3_busy_timeout(pdb,ALONGTIME);
printf("C(%d)", counter);
sql = sqlite3_mprintf("INSERT INTO C VALUES(%d);", counter++);
assert(SQLITE_OK == (ret = sqlite3_exec(pdb,sql,0,0,&err)));
sqlite3_close(pdb);
}
DWORD ThreadB(LPVOID) {
while(!kbhit()) {
Sleep(3);
TouchDB(); //touch the db
}
return 0;
}
void MakeDB()
{
sqlite3 *pdb = 0;
char sql[] ="DROP TABLE IF EXISTS A;"
"DROP TABLE IF EXISTS B;"
"DROP TABLE IF EXISTS C;"
"CREATE TABLE A(ID INTEGER PRIMARY KEY);"
"CREATE TABLE B(ID INTEGER PRIMARY KEY);"
"CREATE TABLE C(ID INTEGER PRIMARY KEY);"
"INSERT INTO A VALUES(1);"
"INSERT INTO B VALUES(2);";
char *err = 0;
int ret;
if (SQLITE_OK == (ret = sqlite3_open("testdb",&pdb))) {
sqlite3_busy_timeout(pdb,ALONGTIME);
sqlite3_exec(pdb,sql,0,0,&err);
sqlite3_close(pdb);
}
}
int main(int argc, char* argv[]) {
MakeDB(); //make the database the first time
CreateThread(NULL,0,&ThreadA,0,0,NULL);
ThreadB(NULL);
return 0;
}
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
