Fixed: "ERROR 1305 (42000): SAVEPOINT does not exist" in MySQL

ROLLBACK TO SAVEPOINT failed with ERROR 1305 (42000): SAVEPOINT a does not exist. A savepoint only lasts as long as you let it: releasing it removes it, and so does the end of the transaction.

What I Was Doing

I was using savepoints to undo part of a transaction, on MySQL 9.7.4 in Docker. I'd released one, and later tried to go back to it:

mysql

START TRANSACTION;
SAVEPOINT a;
RELEASE SAVEPOINT a;
ROLLBACK TO SAVEPOINT a;

What I Expected

Back to the savepoint.

What I Got

Output (the last statement)

ERROR 1305 (42000): SAVEPOINT a does not exist

The end of a transaction does the same:

mysql

SAVEPOINT b;
COMMIT;
ROLLBACK TO SAVEPOINT b;

Output (the last statement)

Query OK, 0 rows affected (0.000 sec)

What I Checked

The SAVEPOINT page. It has this exact message: "If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists". And it says what removes one: "All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint."

Why It Happens

A savepoint is a marker inside one transaction. RELEASE SAVEPOINT removes the marker, and COMMIT or ROLLBACK ends the transaction and every marker with it. With autocommit on and no START TRANSACTION, a SAVEPOINT statement is its own transaction, so its marker is gone immediately.

The Fix

Set the savepoint inside the transaction you want to go back in, and roll back to it before releasing it or ending the transaction:

mysql

START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
SAVEPOINT before_second_change;
UPDATE products SET QuantityOnHand = 48 WHERE Id = 4;
ROLLBACK TO SAVEPOINT before_second_change;
SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);

After the Fix

Output (ROLLBACK TO SAVEPOINT, then the SELECT)

Query OK, 0 rows affected (0.000 sec)

+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             49 |
|  4 |             50 |
+----+----------------+
2 rows in set (0.002 sec)

Common Mistakes

Mistake	What happens	Do this instead
Rolling back to a savepoint after COMMIT	ERROR 1305	Roll back before you commit
SAVEPOINT with no transaction open	With autocommit on, it's gone at once	START TRANSACTION first
Reusing a released name without setting it again	ERROR 1305	Set it again
A statement that commits by itself in between, such as CREATE TABLE	The transaction ended	Run those outside the transaction

Troubleshooting

Symptom	Likely cause	Fix
The name is right, and it still fails	Something ended the transaction	Look for a COMMIT, or a statement that commits by itself
It works in the client, and fails in the app	The app's connection has autocommit on, with no transaction	Begin a transaction first

Conclusion

ERROR 1305 means the savepoint no longer exists. It was released, or the transaction ended. Keep the ROLLBACK TO SAVEPOINT inside the same open transaction, before any release.

Additional Resources

• SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements — the rules.

Related

• How to Use Savepoints in MySQL
• How to Use Savepoints in EF Core
• Which MySQL Statements Commit a Transaction Automatically