hesela.devSQLite savepoint
A SQLite savepoint is a named transaction-stack boundary that permits partial rollback without necessarily ending the surrounding transaction.
SAVEPOINT can start an outer transaction or mark a point inside one. RELEASE removes the newest matching savepoint and any younger marks. Releasing an inner mark is not an independent commit: a later outer ROLLBACK can still undo its writes. ROLLBACK TO restores the database to the target savepoint state, removes younger marks and retains the target for reuse. A successful RELEASE that empties the transaction stack commits. Names need not be unique. [1]
BEGIN transactions do not nest. After an execution error, check the actual transaction state rather than assuming the enclosing transaction survived. [2] [3]
Four constructed cases run in both DELETE and WAL modes using explicit SQL through Python sqlite3. The row inserted inside the nested savepoint remained invisible to a second connection and disappeared after outer rollback; outermost RELEASE made it visible to that drained autocommit observer. Complete ordered steps and normal reopen results are available for replay. This is not a power-loss test. [5]
Commit and WAL checkpoint progress are separate. An inner RELEASE is not evidence of either outer completion or history reclamation. [4]
Outermost RELEASE does not refresh an existing WAL reader snapshot. In a two-case control, a reader that executed BEGIN and SELECT before release kept its old rows until ending the transaction; a fresh connection saw the committed row. BEGIN without an early read did not acquire the old snapshot. [7] [8]
Do not transfer savepoint lifetime rules between systems. ARIES section 5.2 consumes a rollback target, while SQLite retains it. This comparison concerns contracts, not an assertion that SQLite implements ARIES. [9]
Relations
Limitations
- A savepoint is not an independently durable nested transaction or a backup.
Citations
- SQLite: Savepoints, nesting and transaction-stack rules
- SQLite: Transactions and response to errors
- SQLite C API: Test For Auto-Commit Mode
- SQLite: Write-Ahead Logging, commit and checkpoint
- Hesela: eight SQLite savepoint schedules, complete observations
- SQLite: Isolation, WAL snapshots and read-transaction lifetime
- Hesela: WAL savepoint release and retained-reader control
- Mohan et al. ARIES. ACM TODS 17(1), 94-162 (1992), section 5.2, pp. 117-118. DOI 10.1145/128765.128770.
Further reading
Sources & caveats
- Source synthesis plus deterministic SQL fixtures on the recorded runtime; no crash, I/O-fault or physical-persistence experiment.