Re: [sqlite] Reasons for SQLITE_BUSY

[Edzard Pasma]

--- nikol...@rath.org wrote:
> "Edzard Pasma"  writes:
>> --- nikol...@rath.org wrote:
>>> "Igor Tandetnik"  writes:
 Nikolaus Rath  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.

> Thanks for the idea. But after reading:
> //www.sqlite.org/sharedcache.html it seems to me that to avoid the
>  deadlock, I would not only need to enable shared cache mode but
>  read-uncommitted. Is that right?
>
> I'm hesitating a bit to do that, because I'm not sure what the
> "[read-uncommited] can lead to inconsistent query results" phrase on the
> above page may imply.

Hi again, 

I  don't believe that it is deadlock that you run into when using shared cache 
mode. Likely you face that a table gets locked for reading, immediately after 
the first write. The regular locking model is more tolerant here because 
reading is still possible until a writer starts spilling changes to disk. The 
read_uncommitted pragma is in my opinion a brilliant solution. It is however 
not the only option. As deadlock likely no longer occurs, you can now just wait 
for any lock. Only this must be handled in the application, as the timeout 
setting is not observed in shared cache mode. See ticket 
http://www.sqlite.org/cvstrac/tktview?tn=2010. 

The inconsistency when reading uncommitted data applies to the number of rows. 
Not to the consistency of the data within a row. This is perception, which may 
be too optimistic..

Best regards, Edzard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reasons for SQLITE_BUSY

[Nikolaus Rath]

"Edzard Pasma"  writes:
> --- nikol...@rath.org wrote:
>> "Igor Tandetnik"  writes:
>>> Nikolaus Rath  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.

Thanks for the idea. But after reading
http://www.sqlite.org/sharedcache.html it seems to me that to avoid the
above deadlock, I would not only need to enable shared cache mode but
also read-uncommitted. Is that right?

I'm hesitating a bit to do that, because I'm not sure what the
"[read-uncommited] can lead to inconsistent query results" phrase on the
above page may imply.

Best,

   -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

Re: [sqlite] Reasons for SQLITE_BUSY

[Edzard Pasma]

--- nikol...@rath.org wrote:
> "Igor Tandetnik"  writes:
>> Nikolaus Rath  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

Re: [sqlite] Reasons for SQLITE_BUSY

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath  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

Re: [sqlite] Reasons for SQLITE_BUSY

[Igor Tandetnik]

Nikolaus Rath  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

[sqlite] Reasons for SQLITE_BUSY

[Nikolaus Rath]

Hello,

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.

Can someone explain to me under which circumstances SQLITE_BUSY is returned
immediately, without calling the busy handler first? Hopefully this will
give me an idea of where to look for the problem...

In case it matters: I'm using the Python apsw wrapper compiled against
CVS SQLite from Jul 27th.

Best,

   -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