Savepoints

In the previous lesson, rollback had a single button: ROLLBACK returns to the start of the transaction and erases everything done along the way. That is too blunt for batch work. If the third record in a hundred-book inventory transfer fails, discarding the first two is wasted work; the transfer would have to start over.

Standard SQL defines intermediate stops inside a transaction for this. A savepoint is a name given to a specific point in the transaction; a rollback can target that name, and everything before it is preserved.

Setting an Intermediate Stop

There are three statements. SAVEPOINT name names the current point. ROLLBACK TO name erases every change made after that point, without ending the transaction. RELEASE name drops the point from the list, and that point can no longer be returned to.

sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT NOT NULL);
INSERT INTO book VALUES (1,'Lost Time','fiction');

BEGIN;
INSERT INTO book VALUES (11,'Island','fiction');

SAVEPOINT batch2;
INSERT INTO book VALUES (12,'The Well','fiction');
INSERT INTO book VALUES (13,'Summer Diary','essay');
ROLLBACK TO batch2;

INSERT INTO book VALUES (14,'Hourglass','poetry');
COMMIT;

SELECT id, title FROM book ORDER BY id;
SQL

┌────┬───────────┐
│ id │   title   │
├────┼───────────┤
│ 1  │ Lost Time │
│ 11 │ Island    │
│ 14 │ Hourglass │
└────┴───────────┘

Four rows were attempted to be added, three committed. Books 12 and 13, inserted after the batch2 point, were deleted; book 11, inserted before the point, was kept. The transaction continued after the rollback, and book 14 was added — ROLLBACK TO does not close the transaction.

This is the basis of the standard batch-transfer pattern: a savepoint is placed before each batch, released if the batch finishes without error, and returned to and the batch skipped if it fails. The work of earlier batches is preserved.

RELEASE Is Not a Commit

The word RELEASE is misleading; it makes nothing permanent. All it does is remove that name from the list of active points. Changes made after a point is released are still part of the transaction, and an outer ROLLBACK erases them too:

sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT NOT NULL);

BEGIN;
SAVEPOINT batch;
INSERT INTO book VALUES (11,'Island','fiction');
RELEASE batch;
SELECT 'after release' AS moment, COUNT(*) AS row_count FROM book;
ROLLBACK;
SELECT 'after rollback' AS moment, COUNT(*) AS row_count FROM book;
SQL

┌───────────────┬───────────┐
│    moment     │ row_count │
├───────────────┼───────────┤
│ after release │ 1         │
└───────────────┴───────────┘
┌────────────────┬───────────┐
│     moment     │ row_count │
├────────────────┼───────────┤
│ after rollback │ 0         │
└────────────────┴───────────┘

The row was still there right after the release; it disappeared once the transaction was rolled back. The only statement that makes something durable is COMMIT. Savepoints organize the structure inside a transaction; they do not change what the transaction promises to the outside.

This distinction also means savepoints are not “nested transactions.” A genuine nested transaction could commit the inner transaction independently of the outer one. There is no such thing with savepoints: if the outer transaction is rolled back, everything inside it goes with it.

Nested Points

Savepoints behave like a stack: each new point sits on top of the earlier ones. Returning to a point invalidates every point defined after it as well.

sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT NOT NULL);

BEGIN;
INSERT INTO book VALUES (11,'Island','fiction');
SAVEPOINT outer;
  INSERT INTO book VALUES (12,'The Well','fiction');
  SAVEPOINT inner;
    INSERT INTO book VALUES (13,'Summer Diary','essay');
  ROLLBACK TO inner;
  INSERT INTO book VALUES (14,'Hourglass','poetry');
ROLLBACK TO outer;
INSERT INTO book VALUES (15,'Distant Shore','fiction');
COMMIT;

SELECT id, title FROM book ORDER BY id;
SQL

┌────┬───────────────┐
│ id │     title     │
├────┼───────────────┤
│ 11 │ Island        │
│ 15 │ Distant Shore │
└────┴───────────────┘

Five inserts were made, two survived. ROLLBACK TO inner deleted only book 13; books 12 and 14 remained standing. Then ROLLBACK TO outer ran and erased everything defined after the outer point — books 12 and 14, plus the inner point itself. Book 11, added before the outer point, was kept, and book 15, added after the rollback, was committed.

Indentation only helps with reading; it has no meaning in SQL. The only thing that makes the stack behavior readable is choosing names that reflect their scope.

Returning to the Same Point More Than Once

ROLLBACK TO does not consume the point it returns to. The point stays in place and can be reused. This makes it possible to try several options in sequence inside one transaction:

sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT NOT NULL);
INSERT INTO book VALUES (11,'Island','fiction');

