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.11 · 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.11 · 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.