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
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
