Re: [sqlite] Reasons for SQLITE_BUSY

--- Nikolaus@xxxxxxxx wrote:
> "Igor Tandetnik" <itandetnik@xxxxxxxx> writes:
>> 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).
>
> 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@xxxxxxxxxx
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users