Savepoints

Named markers inside a transaction that let you undo part of the work without giving up the whole unit.

SyntaxSAVEPOINT identifier; ... ROLLBACK [WORK] TO [SAVEPOINT] identifier; ... RELEASE SAVEPOINT identifier;

A savepoint is a named marker inside an open transaction. ROLLBACK TO SAVEPOINT undoes everything done since that marker and leaves the transaction running, so you can recover from one failed step without discarding the work before it.

The three statements

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

WORK and SAVEPOINT in the rollback form are optional noise words, so ROLLBACK TO sp1 and ROLLBACK WORK TO SAVEPOINT sp1 are the same statement. RELEASE SAVEPOINT forgets a marker without committing or rolling anything back.

Rules that bite

Setting a savepoint whose name already exists deletes the old one and replaces it. Rolling back to a savepoint deletes every savepoint set after it. COMMIT, and any ROLLBACK that does not name a savepoint, deletes all of them. Naming one that does not exist gives error 1305, SAVEPOINT identifier does not exist.

Locks are not rewound

Row changes are undone, but row locks taken after the savepoint are not released — the exception being newly inserted rows, whose locks go away with the undo. Everything else stays locked until the transaction finally commits or rolls back. A savepoint therefore shortens your rollback, never your lock footprint.

Example

Example · sql
-- Import a batch of rows as one transaction, skipping the bad ones
-- instead of losing the whole batch to a single bad record.
START TRANSACTION;

INSERT INTO import_runs (source, started_at) VALUES ('feed-2026-09', NOW());
SET @run_id = LAST_INSERT_ID();

SAVEPOINT row_1;
INSERT INTO products (sku, name, price) VALUES ('KB-88', 'Keyboard', 79.00);
-- succeeded; keep it and move on

SAVEPOINT row_2;
INSERT INTO products (sku, name, price) VALUES ('KB-88', 'Duplicate', 12.00);
-- ERROR 1062 (23000): Duplicate entry 'KB-88' for key 'products.PRIMARY'

-- Undo only this row. row_1's insert and the import_runs row survive.
ROLLBACK TO SAVEPOINT row_2;
INSERT INTO import_errors (run_id, sku, reason)
VALUES (@run_id, 'KB-88', 'duplicate sku');

COMMIT;   -- all savepoints are discarded here

When to use it

  • Importing a batch where one invalid row should be skipped rather than aborting the whole load.
  • Recording an audit row that survives even when the operation it describes is rolled back.
  • Implementing nested transaction blocks in a data-access layer on top of MySQL.
  • Trying an optimistic update and falling back to a slower path without reopening the transaction.

More examples

The same rollback, written four ways

WORK and SAVEPOINT are optional keywords; the short form is what most code uses and the long form is what the standard spells out. The distinction that matters is the last line: dropping the savepoint name turns a partial undo into a full one. A missing identifier in generated SQL is therefore not a syntax error, it is a silently much larger rollback.

Example · sql
START TRANSACTION;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 7;

-- All four of these are the same statement:
ROLLBACK TO sp1;
ROLLBACK TO SAVEPOINT sp1;
ROLLBACK WORK TO sp1;
ROLLBACK WORK TO SAVEPOINT sp1;

-- But this one is completely different: no savepoint named, so it
-- ends the entire transaction and deletes every savepoint in it.
ROLLBACK;

Nested savepoints and how they disappear

Savepoints form a stack, and rolling back to one pops everything above it. This is exactly how ORMs implement 'nested transactions' — MySQL has no real nesting, so frameworks emit SAVEPOINT for an inner block and ROLLBACK TO SAVEPOINT when it fails. Knowing that explains why an inner block's failure can leave outer work intact while a bare ROLLBACK throws all of it away.

Example · sql
START TRANSACTION;
SAVEPOINT outer_sp;

INSERT INTO orders (customer_id, status) VALUES (4821, 'pending');

  SAVEPOINT inner_sp;
  UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'KB-88';

-- Rolling back to the outer marker also deletes inner_sp.
ROLLBACK TO SAVEPOINT outer_sp;

ROLLBACK TO SAVEPOINT inner_sp;
-- ERROR 1305 (42000): SAVEPOINT inner_sp does not exist

RELEASE SAVEPOINT outer_sp;   -- forget the marker, keep the transaction
COMMIT;

A per-item savepoint in a stored procedure

The pattern for keeping an audit trail even when the operation it records fails. The savepoint sits after the log insert, so the handler can discard the money movement and still commit the evidence that it was attempted. Without the savepoint the handler's only options are committing a half-applied credit or rolling back the log entry too.

Example · sql
DELIMITER //

CREATE PROCEDURE apply_credit(IN cust_id INT, IN amt DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK TO SAVEPOINT before_credit;   -- partial undo
    INSERT INTO credit_failures (customer_id, amount, failed_at)
    VALUES (cust_id, amt, NOW());
    COMMIT;                                -- keep the audit row
  END;

  START TRANSACTION;
  INSERT INTO audit_log (customer_id, action) VALUES (cust_id, 'credit_attempt');

  SAVEPOINT before_credit;
  UPDATE accounts SET balance = balance + amt WHERE customer_id = cust_id;
  INSERT INTO credits (customer_id, amount) VALUES (cust_id, amt);
  COMMIT;
END //

DELIMITER ;

Related

sql begin commitsql acidsql lockingsql isolationsql insert

Discussion

  • Be the first to comment on this lesson.