SQLite SAVEPOINT

A savepoint creates a named checkpoint inside an active transaction. You can roll back to a savepoint without undoing the entire transaction. This gives you fine-grained control over which parts of a complex operation to keep and which to discard.

Savepoint vs Transaction Analogy

Imagine editing a document:

BEGIN (open the doc for editing)
  ─── Write paragraph 1
  SAVEPOINT after_para1    ← bookmark here
  ─── Write paragraph 2
  SAVEPOINT after_para2    ← bookmark here
  ─── Write paragraph 3
  ─── Realize paragraph 3 is wrong
  ROLLBACK TO after_para2  ← undo only paragraph 3
  ─── Rewrite paragraph 3
COMMIT                     ← save everything to disk

Without savepoints, you'd have to undo the entire document
and rewrite all paragraphs from scratch.

SAVEPOINT Syntax

SAVEPOINT savepoint_name;           -- create a checkpoint
ROLLBACK TO savepoint_name;         -- undo back to checkpoint
RELEASE savepoint_name;             -- remove checkpoint (keep work)

Working Example: Order Processing

CREATE TABLE orders  (id INTEGER PRIMARY KEY, item TEXT, amount REAL);
CREATE TABLE payments(id INTEGER PRIMARY KEY, order_id INTEGER, paid REAL);
CREATE TABLE stock   (item TEXT PRIMARY KEY, qty INTEGER);

INSERT INTO stock VALUES ('Pen',100),('Notebook',50),('Eraser',200);

-- Process a complex order with savepoints
BEGIN;

  -- Step 1: Insert the order
  INSERT INTO orders (item, amount) VALUES ('Pen', 9.90);
  SAVEPOINT order_placed;

  -- Step 2: Deduct stock
  UPDATE stock SET qty = qty - 10 WHERE item = 'Pen';
  SAVEPOINT stock_deducted;

  -- Step 3: Record payment — simulate a failure condition
  INSERT INTO payments (order_id, paid) VALUES (last_insert_rowid(), 9.90);

  -- Discover: wrong amount entered, correct it
  ROLLBACK TO stock_deducted;   -- undo the bad payment insert only

  -- Redo payment with correct data
  INSERT INTO payments (order_id, paid)
    VALUES ((SELECT MAX(id) FROM orders), 9.90);

COMMIT;

Multiple Nested Savepoints

BEGIN;

  INSERT INTO log VALUES (1, 'Step A');
  SAVEPOINT sp1;

  INSERT INTO log VALUES (2, 'Step B');
  SAVEPOINT sp2;

  INSERT INTO log VALUES (3, 'Step C');
  SAVEPOINT sp3;

  -- Roll back only Step C
  ROLLBACK TO sp3;

  -- sp3 still exists, re-insertable if needed
  INSERT INTO log VALUES (3, 'Step C corrected');

  -- Release sp2: remove it but KEEP work since sp2
  RELEASE sp2;

COMMIT;   -- saves: Step A, Step B, Step C corrected

SAVEPOINT vs Regular Transaction

                BEGIN / COMMIT      SAVEPOINT
────────────────────────────────────────────────────────
Rollback scope  Entire transaction  Only back to savepoint
Nesting         Cannot nest         Can nest multiple
Use case        Simple operations   Complex multi-step ops
Named           No                  Yes (you choose the name)

RELEASE — Remove Without Rolling Back

BEGIN;

  INSERT INTO t VALUES (1, 'A');
  SAVEPOINT sp1;

  INSERT INTO t VALUES (2, 'B');

  RELEASE sp1;    -- savepoint sp1 removed, both inserts kept

COMMIT;
-- Result: rows 1 and 2 both saved

Savepoints Without BEGIN

Creating a savepoint outside an explicit transaction automatically starts one. The transaction commits or rolls back when you RELEASE or ROLLBACK the outermost savepoint.

SAVEPOINT outer;        -- implicitly starts a transaction

  INSERT INTO t VALUES (1, 'test');
  SAVEPOINT inner;
    INSERT INTO t VALUES (2, 'nested');
  ROLLBACK TO inner;    -- undo 'nested' insert only

RELEASE outer;          -- commits 'test' insert

Error Recovery Pattern

BEGIN;

FOR each item in batch:
  SAVEPOINT item_sp;

  TRY:
    INSERT INTO orders ...
    UPDATE stock ...
    RELEASE item_sp;        -- success: keep this item's changes

  ON ERROR:
    ROLLBACK TO item_sp;    -- failure: undo just this item
    -- log the error, continue to next item

COMMIT;   -- save all successfully processed items

Key Takeaways

• SAVEPOINT creates a named checkpoint inside a transaction
• ROLLBACK TO sp_name undoes work back to that checkpoint without cancelling the whole transaction
• RELEASE sp_name removes a savepoint but keeps the work done since it was set
• Savepoints can nest — roll back to any earlier checkpoint selectively
• Use savepoints in batch processing to recover from per-item errors without losing the entire batch