On Wed, 7 Jun 2006, Jiri Hajek wrote:
However, right after fixing this, I found another problem. It certainly can
be my fault, but I don't see how could it be: If I don't use transactions,
multiple threads seem to proceed well, but then right after I add BEGIN and
COMMIT to some place, all threads lock eventually. I debugged it and found
that _all_ threads accessing SQLite are in a loop waiting for an action to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be
somehow my fault, or is it some kind of a dead-lock in SQLite?
If one transaction already has a read lock, and another transaction has a
reserved lock (trying to get a write lock), neither thread can get a write
lock. One of the transactions must abort.
Such a sequence might be (in order):
Transaction 1: BEGIN; SELECT ...
Transaction 2: BEGIN; DELETE ... (SQLITE_BUSY)
T1 : UPDATE ... (SQLITE_BUSY)
Both transactions are now deadlocked.
It would be nice if SQLite told us this. However, SQLite detects the
reserved lock and returns SQLITE_BUSY, telling niether transaction much
other than to try again. If a reserved lock is detected when trying to
promote an existing read lock, this is a deadlock situation and should
perhaps return an error code of SQLITE_DEADLOCK instead?
Christian
