MySQL~Rollback與Commit

資料庫的交易(Transaction)功能，能確保多個 SQL 指令，全部執行成功，或全部不執行，不會因為一些意外狀況，而只執行一部份指令，造成資料異常。

例如~ 當要轉帳時，會有一個交易資料，要從甲帳戶扣錢，把錢加到乙帳戶。如果這個交易從甲帳戶扣錢成功，結果乙帳戶的帳號錯誤，無法成功存入，怎麼辦?

如果有一個機制，可以把「從甲帳戶扣錢，把錢加到乙帳戶」當成一個完整的交易，只有兩個作業都成功，才進行交易(就是Commit)，否則就取消交易(就是Rollback)，這樣就不會出現錯誤。


要點 (1)

MySQL 常用的兩個資料表類型：MyISAM 不支援交易功能，所以以下的整理，均是針對 InnoDB 的DML指令而言，DDL 的指令就不能 ROLLBACK。

怎麼知道我的資料表是MyISAM還是InnoDB? 請參考 https://www.mysql.tw/2017/06/innodb-foreign-key.html

開始進入交易模式，可以使用 START TRANSACTION 或是 BEGIN，並等待COMMIT或是ROLLBACK來確定要不要確認交易。

要點 (2)

SET AUTOCOMMIT=1; 表示每次的指令自動確認 (不需要commit但是無法rollback)
SET AUTOCOMMIT=0; 表示每次的指令不會自動確認 (需要commit但是可以rollback)

SELECT @@AUTOCOMMIT; 可以查詢目前是否AUTOCOMMIT
因此要能夠使用rollback，必須讓AUTOCOMMIT=0

將 AUTOCOMMIT 改為 0 時，就算沒使用 START TRANSACTION 或 BEGIN ，整個連線執行的 SQL 指令，都會等到下達 COMMIT 提交後，才會真正儲存變更。也就是跟在交易模式下相同。

SELECT @@AUTOCOMMIT;
SELECT @AUTOCOMMIT;
SELECT AUTOCOMMIT;

以上三個有何差異? @@AUTOCOMMIT指系統變數，@AUTOCOMMIT指自訂變數，最後一個則是錯誤的指令。

要點 (3)

交易過程中，可標示多個不同的儲存點，有需要時可 ROLLBACK 到某個儲存點。

建立儲存點：SAVEPOINT 名稱
刪除儲存點：RELEASE SAVEPOINT 名稱
ROLLBACK 到某個儲存點：ROLLBACK TO SAVEPOINT 名稱
如果建立新儲存點時，已有同名稱的舊儲存點，舊儲存點將被刪除，並建立新的儲存點。

範例 :
BEGIN;
INSERT INTO student VALUES(10, 'abc');
COMMIT;

範例 :
START TRANSACTION;
INSERT INTO student VALUES(10, 'abc');
COMMIT;

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

範例 :

BEGIN;
INSERT INTO testtable (id, t) VALUES(1, 10);
SAVEPOINT p1;
UPDATE student SET t=20 WHERE id=1;
ROLLBACK TO SAVEPOINT  p1;
COMMIT;

更多參考資料
https://xyz.cinc.biz/2013/05/mysql-transaction.html
https://blog.longwin.com.tw/2006/03/innodb_transaction_2006/