sql / intermediate
Snippet
Partial Transaction Rollback with Savepoints
Savepoints allow granular control over transaction execution by creating intermediate restore markers. If subsequent statements fail or need to be discarded, you can roll back to a specific savepoint without aborting the entire transaction block.
snippet.sql
sql
1
2
3
4
5
6
START TRANSACTION;INSERT INTO accounts (account_id, balance) VALUES (101, 500.00);SAVEPOINT payment_created;UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 101;ROLLBACK TO SAVEPOINT payment_created;COMMIT;
Breakdown
1
START TRANSACTION;
Begins a new explicit transaction block in ANSI SQL.
2
INSERT INTO accounts (account_id, balance) VALUES (101, 500.00);
Executes the first state-changing SQL statement within the active transaction context.
3
SAVEPOINT payment_created;
Establishes a named checkpoint inside the transaction to which execution can later revert.
4
UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 101;
Performs an update operation after the savepoint mark.
5
ROLLBACK TO SAVEPOINT payment_created;
Undoes changes made after the payment_created savepoint while keeping earlier modifications intact.
6
COMMIT;
Permanently saves all remaining un-rolled-back modifications to the database.