How to Use Savepoints in MySQL
ROLLBACK throws away a whole transaction. Sometimes you only want to undo the last part: try something, and if it doesn't work, carry on with what you've already done. A savepoint is a marker inside the transaction that lets you do just that.
Prerequisites
- MySQL, and a table. The examples use MySQL 9.7.4 in Docker, the
mysqlclient from How to Connect to MySQL in a Docker Container, and aproductstable. - Transactions.
Step 1: Set a Savepoint
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
SAVEPOINT before_second_change;
UPDATE products SET QuantityOnHand = 48 WHERE Id = 4;
SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
| 3 | 49 |
| 4 | 48 |
+----+----------------+
2 rows in set (0.001 sec)
Both changes are in place.
Step 2: Undo Back to It
ROLLBACK TO SAVEPOINT before_second_change;
SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
| 3 | 49 |
| 4 | 50 |
+----+----------------+
2 rows in set (0.002 sec)
The second change is gone, and the first is still there. MySQL's SAVEPOINT page says: "The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction."
Step 3: Commit What's Left
COMMIT;
SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
| 3 | 49 |
| 4 | 50 |
+----+----------------+
2 rows in set (0.001 sec)
Rolling Back Doesn't Release the Locks
A rollback to a savepoint undoes the change, and keeps the lock on the row:
START TRANSACTION;
UPDATE products SET ReorderLevel = 13 WHERE Id = 4;
SAVEPOINT b;
UPDATE products SET ReorderLevel = 14 WHERE Id = 3;
ROLLBACK TO SAVEPOINT b;
+---------------+-----------+
| LOCK_MODE | LOCK_DATA |
+---------------+-----------+
| X,REC_NOT_GAP | 3 |
| X,REC_NOT_GAP | 4 |
+---------------+-----------+
2 rows in set (0.001 sec)
Row 3 is still locked, although nothing about it changed in the end. Another session can't have it until the transaction ends:
UPDATE products SET ReorderLevel = 15 WHERE Id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
The page says why: "InnoDB does not release the row locks that were stored in memory after the savepoint." So a savepoint can undo your work, but only the end of the transaction lets others in.
When a Savepoint Stops Existing
Two things remove one. The first is RELEASE SAVEPOINT, when you're done with it:
START TRANSACTION;
SAVEPOINT a;
RELEASE SAVEPOINT a;
ROLLBACK TO SAVEPOINT a;
ERROR 1305 (42000): SAVEPOINT a does not exist
The second is the end of the transaction:
SAVEPOINT b;
COMMIT;
ROLLBACK TO SAVEPOINT b;
Query OK, 0 rows affected (0.000 sec)
The page says: "All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint." Fixed: ERROR 1305 covers the error.
Why EF Core Uses Them
EF Core sets a savepoint around each SaveChanges inside your transaction, so one failed save can't damage the rest. How to Use Savepoints in EF Core shows the statements.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Rolling back to a savepoint, and expecting the transaction to end | It stays open | COMMIT or ROLLBACK afterwards |
Expecting a savepoint to outlive a COMMIT | ERROR 1305 | Set a new one in the new transaction |
| Using a savepoint outside a transaction | With autocommit on, it's gone with the statement | START TRANSACTION first |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
SAVEPOINT a does not exist | It was released, or the transaction ended | Check the order of your statements |
| Other sessions still wait after the rollback to a savepoint | The locks stay | COMMIT or ROLLBACK to release them |
FAQ
Is this a nested transaction?
It's the closest MySQL has. A second START TRANSACTION commits the first, so savepoints are how you get an inner part that can fail by itself.
Can I set the same name twice?
Yes. The page says: "If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set."
Conclusion
SAVEPOINT name marks a place, ROLLBACK TO SAVEPOINT name undoes back to it and keeps the transaction open, and RELEASE SAVEPOINT name drops the mark. Everything ends with the transaction.
Additional Resources
- SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements — the statements.
