On 11/11/2015 01:19 AM, Florian Weimer wrote:
> * 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.
For a DML statement, I usually just do:
sqlite3_step(pStmt);
rc = sqlite3_reset(pStmt);
if( rc!=SQLITE_OK ){
/* Handle error */
}
I don't see why you would want to call step() but not reset() for a DML
operation. And since reset() always returns either the same or a more
specific error code, there isn't too much reason to ever catch the
return value of step(). IMO.
Dan.
