laika222
11/10/2016 - 3:51 PM

Normally, statements are auto-committed and can't be rolled back. Use START TRANSACTION to disable auto-commit for a series of statements. T

Normally, statements are auto-committed and can't be rolled back. Use START TRANSACTION to disable auto-commit for a series of statements. This allows you to not change anything until all statements are successful or until you approve the result of the changes. Transactions also can provide isolation protecting from multiple users updating data at same time until the trans is complete. Once COMMIT is executed, the transaction ends and auto-commit is restored. Transactions can also use ROLLBACK to undo the transaction from the beginning. Line 6 shows a transaction that completes two statements (lines 8 & 9), updating Price in Orders4 where OrderID=33. Line 10 SELECTs to show the change that was made. If the change is satisfactory, line 11 permanently COMMITs it and sets auto-commit back on. Starting line 13 shows the same transaction but if the change isn't satisfactory on line 17, ROLLBACK is issued, which reverts the Price back to its original value, and ends the transaction turning auto-commit back on

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

--COMMITTED TRANSACTION
START TRANSACTION;
SELECT @A:=SUM(Price)FROM products1 WHERE ProductID=2;
UPDATE Orders4 SET Price=@A WHERE OrderID=33;
SELECT * from Orders4;
COMMIT;

--COMMITTED TRANSACTION
START TRANSACTION;
SELECT @A:=SUM(Price)FROM products1 WHERE ProductID=2;
UPDATE Orders4 SET Price=@A WHERE OrderID=33;
SELECT * from Orders4;
ROLLBACK;