SQLite Savepoints: Nested Transactions with ROLLBACK TO

A Savepoint Is a Named Bookmark Inside a Transaction

A regular transaction is all-or-nothing: everything between BEGIN and COMMIT either lands together or gets thrown out together. That's usually what you want — but sometimes you'd like a finer grain. "Try this batch of changes; if it goes wrong, undo just that batch and keep the rest of the transaction alive."

That's what a savepoint is. You drop a named bookmark, do some work, and then either keep the work (RELEASE) or rewind to the bookmark (ROLLBACK TO).

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Boris', 100);

BEGIN;
  UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada';

  SAVEPOINT risky;
    UPDATE accounts SET balance = balance + 30 WHERE name = 'Nobody';
  ROLLBACK TO risky;

  UPDATE accounts SET balance = balance + 30 WHERE name = 'Boris';
COMMIT;

SELECT * FROM accounts;

Ada's debit and Boris's credit both stick. The mistaken update to Nobody was rolled back without losing the rest of the transaction.

The Three Commands

Three statements is the whole API:

• SAVEPOINT name — set a bookmark.
• RELEASE SAVEPOINT name — keep the work done since the bookmark; remove the bookmark.
• ROLLBACK TO SAVEPOINT name — undo everything since the bookmark; the bookmark stays in place so you can try again.

The word SAVEPOINT after RELEASE and ROLLBACK TO is optional — RELEASE risky and ROLLBACK TO risky both work.

CREATE TABLE log (msg TEXT);

BEGIN;
  INSERT INTO log VALUES ('start');

  SAVEPOINT step1;
    INSERT INTO log VALUES ('step 1 done');
  RELEASE step1;

  SAVEPOINT step2;
    INSERT INTO log VALUES ('step 2 attempt');
  ROLLBACK TO step2;
    INSERT INTO log VALUES ('step 2 retry');
  RELEASE step2;
COMMIT;

SELECT * FROM log;

The step 2 attempt row never existed as far as the final database is concerned. Everything else lands.

Savepoints Without an Outer Transaction

Here's a small twist: you can issue SAVEPOINT without a BEGIN first. SQLite quietly opens a transaction for you, and the outermost savepoint plays the role of the transaction itself. RELEASE on that savepoint commits, ROLLBACK TO rewinds without committing.

CREATE TABLE notes (text TEXT);

SAVEPOINT outer;
  INSERT INTO notes VALUES ('hello');
RELEASE outer;

SELECT * FROM notes;

That's why savepoints are sometimes described as "named transactions." But mixing the two styles in real code gets confusing — pick one. Most people use explicit BEGIN ... COMMIT for the outer boundary and savepoints only for the inner partial-undo points.

Nesting Savepoints

Savepoints stack. You can set one inside another and roll the inner one back without touching the outer:

CREATE TABLE items (name TEXT);

BEGIN;
  INSERT INTO items VALUES ('a');

  SAVEPOINT outer;
    INSERT INTO items VALUES ('b');

    SAVEPOINT inner;
      INSERT INTO items VALUES ('c');
    ROLLBACK TO inner;
    -- 'c' is gone; 'a' and 'b' still here

    INSERT INTO items VALUES ('d');
  RELEASE outer;
COMMIT;

SELECT * FROM items;

Final contents: a, b, d. Rolling back to inner removed c but left the work done before inner (the insert of b) intact, and the transaction kept going.

Rolling back to an outer savepoint also discards anything done at inner levels — the whole stack above that name unwinds at once:

CREATE TABLE items (name TEXT);

BEGIN;
  SAVEPOINT outer;
    INSERT INTO items VALUES ('b');
    SAVEPOINT inner;
      INSERT INTO items VALUES ('c');
  ROLLBACK TO outer;
COMMIT;

SELECT * FROM items;

Both b and c are gone. ROLLBACK TO outer rewinds everything since outer was set, including inner and the c insert.

Why Use Savepoints?

The classic case is processing a batch where individual items are allowed to fail without scrapping the whole batch. Wrap each item in a savepoint; if it fails, roll back to the savepoint and move on:

CREATE TABLE imports (id INTEGER PRIMARY KEY, value INTEGER CHECK(value > 0));

BEGIN;
  SAVEPOINT row1;
    INSERT INTO imports(value) VALUES (10);
  RELEASE row1;

  SAVEPOINT row2;
    INSERT INTO imports(value) VALUES (-5);  -- would fail the CHECK
  ROLLBACK TO row2;

  SAVEPOINT row3;
    INSERT INTO imports(value) VALUES (20);
  RELEASE row3;
COMMIT;

SELECT * FROM imports;

In real application code, the bad insert raises an error and the application catches it, issues ROLLBACK TO, and continues. Two good rows land; the bad row doesn't poison the batch.

This pattern is also how ORMs and migration tools implement nested transactions — they don't actually nest BEGIN blocks (SQLite doesn't allow that), they map nested calls to savepoints.

A Few Things to Watch

A handful of details that bite people new to savepoints:

• COMMIT always commits the whole transaction. It doesn't matter how many open savepoints you have — COMMIT (or its alias END) closes the entire outer transaction. Don't think of RELEASE as a partial commit; nothing is durable until the surrounding transaction commits.
• ROLLBACK (without TO) aborts everything. It ends the transaction and discards all open savepoints. Use ROLLBACK TO name when you want to keep the transaction alive.
• A savepoint stays open until released or rolled back through. Forgetting to RELEASE doesn't lose data, but the bookmark just sits there until the transaction ends.
• Names aren't required to be unique. If you set SAVEPOINT s twice, ROLLBACK TO s finds the most recent one. Useful for recursion; confusing if accidental.

Next: Views

Savepoints give you finer control over writes. The next step is shaping how you read — saving a query as a named, reusable object you can SELECT from like a table. That's a view, and it's coming up next.