How to Use Savepoint in MySQL: Syntax and Examples

In MySQL, use SAVEPOINT to set a point within a transaction to which you can later roll back without affecting the entire transaction. Use ROLLBACK TO SAVEPOINT to undo changes after the savepoint, and RELEASE SAVEPOINT to remove it.

Syntax

The SAVEPOINT command creates a named point in a transaction. You can roll back to this point without rolling back the whole transaction. The main commands are:

• SAVEPOINT savepoint_name; - sets a savepoint.
• ROLLBACK TO SAVEPOINT savepoint_name; - undoes changes after the savepoint.
• RELEASE SAVEPOINT savepoint_name; - removes the savepoint.

sql

SAVEPOINT savepoint_name;
-- Later in the transaction
ROLLBACK TO SAVEPOINT savepoint_name;
-- Or to remove
RELEASE SAVEPOINT savepoint_name;

Example

This example shows how to use savepoints to undo part of a transaction without losing all changes.

sql

START TRANSACTION;

INSERT INTO employees (name, role) VALUES ('Alice', 'Developer');
SAVEPOINT sp1;

INSERT INTO employees (name, role) VALUES ('Bob', 'Designer');

-- Oops, we want to undo Bob's insert only
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

Output

Query OK, 1 row affected.
Query OK, 0 rows affected.
Query OK, 1 row affected.
Query OK, 0 rows affected.
Query OK, 0 rows affected.

Common Pitfalls

Common mistakes when using savepoints include:

• Trying to use savepoints outside a transaction (they only work inside START TRANSACTION and COMMIT blocks).
• Rolling back to a savepoint that does not exist or was released.
• Not releasing savepoints, which can clutter the transaction.

Always ensure your savepoint names are unique within the transaction.

sql

/* Wrong: Using savepoint outside transaction */
SAVEPOINT sp1; -- Error

/* Correct: Inside transaction */
START TRANSACTION;
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Quick Reference

Command	Description
SAVEPOINT savepoint_name;	Create a savepoint in the current transaction.
ROLLBACK TO SAVEPOINT savepoint_name;	Undo changes after the savepoint.
RELEASE SAVEPOINT savepoint_name;	Remove the savepoint.
START TRANSACTION;	Begin a transaction.
COMMIT;	Save all changes permanently.

Key Takeaways

Use SAVEPOINT to mark a point inside a transaction for partial rollback.

ROLLBACK TO SAVEPOINT undoes changes after the savepoint without aborting the whole transaction.

Savepoints only work inside transactions started with START TRANSACTION.

Release savepoints when no longer needed to keep transactions clean.

Savepoint names must be unique within the transaction.