Have you thought of performing your own thread locking using the
equivalent of a semaphore? It is a much better method than some form of
busy wait and is not only less likely to unearth deep synchronisation
problems but will result in a faster running application.
Threads A and B wait on the semaphore before the BEGIN TRANSACTION and
signal it after the COMMIT or a possible ROLLBACK.
JS
Michael J. Sviridov wrote:
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
