sql / intermediate
Snippet
Managing Partial Rollbacks using Transaction Savepoints
Savepoints provide granular transaction control by allowing partial rollbacks to specified markers within a transaction without aborting the entire sequence of operations.
snippet.sql
sql
1
2
3
4
5
6
START TRANSACTION;UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 101;SAVEPOINT inventory_updated;UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 202;ROLLBACK TO SAVEPOINT inventory_updated;COMMIT;
Breakdown
1
START TRANSACTION;
Begins a new explicit database transaction block.
2
SAVEPOINT inventory_updated;
Establishes a named checkpoint inside the active transaction.
3
ROLLBACK TO SAVEPOINT inventory_updated;
Undoes modifications made after the savepoint while preserving prior operations.
4
COMMIT;
Permanently saves all changes executed prior to the savepoint.