"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.. -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
