--- nikol...@rath.org wrote: > "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: > //www.sqlite.org/sharedcache.html it seems to me that to avoid the > deadlock, I would not only need to enable shared cache mode but > 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. Hi again, I don't believe that it is deadlock that you run into when using shared cache mode. Likely you face that a table gets locked for reading, immediately after the first write. The regular locking model is more tolerant here because reading is still possible until a writer starts spilling changes to disk. The read_uncommitted pragma is in my opinion a brilliant solution. It is however not the only option. As deadlock likely no longer occurs, you can now just wait for any lock. Only this must be handled in the application, as the timeout setting is not observed in shared cache mode. See ticket http://www.sqlite.org/cvstrac/tktview?tn=2010. The inconsistency when reading uncommitted data applies to the number of rows. Not to the consistency of the data within a row. This is perception, which may be too optimistic.. Best regards, Edzard _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
