Re: [sqlite] Reasons for SQLITE_BUSY

Nikolaus Rath <Nikolaus@xxxxxxxx> 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@xxxxxxxxxx
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users