sql / intermediate
Snippet
Managing Partial Rollbacks using Transaction Savepoints
Savepoints establish intermediate markers within an ongoing transaction. They allow rolling back specific operations performed after the marker without aborting the entire transaction.
snippet.sql
sql
1
2
3
4
5
6
START TRANSACTION;INSERT INTO logs (log_message) VALUES ('Process started');SAVEPOINT before_update;UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;ROLLBACK TO SAVEPOINT before_update;COMMIT;
Breakdown
1
START TRANSACTION;
Begins the transaction block.
2
INSERT INTO logs (log_message) VALUES ('Process started');
Inserts an initial logging entry that will persist after commit.
3
SAVEPOINT before_update;
Sets a named savepoint bookmark called before_update.
4
UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;
Performs an inventory stock decrement.
5
ROLLBACK TO SAVEPOINT before_update;
Cancels the inventory update while retaining the previous log insertion.
6
COMMIT;
Finalizes the remaining active operations in the transaction.