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