tx, conn: wait for a shared-cache lock in BeginTx, Commit and Rollback, and end such a wait with the context

Refs #192.

With cache=shared, a second connection meets SQLITE_LOCKED_SHAREDCACHE (262) rather than SQLITE_BUSY, and busy_timeout does not apply to that code. The statement path waits for it through sqlite3_unlock_notify (conn.step, conn.prepareV2), so ExecContext(ctx, "BEGIN IMMEDIATE") waited. BeginTx, Commit and Rollback go through tx.exec, which called sqlite3_exec directly, as its TODO noted, so they skipped the wait and failed at once. On v1.60.1, with a write lock held by another connection, BeginTx with _txlock=immediate failed in no time with database table is locked (262), while the executed BEGIN IMMEDIATE waited. Without cache=shared both paths waited for busy_timeout and returned SQLITE_BUSY. Preparing any statement, COMMIT and ROLLBACK included, also meets 262 while another connection is changing the schema, so Commit of even an empty transaction failed at once during another connection's CREATE TABLE.

The wait also ignored the context: ExecContext with a 1 s deadline waited 5 s, until the other transaction ended, and only then returned context deadline exceeded.

Changes

  • tx.go: tx.exec prepares and steps its statement like any other, so a transaction's BEGIN, COMMIT and ROLLBACK wait like an executed statement. It still reports a statement that ran as a success when the context is done just after, as sqlite3_exec did, so that a BEGIN that took effect reaches database/sql as a transaction, which it then rolls back for the done context.
  • conn.go, sqlite.go, mutex.go: the connection records the context of the call in progress (waitCtx), set and restored by interruptOnDone and by newStmtContext, which starts no watcher of its own. retry watches that context with context.AfterFunc only while it waits, and returns ctx.Err() itself; AfterFunc runs at once for a context already done. The mutex is unlocked once, by whichever of the unlock-notify callback and the context comes first. After the wait, retry waits for the AfterFunc function if it has started, then calls sqlite3_unlock_notify(db, 0, 0), which cancels the callback if it has not fired and, since SQLite invokes callbacks and handles that call under the same mutex, returns only once no callback is running, and only then frees the mutex.
  • conn.go: PrepareContext and BeginTx with a context that is already done return its error, as ExecContext and QueryContext do; on master they prepared, or began a transaction that database/sql then rolled back.
  • CHANGELOG.md: two entries under the pending release.

Not changed

The wait is still not bounded by busy_timeout, as it was not for Exec; the context ends it. A wait in rows.Next, after QueryContext has returned, is not bounded by the context, as on master. SQLite discourages the shared cache; for an in-memory database shared by several connections, file:/name?vfs=memdb avoids it, and there busy_timeout works for both paths.

Testing

  • sharedcache_test.go: BeginTx waits for a write lock and for a schema lock and starts once it is released; Commit and Rollback of an empty transaction wait for a schema lock; BeginTx, ExecContext and QueryContext, waiting while preparing and while stepping, return context.Canceled once canceled while the lock is held, and the connection is usable afterwards; a context done before the wait begins ends it at once with context.Canceled or context.DeadlineExceeded, with nothing but retry watching it; the reproduction from the review, an authorizer that cancels the context while BEGIN is prepared, returns context.Canceled; a statement prepared with a context canceled afterwards and executed without one still waits; a wait ended by the lock's release, with its context canceled later, leaves a later wait undisturbed; PrepareContext and BeginTx with a done context return its error. The tests see the wait through the connection's state rather than by timing.
  • Eight pieces undone one at a time, each making a named test fail: retry ignoring the context, retry returning SQLITE_INTERRUPT instead of ctx.Err(), newStmtContext and interruptOnDone each not recording or not restoring the context, the early check in newStmtContext, and tx.exec back to sqlite3_exec.
  • go test of the package on darwin/arm64 (Go 1.26.3) and linux/arm64 (Go 1.27.1), and the new tests with -race -count=10 on both.
  • The benchmarks from the review against 0fc3f90d on darwin/arm64 (Apple M3), interleaved, -count=8: ExecContext "select 1" +1.2%, QueryRowContext "select ?" no change, BeginTx + ExecContext + Commit +4% with 2 more allocations, the same with context.Background(); the allocations are the statement path's in place of sqlite3_exec.
Edited by Ian Chechin

Merge request reports

Loading
Loading