SAVEPOINT, ROLLBACK TO, RELEASE, and Nested Units of Work

Learning outcomes

BEGIN transactions do not nest in SQLite. When an application needs smaller rollback boundaries inside a larger unit of work, SQLite provides savepoints. A savepoint is a named mark on the transaction stack, not a separately durable transaction.

Why BEGIN cannot simply be nested

If a transaction is already active, another BEGIN fails. This avoids pretending there are independent durable transactions inside one outer transaction. Savepoints instead create named rollback positions within the same transaction timeline.

BEGIN;INSERT INTO transfer_event VALUES('TX-200','FILTER-01','PLANT-A','PLANT-B',1);BEGIN;-- expected error: cannot start a transaction within a transactionROLLBACK;

Use SAVEPOINT when the real requirement is “I want to undo work back to here without abandoning everything.”

The savepoint stack mental model

BEGIN outer transaction
  |
  +-- SAVEPOINT batch_start        [mark A]
        |
        +-- item 1 succeeds
        |
        +-- SAVEPOINT item_2       [mark B]
              |
              +-- item 2 fails
              |
              +-- ROLLBACK TO item_2  <-- rewind after B
              +-- RELEASE item_2      <-- remove B
        |
        +-- item 3 succeeds
        |
        +-- RELEASE batch_start    <-- remove A, still inside BEGIN
  |
COMMIT  <-- only here is outer work durably committed

The most recently created savepoint is the first matching one reached during stack operations. SQLite permits duplicate savepoint names, so unique descriptive names are easier for humans even though the engine can resolve the stack.

SAVEPOINT, ROLLBACK TO, RELEASE: three different actions

Staged import: keep good items, reject one bad item

Suppose a maintenance import contains multiple independent notes. The whole file is one administrative batch, but one malformed row should not require retyping every earlier valid row. Savepoints let the importer try each item, undo only that item on validation failure, then continue—provided that this partial-success policy is actually acceptable to the business process.

DROP TABLE IF EXISTS import_reject;DROP TABLE IF EXISTS staged_note;CREATE TABLE staged_note(  import_id   TEXT PRIMARY KEY,  device_code TEXT NOT NULL,  severity    INTEGER NOT NULL CHECK(severity BETWEEN 1 AND 5),  note_text   TEXT NOT NULL CHECK(length(trim(note_text)) > 0));CREATE TABLE import_reject(  import_id TEXT PRIMARY KEY,  reason    TEXT NOT NULL);

BEGIN IMMEDIATE;SAVEPOINT import_batch;SAVEPOINT item_1;INSERT INTO staged_note VALUES('N-501','PUMP-007',2,'Seal inspected');RELEASE item_1;SAVEPOINT item_2;-- This fails because severity 9 violates the CHECK constraint.INSERT INTO staged_note VALUES('N-502','FAN-014',9,'Noise increased');-- Application catches the error and executes:ROLLBACK TO item_2;RELEASE item_2;INSERT INTO import_reject VALUES('N-502','severity outside 1..5');SAVEPOINT item_3;INSERT INTO staged_note VALUES('N-503','SENS-003',1,'Calibration verified');RELEASE item_3;SELECT COUNT(*) AS accepted FROM staged_note;   -- expected: 2SELECT COUNT(*) AS rejected FROM import_reject; -- expected: 1RELEASE import_batch;COMMIT;

The SQL transcript shows the control flow explicitly. A real CLI script cannot magically jump to ROLLBACK TO only when the preceding statement fails; application code or carefully designed shell scripting must inspect the error and choose the recovery path.

Why ROLLBACK TO is usually followed by RELEASE

ROLLBACK TO item_2 rewinds changes after the named savepoint but leaves that savepoint on the stack. If your recovery for that item is finished, RELEASE item_2 removes the mark so later code does not accidentally target it again.

BEGIN;CREATE TABLE IF NOT EXISTS savepoint_probe(x INTEGER);DELETE FROM savepoint_probe;SAVEPOINT a;INSERT INTO savepoint_probe VALUES(1);SAVEPOINT b;INSERT INTO savepoint_probe VALUES(2);ROLLBACK TO b;SELECT group_concat(x) FROM savepoint_probe; -- 1RELEASE b;INSERT INTO savepoint_probe VALUES(3);ROLLBACK TO a;SELECT count(*) FROM savepoint_probe;        -- 0RELEASE a;COMMIT;

Savepoint started without BEGIN

A savepoint may be the outermost transaction boundary. SQLite documents an outermost SAVEPOINT used outside BEGIN...COMMIT as behaving like BEGIN DEFERRED. Releasing that outermost savepoint empties the transaction stack and therefore commits.

SAVEPOINT request_700;INSERT INTO import_reject VALUES('N-700','demonstration');RELEASE request_700;-- Because it was the outermost transaction, RELEASE commits here.

This form can be useful in libraries that do not know whether a caller already owns an outer transaction, but API design must be deliberate: transaction ownership should not become invisible magic.

Application integration: exception handling makes the SQL semantics explicit

Higher-level libraries often implement “nested transactions” using savepoints. The following Python example uses engine-level SQL directly so the mapping is visible. It is illustrative; Chapter 15 covers driver engineering in depth.

import sqlite3con = sqlite3.connect("fieldnotes.db", isolation_level=None)try:    con.execute("BEGIN IMMEDIATE")    for import_id, device, severity, text in rows:        con.execute("SAVEPOINT one_item")        try:            con.execute(                "INSERT INTO staged_note VALUES(?,?,?,?)",                (import_id, device, severity, text),            )            con.execute("RELEASE one_item")        except sqlite3.IntegrityError as exc:            con.execute("ROLLBACK TO one_item")            con.execute("RELEASE one_item")            con.execute(                "INSERT INTO import_reject VALUES(?,?)",                (import_id, str(exc)),            )    con.execute("COMMIT")except Exception:    try:        con.execute("ROLLBACK")    finally:        con.close()    raiseelse:    con.close()

When partial recovery is the wrong policy

Checkpoint and bridge

Lesson 5 turns these primitives into transaction design: short boundaries, idempotent request handling, retry-safe state changes, failure injection, and a clear line between what SQLite can roll back and what lies outside the database.

Authoritative references

• SQLite — Savepoints.
• SQLite — Transaction.
• SQLite — ON CONFLICT.