Re: [sqlite] unexpected sqlite_busy behaviour within transactions
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected sqlite_busy behaviour within transactions
On Saturday, 22 February, 2020 09:26, Andy Bennett 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 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 -- 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
Re: [sqlite] unexpected sqlite_busy behaviour within transactions
On 2/22/2020 7:50 AM, softw...@quantentunnel.de 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 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. 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
