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
