Christian Smith <[EMAIL PROTECTED]> 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? >
If an application already has a shared lock and it gets an SQLITE_BUSY while trying to do its first write, it can safely assume that it is in a deadlock situation. Anytime you get an SQLITE_BUSY return on the first write attempt of a transaction, the best way to deal with it is to rollback and retry the entire transaction. If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT repeatedly until it works.) I will look into translating selected SQLITE_BUSY returns into SQLITE_DEADLOCK. This will be a slight change in the way things work, so I'll have to move to a new minor version number: 3.4.0. But perhaps that is worth doing. -- D. Richard Hipp <[EMAIL PROTECTED]>
