Savepoints
A savepoint is a named bookmark inside a transaction: ROLLBACK TO SAVEPOINT rewinds the transaction's work back to it without giving up the transaction itself.
In PostgreSQL, savepoints are how you recover from errors mid-transaction. MySQL doesn't abort transactions on error, so you rarely need them for that — their job here is to discard a multi-statement branch in one go.
Discard a risky branch
The risky part of the transaction made real progress before failing. A single ROLLBACK TO SAVEPOINT undoes all of it — including the statements that succeeded:
A> BEGIN;
Query OK
A> INSERT INTO items VALUES (2, 'gadget');
Query OK, 1 row affected
A> SAVEPOINT before_risky;
Query OKThe risky branch makes real progress before it fails…
A> INSERT INTO items VALUES (3, 'gizmo');
Query OK, 1 row affected
A> INSERT INTO items VALUES (4, 'widget'); -- ER_DUP_ENTRY
ERROR 1062 (23000): Duplicate entry 'widget' for key 'items.name'The transaction is still alive — but the branch is half-done. Rewind all of it in one go.
A> ROLLBACK TO SAVEPOINT before_risky;
Query OK
A> INSERT INTO items VALUES (3, 'doohickey');
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT id, name FROM items ORDER BY id; -- survived — it predates the savepoint; 'gizmo' is gone with the branch
id | name
----+-----------
1 | widget
2 | gadget
3 | doohickey
(3 rows)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Nesting and RELEASE
A> BEGIN;
Query OK
A> INSERT INTO steps VALUES (1);
Query OK, 1 row affected
A> SAVEPOINT outer_sp;
Query OK
A> INSERT INTO steps VALUES (2);
Query OK, 1 row affected
A> SAVEPOINT inner_sp;
Query OK
A> INSERT INTO steps VALUES (3);
Query OK, 1 row affectedRolling back to the OUTER savepoint discards rows 2 and 3 — and inner_sp itself.
A> ROLLBACK TO SAVEPOINT outer_sp;
Query OK
A> ROLLBACK TO SAVEPOINT inner_sp; -- SAVEPOINT inner_sp does not exist — destroyed by the outer rollback
ERROR 1305 (42000): SAVEPOINT inner_sp does not existRELEASE keeps the work done after the savepoint, but you can no longer rewind to it.
A> INSERT INTO steps VALUES (4);
Query OK, 1 row affected
A> RELEASE SAVEPOINT outer_sp;
Query OK
A> COMMIT;
Query OK
A> SELECT n FROM steps ORDER BY n;
n
---
1
4
(2 rows)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
ROLLBACK TO SAVEPOINT undoes the data changes made after the savepoint but keeps the transaction — and the locks it took before the savepoint — alive. Roll back to an outer savepoint and the inner ones vanish with it, so reaching for one afterward fails with errno 1305. RELEASE SAVEPOINT forgets a bookmark without undoing anything, and every savepoint disappears on COMMIT or a full ROLLBACK.