淺談MySQL的事務和ACID
所謂事務(Transaction)，就是通過確保成批的操作要么完全執(zhí)行，要么完全不執(zhí)行，來維護數(shù)據(jù)庫的完整性。舉一個爛大街的例子：A 向 B 轉賬 1000 元，對應的 SQL 語句為：(沒有顯式定義事務)
- UPDATE deposit_table set depositdeposit = deposit - 1000 WHERE name = 'A';
- UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name = 'B';
運行后的結果如下：
- mysql> SELECT * FROM deposit_table;
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 3000 |
- | B | 5000 |
- +------+---------+
這樣做可能遇到問題，比如執(zhí)行完第一條語句之后，數(shù)據(jù)庫崩潰了，最后的結果就可能會是這樣(有待商榷，取決于下一條 SQL 有沒有被寫入日志)：
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 5000 |
- +------+---------+
A 的 1000 塊錢平白無故消失了，這肯定不合適。事務就是為了解決類似的問題而出現(xiàn)的，如果使用事務來處理轉賬，對應的 SQL 就是：
- START TRANSACTION;
- UPDATE deposit_table set depositdeposit = deposit - 1000 WHERE name = 'A';
- UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name = 'B';
- COMMIT;
僅僅是在這原先的兩條 SQL 語句前后加上了 START TRANSACTION 和 COMMIT ，就可以保證即使轉賬操作失敗，A 的余額也不會減少。
仔細想一想發(fā)現(xiàn)這個例子不是特別合適，因為數(shù)據(jù)庫的故障恢復技術(以后會談到)會影響最終的結果，也不容易模擬這種故障，最后結果只能靠猜 : ) 但我也想不出其它更加合適的例子。
接下來就詳細討論事務的一些特性和(某些)實現(xiàn)細節(jié)。
ACID
- A：Atomicity(原子性)
- C：Consistency(一致性)
- I：Isolation(隔離性)
- D：Durability(持久性)
1. Atomicity(原子性)
先談兩個重要的概念：提交(commit)和回滾(rollback)，當我們執(zhí)行提交操作后，將對數(shù)據(jù)庫進行永久性的修改，執(zhí)行回滾操作，意味著數(shù)據(jù)庫將撤銷正在進行的所有沒有提交的修改。注意這里的永久性并不意味著事務一完成就把數(shù)據(jù)刷到磁盤上，即使沒有刷入磁盤，MySQL 也有日志機制來保證修改不會丟失。
事務是支持提交和回滾的工作單元，原子性，就是說事務對數(shù)據(jù)庫進行多次更改時，要么在提交事務的時候所有更改都成功，要么在回滾事務的時候撤銷所有更改。這是官方文檔的表述，但有的人似乎錯誤理解了 commit 語句，實際上，哪怕事務里某一語句出現(xiàn)了錯誤，一旦你執(zhí)行 commit，前面正常的修改仍然會被提交，MySQL 不會自動判斷事務中的 SQL 執(zhí)行成功與否。
我們接下來用例子來看看 commit 和 rollback：
- mysql> SELECT * FROM deposit_table;
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 6000 |
- +------+---------+
- 2 rows in set (0.04 sec)
- mysql>
- START TRANSACTION;
- INSERT INTO deposit_table VALUES('C', 7000);
- INSERT INTO deposit_table VALUES('D', 8000);
- #再次插入 D，由于主鍵的唯一性，該語句會執(zhí)行失敗
- INSERT INTO deposit_table VALUES('D', 9000);
- COMMIT; #提交事務
- Query OK, 0 rows affected (0.00 sec)
- Query OK, 1 row affected (0.00 sec)
- Query OK, 1 row affected (0.00 sec)
- 1062 - Duplicate entry 'D' for key 'PRIMARY'
- Query OK, 0 rows affected (0.07 sec)
- mysql> SELECT * FROM deposit_table;
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 6000 |
- | C | 7000 |
- | D | 8000 |
- +------+---------+
- 4 rows in set (0.04 sec)
我們可以看到，在執(zhí)行 INSERT INTO deposit_table VALUES('D', 9000) 的時候，由于前一條語句已經插入了 D，所以這一句 SQL 語句執(zhí)行失敗，報出 1062 - Duplicate entry 'D' for key 'PRIMARY' 錯誤，但執(zhí)行 COMMIT后，前面的修改仍然得到了提交，這顯然是不符合我們的預期的。
注意：如果你是使用 Navicat 的查詢界面，將執(zhí)行不到 COMMIT 語句，只能執(zhí)行到報錯的地方，建議使用命令行來執(zhí)行。
所以在實際情況中，我們需要根據(jù) MySQL 的錯誤返回值來確定，是使用 ROLLBACK 還是 COMMIT 。就像這樣：
- # 創(chuàng)建一個存儲過程
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
- BEGIN
- # 創(chuàng)建一個標志符，出現(xiàn)錯誤就將其置為 1
- DECLARE err_flg INTEGER;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;
- START TRANSACTION;
- INSERT INTO deposit_table VALUES('C', 7000);
- INSERT INTO deposit_table VALUES('D', 8000);
- INSERT INTO deposit_table VALUES('D', 9000);
- # 發(fā)生錯誤，回滾事務
- IF err_flg = 1 THEN
- SELECT 'SQL Err Invoked'; # 錯誤提示信息
- ROLLBACK;
- SELECT * FROM deposit_table;
- # 沒有發(fā)生錯誤，直接提交
- ELSE
- SELECT 'TRANSACTION Success';
- COMMIT;
- SELECT * FROM deposit_table;
- END IF;
- END
接下來我們調用該存儲過程：
- mysql> call insert_test();
- +-----------------+
- | SQL Err Invoked |
- +-----------------+
- | SQL Err Invoked |
- +-----------------+
- 1 row in set (0.04 sec)
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 6000 |
- +------+---------+
- 2 rows in set (0.09 sec)
- Query OK, 0 rows affected (0.00 sec)
結果里打印出了錯誤信息 SQL Err Invoked 表的內容也沒有更改，表明我們的 ROLLBACK 成功回滾了事務，達到我們的預期。如果你是使用其他語言調用 MySQL 的接口，也只需要獲取錯誤標志，相應的執(zhí)行 ROLLBACK 或者 COMMIT 。
2. Consistency(一致性)
官網給出的解釋如下：
| The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values. |
翻譯過來就是：在每次提交或回滾之后以及正在進行的事務處理期間，數(shù)據(jù)庫始終保持一致狀態(tài)，如果跨多個表更新了相關數(shù)據(jù)，則查詢將看到所有舊值或所有新值，而不是新舊值的混合。
舉個例子：
- # 表 a，b 的定義略過
- START TRANSACTION;
- UPDATE a SET name = 'a_new' WHERE name = 'a_old';
- UPDATE b SET name = 'b_new' WHERE name = 'b_old';
- COMMIT;
這個例子里的一致性，就是說，如果此時有查詢 SELECT a.name, b.name FROM a, b; 得到的結果要么是 a_old b_old (表明事務已回滾或者正在執(zhí)行)，要么是 a_new b_new (表明事務已經成功提交)，而不會出現(xiàn) a_old b_new以及 a_new b_old 這兩種情況。
有的博客將一致性解釋為“數(shù)據(jù)符合現(xiàn)實世界中的約束，比如唯一性約束等等。” 我個人還是傾向于官方文檔的解釋，這點見仁見智吧，糾結這些概念意義不大。
3. Isolation(隔離性)
事務的隔離性是說，事務之間不能互相干擾，也不能看到彼此的未提交數(shù)據(jù)。這種隔離是通過鎖機制實現(xiàn)的。我們在操作系統(tǒng)里也了解過，使用鎖，往往就意味著并發(fā)性能的下降，因為可能會發(fā)生阻塞，甚至死鎖現(xiàn)象。
當然，用戶在確定事務確實不會相互干擾時，可以調整隔離級別，犧牲部分隔離性以提高性能和并發(fā)性，至于使用哪種隔離級別(isolation level)這就需要你自己做 trade off。
因為隔離性涉及的的內容很多，我把它放到下一篇文章詳細解釋。
4. Durability(持久性)
事務的持久性是說，一旦提交操作成功，該事務所做的更改就不會因為一些意外而丟失，比如電源斷電，系統(tǒng)崩潰等潛在威脅。MySQL 提供了很多機制，比如日志技術，doublewrite buffer等等。
MySQL 的日志恢復技術我將單獨寫一篇文章，這里說說 doublewrite buffer 技術。
雖然這個技術名字叫做 buffer，但實際上該緩沖區(qū)并不位于內存，而是位于磁盤。這可能聽起來很詭異——既然是把數(shù)據(jù)放入磁盤，為啥不直接寫入到 data file，反而多此一舉?
這是因為 InnoDB 的 Page Size 一般是 16kb，其數(shù)據(jù)校驗也是針對頁來計算的，在將數(shù)據(jù)刷入磁盤的過程中，如果發(fā)生斷電等故障，該頁可能只寫入了一部分(partial page write)。這種情況是 redo 日志無法解決的，因為 redo 日志中記錄的是對頁的物理操作，如果頁本身發(fā)生了損壞，再對其進行 redo 是沒有意義的。所以我們需要一個副本，在發(fā)生這種情況時還原該頁。
而且緩沖區(qū)是順序寫的，開銷相對隨機讀寫要小很多，所以 doublewrite 后，性能也不是降為原來的 50%。
二、事務中的常用語句
- START TRANSACTION / BEGIN 顯式開啟一個事務
- COMMIT 提交事務，永久性修改數(shù)據(jù)庫
- SAVEPOINT 在事務里創(chuàng)建保存點
- RELEASE SAVAPOINT 移除某保存點
- ROLLBACK 回滾事務，撤回所有未提交的更改，事務會終止
- ROLLBACK TO [SAVEPOINT] 回滾到給定保存點，但事務不終止，另外，該保存點后的行鎖不會被釋放，詳見SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) - SET TRANSACTION 設置事務隔離級別
- SET autocommit 0/1 是否自動提交(默認自動提交)
強調一下 autocommit 參數(shù)，默認情況下，如果不顯式使用 START TRANSACTION / BEGIN ，MySQL 會把每一句 SQL 當做獨立的事務，舉個例子：
原來的表結構：
- mysql> SELECT * FROM deposit_table;
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 6000 |
- +------+---------+
- 2 rows in set (0.04 sec)
新的存儲過程(僅僅刪除了 START TRANSACTION )：
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
- BEGIN
- #Routine body goes here...
- DECLARE err_flg INTEGER;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;
- # START TRANSACTION;
- INSERT INTO deposit_table VALUES('C', 7000);
- INSERT INTO deposit_table VALUES('D', 8000);
- INSERT INTO deposit_table VALUES('D', 9000);
- IF err_flg = 1 THEN
- SELECT 'SQL Err Invoked';
- ROLLBACK;
- SELECT * FROM deposit_table;
- ELSE
- SELECT 'TRANSACTION Success';
- COMMIT;
- SELECT * FROM deposit_table;
- END IF;
- END
調用的結果：
- mysql> call insert_test();
- +-----------------+
- | SQL Err Invoked |
- +-----------------+
- | SQL Err Invoked |
- +-----------------+
- 1 row in set (0.24 sec)
- +------+---------+
- | name | deposit |
- +------+---------+
- | A | 2000 |
- | B | 6000 |
- | C | 7000 |
- | D | 8000 |
- +------+---------+
- 4 rows in set (0.28 sec)
- Query OK, 0 rows affected (0.21 sec)
在這里，我們看到盡管確實執(zhí)行了 ROLLBACK，但 C 和 D 仍然插入到了 deposit_table 。這是因為沒有顯式標明事務，MySQL 會進行隱式事務，自動提交每次的修改，所以就無法進行回滾了。
