sqlite3_interrupt and explicit transactions

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_interrupt and explicit transactions

Jesse Rittner
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."
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?
2. What about an interrupt during an explicit call to COMMIT or ROLLBACK?
3. What is the behavior with regard to savepoints? Will the outermost
transaction get rolled back if an operation gets interrupted?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_interrupt and explicit transactions

Simon Slavin-3
On 16 Dec 2019, at 7:16pm, Jesse Rittner <[hidden email]> wrote:

> 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?
> 2. What about an interrupt during an explicit call to COMMIT or ROLLBACK?
> 3. What is the behavior with regard to savepoints?

1. If BEGIN is interrupted, no transaction is started.
2. This results in the equivalent of ROLLBACK
3. This results in the equivalent of ROLLBACK.  Savepoints are lost.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_interrupt and explicit transactions

Keith Medcalf
In reply to this post by Jesse Rittner

On Monday, 16 December, 2019 12:17, Jesse Rittner <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users