On Saturday, 22 February, 2020 09:26, Andy Bennett <andy...@ashurst.eu.org> 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
