Savepoints

Savepoints create named checkpoints inside a transaction.

They let you roll back part of a transaction while keeping earlier successful work.

Core Concepts

flowchart LR
  A[BEGIN] --> B[SAVEPOINT step1]
  B --> C[Work A]
  C --> D[SAVEPOINT step2]
  D --> E[Work B]
  E --> F{Need partial undo?}
  F -->|Yes| G[ROLLBACK TO step2]
  F -->|No| H[Keep all]
  G --> I[RELEASE / COMMIT]
  H --> I

Command	Purpose
SAVEPOINT name;	Create checkpoint
ROLLBACK TO name;	Undo to checkpoint
RELEASE name;	Finalize/remove savepoint

Partial Undo

BEGIN;

INSERT INTO tx_sp_demo (value) VALUES ('kept-1');
SAVEPOINT sp1;

INSERT INTO tx_sp_demo (value) VALUES ('undo-this');
ROLLBACK TO sp1;

RELEASE sp1;
COMMIT;

Expected output
kept-1 persists; undo-this is rolled back.

Multiple Savepoints

BEGIN;
SAVEPOINT a;
INSERT INTO tx_sp_demo (value) VALUES ('A');
SAVEPOINT b;
INSERT INTO tx_sp_demo (value) VALUES ('B');
ROLLBACK TO b;
RELEASE b;
RELEASE a;
COMMIT;

Expected output
A remains; B is undone before commit.

Code Examples

-- Setup table for savepoint examples.
CREATE TABLE tx_sp_demo (
  id    INTEGER PRIMARY KEY,
  value TEXT NOT NULL
);

Expected output
Savepoint demo table created.

-- Demonstrate selective rollback.
BEGIN;

INSERT INTO tx_sp_demo (value)
VALUES ('base');

SAVEPOINT step_after_base;

INSERT INTO tx_sp_demo (value)
VALUES ('temporary-change');

ROLLBACK TO step_after_base;
RELEASE step_after_base;

COMMIT;

Expected output
base persists; temporary-change is removed.

-- Verify final table contents.
SELECT id, value
FROM tx_sp_demo
ORDER BY id;

id	value
1	base

SQLite-Specific Nuances

SQLite Nuance

Savepoints are SQLite's practical tool for nested transactional control inside one outer transaction.

ROLLBACK TO does not end the transaction; it rewinds to the named savepoint.

Common Pitfalls / Best Practices

Pitfall

Calling ROLLBACK TO and assuming transaction is finished.

You still need to COMMIT or full ROLLBACK at the outer level.

Best Practice

Use clear savepoint names that describe workflow checkpoints (before_discount, after_validation).

Quick Challenge

Inside one transaction, insert row-a, set savepoint spx, insert row-b, roll back to spx, then commit.

View Solution

BEGIN;

INSERT INTO tx_sp_demo (value)
VALUES ('row-a');

SAVEPOINT spx;

INSERT INTO tx_sp_demo (value)
VALUES ('row-b');

ROLLBACK TO spx;
RELEASE spx;

COMMIT;

Expected output
row-a persists, row-b is discarded.