MySQL~Rollback與Commit



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

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

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


要點 (1)

MySQL 常用的兩個資料表類型:MyISAM 不支援交易功能,所以以下的整理,均是針對 InnoDB 而言。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 提交後,才會真正儲存變更。也就是跟在交易模式下相同。

要點 (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 [email protected] WHERE type=1;
COMMIT;

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

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理