[EMAIL PROTECTED] wrote: > Bill King <[EMAIL PROTECTED]> wrote: > >> Christian Smith wrote: >> >>> 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? >>> >> 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. >> > > The flag that is set is private to each sqlite3 database > connection. So setting the flag in one connection should not > have any effect on any other connection. > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > I understand why I'm getting the deadlock now, lazy locking, (it's against the logical grain of transaction/locking, but that's a whole other argument) . Maybe this should be highlighted with big arrows in the information around multi-threading, as starvation/deadlock happens and often, especially if you get the scenario:
begin begin write (fail because of read lock) write ( busy deadlock) commit (fail, busy, read lock). commit (fail, busy, deadlock). Which is quite common in a multi-threaded environment. (Our situation is a multi-threaded directory scanner, inserting file entries into the database, so collision occurs often) The system descends into a sleep/wait scenario then, and with the increasing sleep periods, just... stops. It eventually recovers, but, if I'd known that had I used begin exclusive transaction in the first place to stop this deadlock scenario, I wouldn't have had the levels of grief that I have had. -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
