>I have a database with one process (in one thread) writing to it, and
>another process (also in a single thread) reading from it only. All
>writes are done under BEGIN TRANSACTION IMMEDIATE. Sometimes, an END
>TRANSACTION fails with error 5, SQLITE_BUSY. The documentation says
>this should not happen:
The documentation specifies that this should not happen for a BEGIN IMMEDIATE TRANSACTION.
BEGIN TRANSACTION IMMEDIATE is no different than BEGIN DEFERRED TRANSACTION IMMEDIATE as far as I can tell, and starts a DEFERRED transaction.
In other words, the IMMEDIATE or DEFERRED or EXCLUSIVE keyword *must* appear immediately following the keyword BEGIN and the option specification is terminated by the appearance immediately following of the keyword TRANSACTION. Any token following the keyword TRANSACTION appears to be simple ignored (or perhaps not).
The parser appears to accept any syntax
BEGIN [IMMEDIATE|DEFERRED|EXCLUSIVE] TRANSACTION <meaningless token of zero effect here>;
I suspect this is/was intended to be the SAVEPOINT name, but it does not appear to be saved.
sqlite> begin transaction rightnow;
sqlite> begin rightnow transaction immediate;
Error: near "rightnow": syntax error
sqlite> begin deferred transaction CamelCaseCommentWord;
sqlite> begin immediate whatchamacallit;
Error: near "whatchamacallit": syntax error
sqlite> begin immediate transaction whatchamacallit;
sqlite> begin transaction immediate;
sqlite> rollback to immediate;
Error: no such savepoint: immediate
This is with the current tip of trunk (version 3.25.0) so YMMV. However, the transaction attribute, according to the syntax diagrams must appear between the keywords BEGIN and TRANSACTION, not following the keyword TRANSACTION.
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.