Christian Smith wrote:
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
According to DRH this scenario shouldn't happen. Begin should set a
flag, and the second begin will bug out because the flag is set. This is
what looks like happening in my scenario, and is definately wrong
behaviour. begin should be just that begin, mutually exclusive, unless
Dr Hipp want's to implement versioning based transaction schemes. Not,
begin "maybe i'm read, maybe i'm write, i'll decide later and woe betide
any one else who tries to write".
