canceling Exec aborts entire transaction

[rittneje]

https://www.sqlite.org/c3ref/interrupt.html

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.

Imagine the following contrived situation (pseudo-code):

tx := db.Begin()
defer tx.Rollback()

ctx := context.WithTimeout(time.Second)
// this times out, so sqlite3_interrupt is called
tx.ExecContext(ctx, "INSERT INTO Foo VALUES (1)")

// this gets executed outside a transaction!
tx.Exec("INSERT INTO Foo VALUES (2)")

// no-op!
tx.Rollback()

This should result in the database being totally unmodified. However, as per the docs sqlite3_interrupt would have automatically aborted the explicit transaction. Thus the second insert is actually executed in an implicit transaction. (The Go standard library calls Exec on the Conn. There is no Exec on the Tx interface, since the assumption is that the Conn is stateful.)

Unfortunately, this is mostly caused by sqlite3_interrupt not really lining up with what context cancellation is supposed to mean. The best solution I can think of is to return sql.ErrTxDone (or something similar) if the actual SQLite transaction has been aborted, but the user has not called Rollback on the Tx yet. Note that this would apply to both the methods of Conn and the methods of Stmt.

[marshallthomas]

Do you mind if I take a crack at it? I'm new to open source programming, but I've also done collaborative work before on a python based sql database management program for three months. Granted, that was in Python, but I'm substantially more comfortable with C programming anyways.