On Thu, 2007-08-30 at 13:25 +0800, Ben Mann 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.
Continually returns SQLITE_BUSY, right? Doesn't actually block?
Was the INSERT attempted by B inside a transaction? I'm guessing
so.
This isn't really a threading problem. Once connection B has the
PENDING lock, no new connections will be able to get the SHARED
lock required to read the database. The connection held by thread
A already has it's shared lock, so it can continue to read. Once
B commits or rolls it's transaction back, the PENDING lock will
be dropped and other connections, including new connections, will
be able to obtain new SHARED locks.
If I've understood things right...
Dan.
> 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]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
