On Sun, 3 Mar 2019 at 20:53, Keith Medcalf <kmedc...@dessus.com> wrote:
> My observation (on the current tip version 3.28.0) of Schrodingers
> Transactions is that if there is (for example) a transaction in progress
> and that is COMMIT or ROLLBACK, then the changes are either committed or
> rolled back and the explicit transaction is ended (that is, autocommit
> becomes True).
>
You kind of covered this in a previous email where you talked about "COMMIT
or ROLLBACK command completing successfully", but sqlite has a special case
around COMMIT which I think is worth mentioning in detail:
If COMMIT fails with SQLITE_BUSY, it means the EXCLUSIVE lock could not be
obtained within the configured timeout, because of other concurrent
activity on the DB. In this case, the transaction's changes are not
committed or rolled back -- it _remains open_. It is then up to the
programmer to decide whether to ROLLBACK and give up, or try to COMMIT
again at a later date.
> Statements which were in progress that were permitted to proceed (ie,
> where the next step did not return an abort error) continue with a read
> lock in place (ie, as if they were part of an implicit transaction on the
> connection) and once all those statements are completed, the read locks are
> released. You can BEGIN another transaction on the same connection (or
> another connection) and the locks will be escalated as you requested in the
> same fashion as would normally be expected for an in-progress implicit
> transaction.
>
Wait what? If I've understood correctly you're describing a situation where
statements outlive their transaction context? Something like:
sqlite3 *db; // initialised elsewhere
sqlite3_stmt *stmt;
int rc;
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1, &stmt, 0);
rc = sqlite3_step(stmt); // advance to first row
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
rc = sqlite3_step(stmt); // advance to second row?
...
sqlite3_finalize(stmt);
And the sqlite3_step() following the transaction acquires a new read-lock?
Or it prevents the COMMIT from dropping the read-lock?
It seems bizarre that this is even possible, so I may have misunderstood!
-Rowan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
