On Monday, 16 December, 2019 12:17, Jesse Rittner <rittneje+sql...@gmail.com> wrote:
> I have a few questions about how sqlite3_interrupt interacts with > explicit transaction operations. The docs say that "If the interrupted > SQL operation is an INSERT, UPDATE, or DELETE that is inside an > explicit transaction, then the entire transaction will be rolled back > automatically." Less than 3 minutes of testing and code analysis reveals: > 1. Can sqlite3_interrupt interrupt a call to BEGIN or its variants (BEGIN > IMMEDIATE and BEGIN EXCLUSIVE)? If so, is the transaction automatically > "rolled back" in this case? No. Setting transaction mode and autocommit mode is not an interruptible operation. See * below. >2. What about an interrupt during an explicit call to COMMIT or ROLLBACK? No. Re-enabling autocommit is not an interruptible operation. See * below. >3. What is the behavior with regard to savepoints? Will the outermost >transaction get rolled back if an operation gets interrupted? Yes. You can always determine whether a transaction is in process on a database connection by calling sqlite3_get_autocommit. sqlite3_interrupt sets an isInterrupted flag on a connection. When sqlite3_exec is called on a non-running statement (ie, for the first time) AND isInterrupted is set AND there are no running statements the isInterrupted flag is reset. The statement will then commence running if isInterrupted is clear, and not run if isInterrupted is set. When a VDBE program is executing it checks after certain opcodes if the isInterrupted flag is set and if it is the executing statement (and its containing transaction) are aborted. *Note that in order for an interrupt to have any effect, the VDBE program must be running. If the VDBE program is "paused" (as in the statement has returned a row and is waiting for the next sqlite3_exec) it will not be interrupted until you call sqlite3_exec on that statement the next time. The isInterupted flag will remain set until all "busy" statements on the connection have been interrupted. That means that if you start a statement executing and do not step it to completion (or reset it) the isInterrupted will remain in effect until you do, effectively precluding your ability to execute any statements whatsoever on that connection until the connection is no longer busy, as the purpose of the sqlite3_interrupt is to cancel pending operations on a connection and return it to a non-busy state. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
