http://www.sqlite.org/lockingv3.html
where A3 means the processes 3rd connection.
A1 - Shared (select)
B2 - Pending (insert)
A3 - Shared (select)
Looks like a deadlock at A3. B2 has a pending Lock and can not make further
progress until A1 completes. But A3 is now waiting on B2.
Either finish the A1 transaction before calling foo() or run foo() using the
same connection as A1. Not really sure why within the same thread you ever need
more than 1 connection.
Ben Mann <[EMAIL PROTECTED]> wrote: 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
#include
#include
#include
#include
#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]
-----------------------------------------------------------------------------
