* Dan Kennedy: > 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.
D'oh! I mistakenly assumed using the sqlite3_prepare_v2() function would avoid that. Thanks. > So technically the error did not occur within > the BEGIN IMMEDIATE transaction. Right. I have changed my wrapper for sqlite3_reset() to ignore the error value and removed the compensation code from my transaction monitor. The tests that previously failed occasionally still succeed. >> 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. My concern is about sqlite3_step(UPDATE) without a following sqlite3_reset(UPDATE). Perhaps I should change my wrapper to unconditionally call sqlite3_reset() after DML-related sqlite3_step(), whether the stepping operation succeeded or not.
