My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?). I have found another source of such snapshot failures with WAL-mode databases.
I like to pre-compile my DML statements before starting transactions, mainly for the implied syntax check. (But perhaps there are also performance benefits, too?I haven't checked.) Here is what I did (if my tracing is correct). Unless mentioned otherwise, the operations succeed with SQLITE_OK. Open the database. Compile the UPDATE statement. Compile the ROLLBACK statement (used below). Start a BEGIN DEFERRED transaction (with a fresh statement). Reset the UPDATE statement. Step the UPDATE statement. -> this fails with SQLITE_BUSY_SNAPSHOT Reset/step/reset the pre-compiled ROLLBACK statement. Start a BEGIN IMMEDIATE transaction (with a fresh statement). -> this fails with SQLITE_BUSY_SNAPSHOT Start a BEGIN IMMEDIATE transaction (with a fresh statement). Reset the UPDATE statement. -> this fails with SQLITE_BUSY_SNAPSHOT After the last failure, iterating through the list of stattements does not show that the UPDATE statement is busy. This seems to contradict this piece of the documentation: | If X starts a transaction that will initially only read but X knows | it will eventually want to write and does not want to be troubled | with possible SQLITE_BUSY_SNAPSHOT errors that arise because another | connection jumped ahead of it in line, then X can issue BEGIN | IMMEDIATE to start its transaction instead of just an ordinary | BEGIN. The BEGIN IMMEDIATE command goes ahead and starts a write | transaction, and thus blocks all other writers. If the BEGIN | IMMEDIATE operation succeeds, then no subsequent operations in that | transaction will ever fail with an SQLITE_BUSY error. <https://www.sqlite.org/isolation.html> This happens with version 3.8.11.1 on Fedora 22. I don't know how risky pre-compiling UPDATE statement is. For SELECT statements, it is problematic because they can lead to persistent transaction failures with SQLITE_BUSY_SNAPSHOT because the pre-compiled and partitial executed statement is busy and will block creating a new transaction. I wonder if this can happen with UPDATE statements as well.
