"Edzard Pasma" <edz...@volcanomail.com> writes: > --- nikol...@rath.org wrote: >> "Igor Tandetnik" <itandet...@mvps.org> writes: >>> Nikolaus Rath <nikol...@rath.org> wrote: >>>> I am accessing the same database from different threads. Each >>>> thread >>>> has its own connection. I have set the busy timeout for each >>>> connection to 5000 milliseconds. >>>> >>>> However, in some testcases I still get SQLITE_BUSY errors from >>>> sqlite3_step. Moreover, the whole testcases run in much less than >>>> 5 >>>> seconds, to apparently sqlite does not even try to wait for the >>>> lock >>>> to disappear. >>> >>> You are getting a deadlock. The scenario goes like this: thread A >>> runs a >>> transaction that starts as a reader (with a SELECT statement) but >>> later >>> becomes a writer (by executing INSERT, UPDATE or DELETE). Thread B >>> also >>> runs a transaction like this, or a simple writer transaction. Then >>> the >>> following sequence of events occurs: >>> >>> 1. Thread A starts as reader and takes a shared lock >>> 2. Thread B starts as writer, takes a pending lock and waits for >>> readers >>> to clear. >>> 3. Thread A tries to become a writer and promote its lock to >>> reserved, >>> but can't because there's already a writer on the database. >>> >>> The two threads deadlock. No amount of waiting by either thread >>> would >>> get them out of the impasse: the only way out is for one of the >>> threads >>> to roll back its transaction and start from scratch. When SQLite >>> detects >>> this situation, it returns SQLITE_BUSY immediately, without calling >>> the >>> busy handler (because, again, waiting won't help any). >>> >>> To avoid the possibility of a deadlock, start your >>> reader-turning-writer >>> transactions with BEGIN IMMEDIATE (this essentially makes the >>> transaction a writer right away). >> >> Ah, I see. I expected that a deadlock would actually result in both >> threads hanging forever, rather than SQLite detecting it and >> abandoning >> immediately. The later is of course even better once you know about >> it. >> Thanks for the explanations! I should be able to fix my problem >> now.. > > Hi, > > Just in case it appears difficult to fix, I like to suggest to try > using shared cache mode. The shared cache locking model does not have > this particular deadlock situation. I'm assuming that the database is > accessed from within a single process only.
Thanks for the idea. But after reading http://www.sqlite.org/sharedcache.html it seems to me that to avoid the above deadlock, I would not only need to enable shared cache mode but also read-uncommitted. Is that right? I'm hesitating a bit to do that, because I'm not sure what the "[read-uncommited] can lead to inconsistent query results" phrase on the above page may imply. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
