SAVEPOINT
In this page:
What is a SAVEPOINT?
A SAVEPOINT works like a bookmark placed partway through a long transaction, letting you roll back to that exact point later without discarding everything that happened earlier in the same transaction.
Example: What is a SAVEPOINT?
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT after_withdrawal;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;Rolling Back to a SAVEPOINT
ROLLBACK TO undoes everything that happened after a specific savepoint while preserving the changes made before it, giving you fine-grained control instead of an all-or-nothing rollback of the entire transaction.
Example: Rolling Back to a SAVEPOINT
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT after_withdrawal;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO after_withdrawal;
COMMIT;Releasing a SAVEPOINT
RELEASE SAVEPOINT removes a savepoint you no longer need without saving or undoing any data — it simply frees the resources MySQL was using to track that particular checkpoint.
Example: Releasing a SAVEPOINT
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT after_withdrawal;
RELEASE SAVEPOINT after_withdrawal;
COMMIT;Multiple SAVEPOINTs in One Transaction
A single transaction can contain multiple savepoints, which is useful for multi-step processes where you might need to step backward incrementally to find exactly where something went wrong.
Example: Multiple SAVEPOINTs in One Transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT step1;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
SAVEPOINT step2;
ROLLBACK TO step1;
COMMIT;SAVEPOINT Lifespan and Limits
Savepoints only exist for the lifetime of their transaction — the moment the transaction ends with either a COMMIT or a full ROLLBACK, every savepoint inside it is automatically discarded.
Example: SAVEPOINT Lifespan and Limits
START TRANSACTION;
SAVEPOINT step1;
COMMIT;
-- step1 no longer exists here -- savepoints don't survive past COMMIT/ROLLBACKChapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: