On 10/18/2015 10:27 PM, Florian Weimer wrote: > 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.
The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset() on the UPDATE is not a new error - it has just saved the error code from the error that occurred during the sqlite3_step() call in the previous transaction. So technically the error did not occur within the BEGIN IMMEDIATE transaction. > > 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. I don't quite understand this. Preparing an SQL statement may read the database (in order to read the schema). But calling sqlite3_prepare() doesn't leave the statement in a "partially executed" state. So if you do: sqlite3_prepare(UPDATE); BEGIN; sqlite3_prepare(SELECT); sqlite3_step(UPDATE); then I guess you might get an SQLITE_BUSY_SNAPSHOT error (if the prepare() call actually needed to read the database), but if you do: sqlite3_prepare(SELECT); sqlite3_prepare(UPDATE); BEGIN; sqlite3_step(<whichever>); then I don't think you can get an SQLITE_BUSY_SNAPSHOT error. Dan.
