--- 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. Regards, Edzard _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
