laika222
11/10/2016 - 5:13 PM

You can create SAVEPOINTs to allow you to revert to a certain point within a TRANSACTION. In a transaction, you can ROLLBACK to multiple sav

You can create SAVEPOINTs to allow you to revert to a certain point within a TRANSACTION. In a transaction, you can ROLLBACK to multiple savepoints. Once you reach the desired state, you can COMMIT it to end the transaction. If you ROLLBACK without referencing a savepoint, it'll rollback the entire transaction and end the transaction. Lines 1-13 show a transaction with multiple savepoints, and then reverting to multiple savepoints before COMMITing and ending the transaction. Lines 17-54 show the same transaction with line by line notes saying what it's doing.

START TRANSACTION;
UPDATE Orders4 SET Price=1.00 WHERE OrderID=27;
SAVEPOINT savepoint1;
UPDATE Orders4 SET Price=2.00 WHERE OrderID=27;
SAVEPOINT savepoint2;
UPDATE Orders4 SET Price=3.00 WHERE OrderID=27;
SAVEPOINT savepoint3;
UPDATE Orders4 SET Price=4.00 WHERE OrderID=27;
SELECT * from Orders4;
ROLLBACK TO savepoint3;
ROLLBACK TO savepoint2;
ROLLBACK TO savepoint1;
COMMIT;

-- DETAIL OF LINES 1-12!!!

-- START TRANSACTION, DISABLE AUTO-COMMIT
START TRANSACTION;

-- UPDATE Price TO 1.00 WHERE OrderID=27
UPDATE Orders4 SET Price=1.00 WHERE OrderID=27;

-- CREATE savepoint1 WITH VALUE 1.00
SAVEPOINT savepoint1;

-- UPDATE Price TO 2.00 WHERE OrderID=27
UPDATE Orders4 SET Price=2.00 WHERE OrderID=27;

--CREATE savepoint2 WITH VALUE 2.00
SAVEPOINT savepoint2;

-- UPDATE Price TO 3.00 WHERE OrderID=27
UPDATE Orders4 SET Price=3.00 WHERE OrderID=27;

--CREATE savepoint3 WITH VALUE 3.00
SAVEPOINT savepoint3;

--UPDATE Price TO 4.00 WHERE OrderID=27
UPDATE Orders4 SET Price=4.00 WHERE OrderID=27;

-- SELECT TO SHOW THE RESULTS, WHICH WILL SHOW THE VALUE AS 4.00
SELECT * from Orders4;

-- ROLLBACK TO savepoint3, REVERTS VALUE BACK TO 3.00
ROLLBACK TO savepoint3;

--ROLLBACK TO savepoint2, REVERTS VALUE BACK TO 2.00
ROLLBACK TO savepoint2;

-- ROLLBACK TO savepoint1, REVERTS VALUE BACK TO 1.00
ROLLBACK TO savepoint1;

-- COMMIT TO FINALIZE THE VALUE AS 1.00, THEREBY ENDING THE TRANSACTION
COMMIT;