BEGIN;
SAVEPOINT trial;
INSERT INTO book VALUES (11,'Colliding','fiction');
ROLLBACK TO trial;
INSERT INTO book VALUES (12,'The Well','fiction');
ROLLBACK TO trial;
INSERT INTO book VALUES (13,'Summer Diary','essay');
RELEASE trial;
COMMIT;

SELECT id, title FROM book ORDER BY id;
SQL

Runtime error near line 6: UNIQUE constraint failed: book.id (19)
┌────┬──────────────┐
│ id │    title     │
├────┼──────────────┤
│ 11 │ Island       │
│ 13 │ Summer Diary │
└────┴──────────────┘

The trial point was returned to twice, and it worked both times. The first attempt hit a constraint violation; the second succeeded but was rolled back anyway; the third committed. None of the earlier attempts left a trace in the transaction.

Writing SAVEPOINT a second time with the same name creates a new point that shadows the old one; a rollback targets the most recently defined one. To avoid confusion, names should be chosen uniquely, or the point should be released once it is no longer needed.

The Batch Pattern

The full pattern is completed by application code that catches the error and makes the decision. The script below runs a three-batch transfer; the second batch has a colliding identifier:

cat > transfer.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                           genre TEXT NOT NULL);
         INSERT INTO book VALUES (11,'Island','fiction');`);

const batches = [
  [[21, 'The Well', 'fiction'], [22, 'Hourglass', 'poetry']],
  [[23, 'Summer Diary', 'essay'], [11, 'Colliding', 'fiction']],
  [[24, 'Distant Shore', 'fiction']],
];

const insert = db.prepare('INSERT INTO book VALUES (?, ?, ?)');
db.exec('BEGIN');
batches.forEach((batch, index) => {
  db.exec(`SAVEPOINT batch_${index}`);
  try {
    for (const row of batch) insert.run(...row);
    db.exec(`RELEASE batch_${index}`);
    console.log(`batch ${index}: accepted (${batch.length} rows)`);
  } catch (error) {
    db.exec(`ROLLBACK TO batch_${index}`);
    console.log(`batch ${index}: skipped — ${error.message}`);
  }
});
db.exec('COMMIT');

for (const s of db.prepare('SELECT id, title FROM book ORDER BY id').all()) {
  console.log(s.id, s.title);
}
JS
node transfer.mjs

batch 0: accepted (2 rows)
batch 1: skipped — UNIQUE constraint failed: book.id
batch 2: accepted (1 rows)
11 Island
21 The Well
22 Hourglass
24 Distant Shore

The second batch’s first row — book 23 — was valid and had been inserted; once the batch was rolled back, it went too. This is a deliberate choice in the pattern: a batch is accepted as a whole or rejected as a whole. If keeping valid rows is required, the batch size is reduced to one, at the cost of a savepoint per row.

In the script, RELEASE runs only for successful batches. The point for a failed batch stays in place, but since COMMIT closes every point, this does not create a leak.

Where It Is Used

Savepoints have three typical uses.

Batch transfers. A point is placed before each batch; if a batch fails, it is rolled back to that point, the record is written to an error log, and the transfer continues. The alternative is making each batch its own transaction, which loses the integrity guarantee across batches.

Steps that can be tried and abandoned. A costly path in a computation is tried first; if the result is unsuitable, it is rolled back and a different path is chosen. Because the whole transaction stays open, intermediate results never leak outward.

Reusable code at library boundaries. A function that does not know whether it is already inside a transaction cannot unconditionally write BEGIN — it would fail if a transaction is already open. Instead it opens a savepoint; if an error occurs, it rolls back only its own work and leaves the caller’s transaction untouched. This is what “nested transaction” support in most data-access libraries actually is.

On the cost side, savepoints are not free: the engine keeps extra bookkeeping so it can undo the changes made after each point. A transfer that opens a savepoint per row inflates its own bookkeeping more than the work itself. Point count is kept at the batch level, not the row level.

Summary

• SAVEPOINT names an intermediate stop inside a transaction; ROLLBACK TO erases everything after that point without closing the transaction.
• RELEASE is not a commit; it only drops the point from the list, and the only statement that makes something durable is COMMIT.
• Points behave like a stack: returning to one invalidates every point defined after it.
• ROLLBACK TO does not consume the point; returning to the same point more than once lets different options be tried.
• Because extra bookkeeping is kept per point, savepoints are used at the batch level, not the row level.

Next Step

Across these two lessons, only one connection reached the database; all a transaction ever saw was its own changes. In real systems, many connections read and write at the same time. The questions “can someone else see an uncommitted change” and “does the same query inside a transaction return the same result twice” only become meaningful then. The next lesson defines the concept of isolation that ties the answers to those questions to a level, and walks through the read anomalies the standard defines one by one.