unexpected sqlite_busy behaviour within transactions

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

unexpected sqlite_busy behaviour within transactions

Software
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.

To reproduce (in a linux terminal):
   sqlite3 ~/test.db "CREATE TABLE t(a);"
   echo '.timeout 5000' > ~/sqlitetest.init
   sqlite3 ~/test.db --init ~/sqlitetest.init "BEGIN IMMEDIATE TRANSACTION; SELECT * FROM t; INSERT INTO t VALUES(1); COMMIT;"
   sqlite3 ~/test.db --init ~/sqlitetest.init "BEGIN TRANSACTION; INSERT INTO t VALUES(1); COMMIT;"
   sqlite3 ~/test.db --init ~/sqlitetest.init "BEGIN TRANSACTION; SELECT * FROM t; INSERT INTO t VALUES(1); COMMIT;"
The three sqlite3 commands return immediately without error as the database is not locked.

Now lock the database in a second linux terminal
   sqlite3 ~/test.db
   sqlite> BEGIN IMMEDIATE TRANSACTION;

Then repeat the three sqlite3 commands in the first terminal. The first two commands take 5 seconds to return, as expected (due to busy_timeout). However, the last sqlite3 command returns immediately. Acquiring an exclusive lock prior to 'INSERT ...' apparently ignores the busy_timeout.

Best regards

Andreas

PS: I use SQLite version 3.27.2 2019-02-25 16:06:06 on raspbian / buster

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected sqlite_busy behaviour within transactions

Igor Tandetnik-2
On 2/22/2020 7:50 AM, [hidden email] wrote:
> 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".
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected sqlite_busy behaviour within transactions

Andy Bennett
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

--
[hidden email]
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected sqlite_busy behaviour within transactions

Keith Medcalf

On Saturday, 22 February, 2020 09:26, Andy Bennett <[hidden email]> wrote:

>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.

An immediate transaction may indeed "fail to commit".

Any statement up to and including the final COMMIT may return SQLITE_BUSY in the event that it is unable to upgrade its RESERVED lock to an EXCLUSIVE lock so that it may modify the database contents (for locking modes other than WAL -- WAL precludes the possibility of failure to obtain the required lock upgrade to EXCLUSIVE) if another connection holds a SHARED lock.  This may indefinitely prevent the transaction from committing.  Statements prior to the COMMIT may require EXCLUSIVE access to the database if they "spill their cache pages", for example.

However, no statement before the commit will fail for want of a RESERVED lock.  Statements before and including the COMMIT may fail (SQLITE_BUSY) for want of an EXCLUSIVE lock if they need to write to the database (COMMIT or spill pages) and the locking mode is not WAL.

Also note that in the original example, even though the INSERT returns SQLITE_BUSY immediately because not doing so is pointless (and might cause a deadlock) does not mean that retrying the statement will not succeed.  The original holder of the RESERVED lock may ROLLBACK their transaction in which case the upgrade of the SHARED lock to RESERVED will succeed and that transaction might be able to proceed without having to restart its transaction.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected sqlite_busy behaviour within transactions

Software
In reply to this post by Software
Dear Igor, Andy, Keith

Thank you for your patience to explain. Now very clear to me why it is not worth for the second process to honor the sqlite_busy handler, and instead returns immediately. In retrospect re-reading with your explanations in mind, I also understand the official documentation.

In my case I will use BEGIN IMMEDIATE TRANSACTION to circumvent the issue.

Best regards

Andreas
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users