Thanks for the response Igor. I've added a few more introspection routines to my code to see what is going on while these two threads are working and it seems the following is happening:
Again, with two threads (A and B), each thread does the following: BEGIN IMMEDIATE TRANSACTION; (60,000 INSERT OR REPLACE statements into the same table) COMMIT TRANSACTION; thread A acquires the lock and starts to write. Meanwhile, thread B waits for the lock to be released in a SQLITE_BUSY loop. The strange thing is, however, that thread B's wait loop seems to somehow affect thread A's writing. After many thousand SQLITE_BUSY's from thread B, thread A's sqlite3_step() returns SQLITE_ERROR "SQLite logic error or missing database" and sqlite3_finalize() returns SQLITE_IOERR "disk I/O error". Each thread runs perfectly when run in turn (i.e. one after the other) but when I try to run them concurrently, the first thread seems always to experience this problem. The second thread always behaves normally, i.e. it eventually breaks out of it's SQLITE_BUSY loop when thread A has finished (but failed) and happily commits it's changes to disk. I'm really battling with this, how can thread B's SQLITE_BUSY loop be affecting thread A's writing? I'm using Visual C++ 7.1 on Windows XP and using the sqlite3.dll from www.sqlite.org. Again, any help would be appreciated. Mike. -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 21 October 2005 02:38 PM To: SQLite Subject: [sqlite] Re: Multithreading Question Michael J. Sviridov wrote: > I've got two thread's (with unique db handles), each thread does the > following: > > BEGIN EXCLUSIVE TRANSACTION; > (60,000 INSERT OR REPLACE statements into the same table) > COMMIT TRANSACTION; > > This works fine, as expected, one thread acquires the lock and the > other thread wait's for it to be released in a SQLITE_BUSY loop. > > My question: Is the same thing possible/safe with a DEFERRED or > IMMEDIATE transaction? > > When I use an EXCLUSIVE transaction all is well, but if I try to use a > DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from > sqlite3_step() on one or two of the INSERT OR REPLACE statements. This is normal for DEFERRED transaction. Your transaction starts as read-only, and is converted to read/write when the first modifying statement is executed. But it is possible that another transaction has already expressed an intent to write, and is waiting for all readers to clear. The only way out of this situation is to roll back the transaction and restart it from the beginning. Just retrying the last statement is pointless, and will result in the same error. SQLITE_ERROR should not happen for IMMEDIATE transaction, but it is possible for it to get SQLITE_BUSY on the first modifying statement (as well as on BEGIN statement). > My > indexes are also sometimes corrupted after this. This should not happen under any circumstances. If this is indeed the case, it is probably a bug and you should report it. Igor Tandetnik
