sql / intermediate
Snippet
Managing Partial Rollbacks Using Savepoints
Savepoints establish intermediate markers within a transaction. If a subsequent statement fails or requires conditional reversal, the transaction can be rolled back specifically to the savepoint without aborting the entire set of preceding modifications.
snippet.sql
sql
1
2
3
4
5
6
START TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;SAVEPOINT balance_deducted;UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;ROLLBACK TO SAVEPOINT balance_deducted;COMMIT;
Breakdown
1
START TRANSACTION;
Initiates an explicit transaction boundary according to standard ANSI SQL.
2
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
Executes the first balance modification statement.
3
SAVEPOINT balance_deducted;
Creates a rollback point named 'balance_deducted' within the active transaction state.
4
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
Executes a second update that may later be selectively reverted.
5
ROLLBACK TO SAVEPOINT balance_deducted;
Reverts only changes made after 'balance_deducted', preserving the first update.
6
COMMIT;
Persists all remaining un-rolled-back modifications to the database.