Yes, sqlite CAN deadlock. It is a weakness of the locking model combined with a weakness in the transaction model. The big problem is that all write locks are elevatable. The possibility for deadlock can be eliminated if only one write lock at a time is elevatable (all others must be known to not elevate). SQLite could do this I suppose by treating an explicit transaction as an "elevatable" lock. Additional write locks can be allowed when an elevatable lock is held, but additional elevatable locks, and write locks, must be denied. Actually, that might not be a bad idea, since most of the time explicit transactions will try to elevate.
Fortunately, rather than REMAIN deadlocked, SQLite will tell the application to back up and try again. Unfortunately, you can't really write code like that. It also seems to not always detect the "deadlocked" scenarios, so you can end up waiting for a full timeout, then being told that the database is "busy". THEORETICALLY, you could put a loop around your transaction, and abort/retry any time you get a BUSY error, but that really just isn't much of an option in the real world. You can also try IMMEDIATE transactions, but again, not practical when concurrency is the objective in the first place. Table locking in the shared cache mode is really the way to go, although this only helps when all threads are in the same process, and still requires an absurd amount of extra code to get tolerable concurrency, especially if you have a long running process, like a sync. John -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Friday, October 16, 2009 12:38 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Exception writing to database from multiple processes On Thu, Oct 15, 2009 at 10:57:11PM -0400, Pavel Ivanov scratched on the wall: > You're definitely talking about some bug in your application or some > misunderstanding about how SQLite should work. SQLite by itself never > causes any deadlocks. As I understand it, that's not exactly true. SQLite can and does deadlock. It also tries to detect when this is happening and encourage the application to break the deadlock. This depends on the applications' cooperation, however. From <http://www.sqlite.org/c3ref/busy_handler.html> : Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. This implies that SQLite can figure out what is going on, but will not automatically rollback a transaction and break the deadlock by itself. That also implies that if an application goes into an infinite "try again" loop whenever it gets an SQLITE_BUSY return code, the deadlock may persist. The page <http://www.sqlite.org/lang_transaction.html> is clear that this is the expected behavior, on behalf of the application: "It is recommended that applications respond to the errors listed above [including SQLITE_BUSY] by explicitly issuing a ROLLBACK command." In short, you can poke at a SQLITE_BUSY state for a bit, but fairly soon you should give up and back all the way out. If you don't, a deadlock is possible. But that would be considered an application bug, not an SQLite bug. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
