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