Re: unexpected sqlite_busy behaviour within transactions

Hi,

>> A busy_timout is honored by write commands outside of 
>> transactions and by single write commands inside transactions, 
>> but not if preceded by a read command. I did not find this 
>> behaviour in the documentation, thus it might be a bug.
>
> It's documented in the description of sqlite3_busy_handler: 
> https://sqlite.org/c3ref/busy_handler.html . Look for a 
> paragraph mentioning "deadlock".

Here's my understanding as I initially struggled to understand the nauance 
of the supplied documentation link.


>>    sqlite3 test.db --init sqlitetest.init "BEGIN TRANSACTION; SELECT * FROM t; INSERT INTO t VALUES(1); COMMIT;"

This returns SQLITE_BUSY after the SELECT statement and before the INSERT 
statement. This is when the SHARED lock needs to be upgraded to a RESERVED 
lock.

At this time, the IMMEDIATE transaction has a RESERVED lock that it will 
need to upgrade to an EXCLUSIVE lock in order to commit successfully. 
RESERVED locks can only be held by one transaction at a time but allow 
other processes to read the database.

This other process has called "BEGIN IMMEDIATE TRANSACTION" and 
https://sqlite.org/rescode.html#busy says

"The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it 
succeeds, then SQLite guarantees that no subsequent operations on the same 
database through the next COMMIT will return SQLITE_BUSY." 

As I understand it, this means that the only way this transaction will fail 
to commit is if the statements executed within it lead to some kind of 
constraint violation. i.e. it wont abort due to the actions of other 
proceses.

So that it can commit successfully this process will need to first upgrade 
its RESERVED lock to a PENDING lock on its way to an EXCLUSIVE lock.

The PENDING lock means that no new processes will be granted SHARED locks.
When all the existing SHARED locks have disappeared, the PENDING lock can 
be upgraded to an EXCLUSIVE lock.


This is how the deadlock happens: the DEFERRED transaction cannot upgrade 
its SHARED lock to a RESERVED lock because the other transaction already 
has a RESERVED lock, so it must wait. The IMMEDIATE transaction can upgrade 
its RESERVED lock to a PENDING lock but will be unable to upgrade that to 
an EXCLUSIVE lock until the DEFERRED transaction has released its SHARED 
lock.

We have a deadlock because the two transactions are waiting for each other.

Therefore, the DEFERRED transaction recieves SQLITE_BUSY straight away so 
that the IMMEDIATE transaction does not have to wait for it's busy handler 
to expire before getting an EXCLUSIVE lock.


Of course, this is also good for the integrity of the DEFERRED transaction. 
If the IMMEDIATE transaction ends up commiting then it may change the data 
that the DEFERRED transaction has already read from the database and based 
its subsequent calculations on. Allowing this would be bad for the 
integrity of that transaction. This is how the SERIALISABLE property of 
SQLite is provided.




Further reading:

https://sqlite.org/c3ref/busy_handler.html
https://sqlite.org/rescode.html#busy
https://sqlite.org/lang_transaction.html#immediate
https://www.sqlite.org/lockingv3.html





Best wishes,
@ndy

-- 
[email protected]
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users