MySQL 事務提交 --不良好的事務習慣。

MySQL 事務提交 --不良好的事務習慣

　　我們知道"事務"是資料庫區別于檔案系統的重要特性之一，MySQL的InnoDB引擎中的事務也完全符合ACID(原子性 一致性 隔離性 持久性)的特性，事務以及事務提交等一些內容不可避免的會出現在我們的日常作業當中，這篇文章我們就來簡單的聊聊一些不良好的事務習慣，

回圈中提交事務

　　我們先來比較兩個存盤程序

CREATE PROCEDURE load1 (count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END;

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a', 80)
START TRANSACTION;
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
COMMIT;
END;

　　因為InnoDB存盤引擎默認是自動提交的，所以load1中如果去掉Commit陳述句，結果也是一樣的，單純比較兩個存盤程序我們就可以知道，load1中存在一個問題，例如當用戶需要插入10000條記錄，但是在插入第5000條時，發生了錯誤，這時前5000條記錄已經存放在資料庫中，而load2中整體都是放在一個事務中，所以不會出現這個問題，除此之外，這兩個存盤程序的性能也存在差別，load2要比load1快出許多，這是因為每一次提交都要寫一次redo log，存盤程序load1實際上寫了10000次重做日志，而對于存盤程序load2來說，實際只寫了1次redo log，因此執行時間會有巨大的差距，

　　在別的一些資料庫中，可能總是要求對事務盡快地釋放，不能有長時間的事務；其次，可能擔心存在Oracle資料庫中由于沒有足夠undo產生的Snapshot Too Old的經典問題，MySQL的InnoDB引擎沒有上述的問題，因此無論是何種角度，都不應該在一個回圈中反復進行提交操作，不論是顯式的還是隱式的提交，

使用自動提交

　　自動提交不是一個好的習慣，因為這可能使開發人員產生錯誤的理解，我們可以使用如下陳述句來改變當前自動提交的方式：

SET autocommit=0;

　　也可以使用START TRANSACTION, BEGIN來顯式地開啟一個事務，在顯式開啟事務后，在默認設定下（即引數completion_type等于0），MySQL會自動地執行SET AUTOCOMMIT=0的命令，并在COMMIT或ROLLBACK結束一個事務后執行SET AUTOCOMMIT=1，此外對于不同語言的API，自動提交是不同的，因此在選用不同的語言來撰寫資料庫應用程式前，應該對連接MySQL的API做好研究，

使用自動回滾

　　InnoDB存盤引擎支持通過定義一個HANDLER來進行自動事務的回滾操作，如在一個存盤程序中發生了錯誤會自動對其進行回滾操作，因此我發現很多開發人員喜歡在應用程式的存盤程序中使用自動回滾操作，例如下面所示的一個存盤程序：

CREATE TABLE 'b' (
'a' int(11) NOT NULL DEFAULT '0',
PRIMARY KEY ('a')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
END;

　　存盤程序sp_auto_rollback_demo首先定義了一個exit型別的HANDLER,當捕獲到錯誤時進行回滾，因此插入第二個記錄1時會發生錯誤，但是因為啟用了自動回滾操作，因此這個存盤程序的執行結果是沒有問題的，看起來非常正常，但我們并不能看出這個存盤程序的結果到底是正確還是錯誤的，為了得到執行正確與否的結果，開發人員可能會進行這樣的處理：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; SELECT -1; END;
START TRANSACTION;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 2;
INSERT INTO b SELECT 1;
INSERT INTO b SELECT 3;
COMMIT;
SELECT 1;
END;

　　這樣如果發生錯誤，先回滾然后回傳-1，表示運行有錯誤，運行正常回傳值1，但是其實問題還是沒有解決，對開發人員來說，重要的不僅是知道是否發生了錯誤，還要知道發生了什么錯誤，因此自動回滾存在這樣一個問題，這里其實有一個建議是：對事務的BEGIN,COMMIT和ROLLBACK操作應該交給程式端來完成，存盤程序需要完成的只是一個邏輯操作，即對邏輯進行封裝，在程式中控制事務的好處是，用戶可以得知發生錯誤的原因，

長事務

　　顧名思義，長事務是執行時間較長的事務，比如，對于銀行系統的資料庫，每過一個階段可能需要更新對應賬戶的利息，如果對應賬號的數量非常大，例如對有1億用戶的表account，需要執行下列陳述句：

UPDATE account
SET account_total = account_total + (1 + interest_rate)

　　這個事務可能需要非常長的時間來完成，由于事務ACID的特性，這個操作被封裝在一個事務中完成，這就產生了一個問題，在執行程序中，當資料庫或作業系統，硬體等發生問題時，重新開始事務的代價變得不可接受，資料庫需要回滾所有已經發生的變化，而這個程序可能比產生這些變化的時間還要長，因此，對于長事務的問題，有時候可以通過轉化為小批量的事務來進行處理，當事務發生錯誤時，只需要回滾一部分資料，然后接著上次已經完成的事務繼續進行，

小結

　　事務是資料庫區別于檔案系統的一個關鍵特性，事務必須遵循ACID特性，即Atomicity(原子性), Consistency(一致性), Isolation(隔離性) 和 Durability(持久性)，隔離性通過鎖來完成，原子性，一致性，隔離性通過redo和undo來完成，在默認配置下，MySQL InnoDB總是自動提交的，如果不知道這點，可能會帶來不好的結果，此外，在應用程式中，最好的做法是把事務的START TRANSACTION, COMMIT, ROLLBACK操作交給程式段來完成，而不是在存盤程序中完成，

標籤：MySQL

上一篇：Leetcode 之 Mysql（day01）

下一篇：MySQL索引查詢原理