Thanks for the input Ned--I thought there had to be more going on. After reading about BEGIN TRANSACTION some more, it seems like some times my retry strategy works (a reader gets SQLITE_BUSY because a writer is holding an exclusive lock, in which case retrying the same sqlite3_ call later in the reader would work--and does from what I've seen). But once the deadlock case is hit, one must abort/rollback as you point out.
Is there a way to detect the deadlock situation (will SQLite return SQLITE_LOCKED), or would it be better to retry a couple of times and then rollback? Is it best to bail out by closing the database handle, or calling sqlite3_prepare16 on a "rollback" statement and trying to execute it? Thanks again for your time. -----Original Message----- From: Ned Batchelder [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 11:54 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Concurrency handling question You can't simply loop on retrying the last sqlite3_* call. You need to rollback one of the transactions and retry the entire transaction. Your two threads are deadlocked because (roughly) each has acquired a lock that the other needs. One needs to release the locks it holds. Rolling back is the way to do that. --Ned. http://nedbatchelder.com -----Original Message----- From: Doug Nebeker [mailto:[EMAIL PROTECTED] Sent: Wednesday, 30 November, 2005 11:04 AM To: sqlite-users@sqlite.org Subject: [sqlite] Concurrency handling question I've written a C++ wrapper for SQLite which has been working great. Lately though I've started getting what might be deadlocks when running two threads against the same database. One thread is a reader and the other is a writer (at the moment, I'm getting 'database locked' errors, but that's after many hours of failures so not sure where it started). It's possible to have two or three readers and/or writers, but not more than that. Each thread has it's own database handle, however, I keep the database handle open for many calls (where each 'call' opens a transaction, executes SQL statements, then closes the transaction). I've read about concurrency, and it sounds like the best way to work is just to retry the last call if SQLITE_BUSY is ever returned, but that doesn't seem to be working for me in this case. I've stripped my main processing loop down to make it concise (it is not completely correct nor will it compile as shown, but it shows the flow). Can anyone see what I'm doing wrong? Is the fact that I keep the database handle open between calls and between retries the problem? If the retry loops finally fails, sqlite3_finalize gets called, sqlite3_close and then sqlite3_open16 to re-initialize the state. Thanks in advance for any insight. do { long prepareLoopCount = 0; PrepareLoop: if(SQLITE_BUSY == (res = sqlite3_prepare16(m_hDB, nextStatement, -1, &pStmt, &tail))) { if(prepareLoopCount++ < 200) { Sleep(300 ms); goto PrepareLoop; } } if((SQLITE_OK == res) && (NULL != pStmt)) { long stepLoopCount = 0; StepLoop: res = sqlite3_step(pStmt); if(SQLITE_BUSY == res) { if(stepLoopCount++ < 200) { Sleep(300 ms); goto StepLoop; } } else do other processing like fetching the rows.... } if(NULL != pStmt) { sqlite3_finalize(pStmt); pStmt = NULL; } } while(NULL != nextStatement) To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd. To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
