Savepoints in MySQL

Introduction

Savepoints let you mark a spot in your work so you can go back to it if needed without undoing everything.

When you want to try a few changes but might want to undo some without losing all progress.

When working on a big task and want to fix mistakes step-by-step.

When multiple steps depend on each other and you want to keep parts safe if later steps fail.

When testing parts of a transaction and deciding if you want to keep or undo them.

When you want to improve control over what parts of your work to save or undo.

Syntax

MySQL

SAVEPOINT savepoint_name;
-- do some work
ROLLBACK TO savepoint_name;
-- or
RELEASE SAVEPOINT savepoint_name;

Savepoint names must be unique within the current transaction.

ROLLBACK TO savepoint_name undoes changes after the savepoint but keeps earlier changes.

Examples

Starts a transaction, marks a savepoint, inserts a user, then undoes the insert by rolling back to the savepoint before committing.

MySQL

START TRANSACTION;
SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK TO sp1;
COMMIT;

Two updates happen with two savepoints. Rolling back to sp2 undoes the second update but keeps the first.

MySQL

START TRANSACTION;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO sp2;
COMMIT;

Marks a savepoint, deletes an order, then releases the savepoint to free resources before committing.

MySQL

START TRANSACTION;
SAVEPOINT sp1;
DELETE FROM orders WHERE id = 10;
RELEASE SAVEPOINT sp1;
COMMIT;

Sample Program

This transaction tries to insert 'Pen' but rolls back that insert using the savepoint. Then it inserts 'Pencil' and commits.

MySQL

START TRANSACTION;
SAVEPOINT before_insert;
INSERT INTO products (name, price) VALUES ('Pen', 1.5);
ROLLBACK TO before_insert;
INSERT INTO products (name, price) VALUES ('Pencil', 0.5);
COMMIT;

Important Notes

Savepoints only work inside transactions started with START TRANSACTION.

ROLLBACK TO savepoint_name does not end the transaction; you can continue working.

RELEASE SAVEPOINT frees the savepoint but does not undo changes.

Summary

Savepoints let you mark places inside a transaction to return to if needed.

You can undo changes after a savepoint without undoing everything.

Use savepoints to control complex transactions step-by-step.