A savepoint is a marker inside a transaction. It lets you undo part of the work without undoing all of it — which is what you need when a long transaction contains several independent steps and one of them fails. ROLLBACK undoes the whole transaction; ROLLBACK TO SAVEPOINT undoes only what happened after that marker.
How do savepoints and ROLLBACK undo part of a transaction?
Consider a data migration that adds a new holder and then adds a second one. If the second insert fails, you want to keep the first and discard the second — not roll back the entire migration. The grammar is four statements: SAVEPOINT names a point, ROLLBACK TO undoes back to it, RELEASE keeps everything up to it, and COMMIT ends the transaction.
What does the savepoint sequence look like?
Two holders are added inside a transaction. Both exist at the moment the SELECT runs:
BEGIN;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (8, '[email protected]', 'First Change', 'Bristol', '2026-10-28');
SAVEPOINT after_first;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (9, '[email protected]', 'Second Change', 'Cardiff', '2026-10-28');
SELECT COUNT(*) AS rows_inside_transaction FROM ticket_holders WHERE holder_id IN (8, 9);
rows_inside_transaction
-----------------------
2
Two rows. Now the migration changes its mind about the second one, and rolls back to the savepoint:
ROLLBACK TO after_first;
SELECT COUNT(*) AS rows_after_rollback_to FROM ticket_holders WHERE holder_id IN (8, 9);
rows_after_rollback_to
1
One row — the first change survived, the second was undone. The transaction itself is still open, and the COMMIT that follows makes the surviving row permanent:
COMMIT;
SELECT holder_id, full_name FROM ticket_holders WHERE holder_id IN (8, 9);
holder_id full_name
--------- ------------
8 First Change
That is the whole mechanism: a marker, a partial undo, and a final commit. The transaction stays alive across a ROLLBACK TO, so you can carry on working after the undo rather than starting again.
What does RELEASE do?
RELEASE is the other half of the pair. It discards the marker while keeping the work, which is how you tell the database that a step is finished and its intermediate state is no longer needed. A migration with two accepted steps:
BEGIN;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (10, '[email protected]', 'Third Change', 'Glasgow', '2026-10-28');
SAVEPOINT keep_this;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (11, '[email protected]', 'Fourth Change', 'Glasgow', '2026-10-28');
RELEASE keep_this;
SELECT holder_id, full_name FROM ticket_holders WHERE holder_id IN (10, 11);
COMMIT;
holder_id full_name
--------- -----------
10 Third Change
11 Fourth Change
Both rows survive. Without the RELEASE the result would be identical — a savepoint is a marker, not a lock — but the marker would still be on the stack when the transaction ends, which some engines treat as an incomplete piece of work. Releasing each savepoint once you are happy with that step is the tidy habit.
Can savepoints be nested?
Yes, and they behave like a stack. Each savepoint records the state at its own moment, so rolling back to an outer one discards everything inside it:
BEGIN;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (12, '[email protected]', 'Outer Step', 'Bristol', '2026-10-28');
SAVEPOINT outer_sp;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (13, '[email protected]', 'Inner Step', 'Bristol', '2026-10-28');
SAVEPOINT inner_sp;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (14, '[email protected]', 'Innermost Step', 'Bristol', '2026-10-28');
ROLLBACK TO inner_sp;
SELECT COUNT(*) AS rows_after_inner_rollback FROM ticket_holders WHERE holder_id IN (12, 13, 14);
rows_after_inner_rollback
-------------------------
2
Two rows survive the inner rollback — the outer step and the inner step, with only the innermost insert undone. Rolling back further discards the inner step too:
ROLLBACK TO outer_sp;
SELECT COUNT(*) AS rows_after_outer_rollback FROM ticket_holders WHERE holder_id IN (12, 13, 14);
rows_after_outer_rollback
-------------------------
1
One row. And after the commit, only the outermost step is permanent. The DELETE at the end of that statement sweeps the demonstration holders away again, so that the module's exercises all start from the same six holders:
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (15, '[email protected]', 'Cleanup Row', 'Bristol', '2026-10-28');
COMMIT;
SELECT holder_id, full_name FROM ticket_holders WHERE holder_id IN (12, 13, 14);
DELETE FROM ticket_holders WHERE holder_id > 6;
holder_id full_name
--------- -----------
12 Outer Step
When is a savepoint the right tool?
- A batch job where each item is independent and one bad item should not undo the good ones.
- A migration with several steps, each of which can be validated and retried independently.
- A procedure that runs a validation, undoes it if it fails, and then does the real work.
- Any place where "all or nothing" is too coarse a choice.
Two cautions. Savepoints are a feature of the transaction, not of the table — they vanish when the transaction ends, so they are no substitute for a persistent staging table. And a savepoint that is never released or rolled back is left on the stack until the transaction commits, so name them clearly and tidy up as you go. The dialect picture is consistent: PostgreSQL, MySQL, SQL Server, Oracle and SQLite all support SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT, though PostgreSQL and Oracle add SAVEPOINT nesting levels and the MySQL spelling of the rollback-to command differs slightly in older versions.
Quick reference
- SAVEPOINT names a point inside the transaction; ROLLBACK TO undoes back to it and keeps the transaction alive.
- RELEASE discards the marker but keeps the work done since it was set.
- Savepoints nest, and rolling back to an outer one discards every step inside it.
- Always COMMIT or ROLLBACK the transaction at the end, and release savepoints as you finish each step.
- Savepoints live only inside the transaction — use a staging table for durable intermediate state.
Savepoint drill: write a transaction that loads three new ticket holders from a feed, saving after each one, and rolls back to the last savepoint when a holder fails validation — then describe in one sentence how you would report to the user which items were kept and which were rejected.