Savepoints

The MySQL 8.4 savepoint contract defines a named point inside a transaction. ROLLBACK TO SAVEPOINT undoes subsequent InnoDB row changes without ending that transaction. It does not commit earlier work. The examples run on MySQL 8.4.11, InnoDB, with explicit BEGIN.

Discard a risky branch

The duplicate-key error rolls back one INSERT, but the preceding branch INSERT survives until A rolls back to the savepoint. The intermediate SELECT asserts that the branch row is gone and the earlier INSERT remains. A then commits a replacement.

A> BEGIN;
Query OK

A> INSERT INTO items VALUES (2, 'gadget');
Query OK, 1 row affected

A> SAVEPOINT before_risky;
Query OK

The 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> SELECT id, name FROM items ORDER BY id;
 id |  name  
----+--------
  1 | widget 
  2 | gadget 
(2 rows)

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

The second Scenario asserts that rolling back to the outer savepoint destroys the inner one (1305). RELEASE removes the outer bookmark without undoing row 4, and an attempt to reuse it also fails with 1305. The final committed rows are 1 and 4.

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 affected

Rolling 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 exist

RELEASE 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> ROLLBACK TO SAVEPOINT outer_sp; -- the released savepoint no longer exists
ERROR 1305 (42000): SAVEPOINT outer_sp does not exist

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

The manual additionally documents that COMMIT and full ROLLBACK delete savepoints, and reusing a savepoint name replaces the old point. Savepoint rollback does not generally release row locks stored in memory after the savepoint. An inserted row's lock carried in its transaction ID is released when that insertion is undone. Those lock and same-name rules are Documented contracts, not asserted by these INSERT-only schedules. Do not infer that a discarded branch releases every lock.

Statement recovery is error-specific. A deadlock rolls back the whole transaction, including its savepoints; see error handling. Nontransactional writes and implicit-commit DDL are outside this rollback promise.

Further reading

• MySQL 8.4: SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT
• The same lesson on PostgreSQL