SAVEPOINT

Definition

A SAVEPOINT marks a named point WITHIN a transaction that you can roll back to, without discarding the entire transaction — ROLLBACK TO <savepoint> undoes only the statements after that point, leaving earlier work in the same transaction intact and still uncommitted.

How It Works

sql
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 9999 WHERE id = 2;  -- oops, wrong amount
ROLLBACK TO sp1;                                              -- undo just that mistake
UPDATE accounts SET balance = balance + 200 WHERE id = 2;    -- redo correctly
COMMIT;

ROLLBACK TO sp1 undoes only the erroneous +9999 update, but the earlier -200 debit (before the savepoint) remains part of the still-open transaction. The corrected +200 update then runs, and the whole thing commits with the correct final result — fine-grained error recovery without restarting the whole transaction from scratch.

Edge Cases and Pitfalls

• ROLLBACK TO <savepoint> does NOT end the transaction — you must still explicitly COMMIT or fully ROLLBACK afterward; forgetting this leaves the transaction still open.
• Rolling back to a savepoint that doesn't exist raises an explicit error (verified empirically: ROLLBACK TO nonexistent_sp → ERROR 1305: SAVEPOINT nonexistent_sp does not exist) rather than silently doing nothing — useful proof that the database is genuinely tracking savepoint state, not just ignoring the statement.
• Like COMMIT/ROLLBACK, SAVEPOINT semantics only apply on a genuinely transactional storage engine; on this course's judge platform, ENGINE=InnoDB must be declared for savepoints to have any real effect.
• A savepoint can be released early with RELEASE SAVEPOINT <name> if you no longer need to roll back to it (frees the resources tracking it, without affecting the transaction's data).

Key Takeaways

• SAVEPOINT creates a named intermediate point in a transaction that ROLLBACK TO can return to without discarding the whole transaction.
• After ROLLBACK TO a savepoint, the transaction is still open — an explicit COMMIT or ROLLBACK is still required to finish it.
• SAVEPOINT gives fine-grained error recovery within a single transaction, useful for multi-step operations where only part might need correcting.