sql / intermediate
Snippet
Handling Partial Failures with Transaction Savepoints
Savepoints allow granular rollback control within a transaction. By marking a point inside an active transaction, you can revert subsequent operational errors without abandoning earlier successful statements.
snippet.sql
sql
1
2
3
4
5
6
START TRANSACTION;INSERT INTO orders (order_id, customer_id) VALUES (101, 55);SAVEPOINT item_insertion;INSERT INTO order_items (order_id, item_id, quantity) VALUES (101, 999, 1);ROLLBACK TO SAVEPOINT item_insertion;COMMIT;
Breakdown
1
START TRANSACTION;
Begins an atomic database transaction boundary.
2
SAVEPOINT item_insertion;
Creates a named restoration marker after the primary order insertion.
3
ROLLBACK TO SAVEPOINT item_insertion;
Undoes changes made after the savepoint while preserving the parent order record.
4
COMMIT;
Finalizes the remaining un-rolled-back modifications to persistent storage.