淺談MySQL的事務和ACID

作者：tobe 2020-02-13 10:14:11

所謂事務(Transaction)，就是通過確保成批的操作要么完全執行，要么完全不執行，來維護數據庫的完整性。

所謂事務(Transaction)，就是通過確保成批的操作要么完全執行，要么完全不執行，來維護數據庫的完整性。舉一個爛大街的例子：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 | 
+------+---------+

這樣做可能遇到問題，比如執行完第一條語句之后，數據庫崩潰了，最后的結果就可能會是這樣(有待商榷，取決于下一條 SQL 有沒有被寫入日志)：

+------+---------+ 
| name | deposit | 
+------+---------+ 
| A    |    2000 | 
| B    |    5000 | 
+------+---------+

A 的 1000 塊錢平白無故消失了，這肯定不合適。事務就是為了解決類似的問題而出現的，如果使用事務來處理轉賬，對應的 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 的余額也不會減少。

仔細想一想發現這個例子不是特別合適，因為數據庫的故障恢復技術(以后會談到)會影響最終的結果，也不容易模擬這種故障，最后結果只能靠猜 : ) 但我也想不出其它更加合適的例子。

接下來就詳細討論事務的一些特性和(某些)實現細節。

ACID

A：Atomicity(原子性)
C：Consistency(一致性)
I：Isolation(隔離性)
D：Durability(持久性)

1. Atomicity(原子性)

先談兩個重要的概念：提交(commit)和回滾(rollback)，當我們執行提交操作后，將對數據庫進行永久性的修改，執行回滾操作，意味著數據庫將撤銷正在進行的所有沒有提交的修改。注意這里的永久性并不意味著事務一完成就把數據刷到磁盤上，即使沒有刷入磁盤，MySQL 也有日志機制來保證修改不會丟失。

事務是支持提交和回滾的工作單元，原子性，就是說事務對數據庫進行多次更改時，要么在提交事務的時候所有更改都成功，要么在回滾事務的時候撤銷所有更改。這是官方文檔的表述，但有的人似乎錯誤理解了 commit 語句，實際上，哪怕事務里某一語句出現了錯誤，一旦你執行 commit，前面正常的修改仍然會被提交，MySQL 不會自動判斷事務中的 SQL 執行成功與否。

我們接下來用例子來看看 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，由于主鍵的唯一性，該語句會執行失敗 
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)

我們可以看到，在執行 INSERT INTO deposit_table VALUES('D', 9000) 的時候，由于前一條語句已經插入了 D，所以這一句 SQL 語句執行失敗，報出 1062 - Duplicate entry 'D' for key 'PRIMARY' 錯誤，但執行 COMMIT后，前面的修改仍然得到了提交，這顯然是不符合我們的預期的。

注意：如果你是使用 Navicat 的查詢界面，將執行不到 COMMIT 語句，只能執行到報錯的地方，建議使用命令行來執行。

所以在實際情況中，我們需要根據 MySQL 的錯誤返回值來確定，是使用 ROLLBACK 還是 COMMIT 。就像這樣：

# 創建一個存儲過程 
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`() 
BEGIN 
    # 創建一個標志符，出現錯誤就將其置為 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); 
         
        # 發生錯誤，回滾事務 
        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.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 的接口，也只需要獲取錯誤標志，相應的執行 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.

翻譯過來就是：在每次提交或回滾之后以及正在進行的事務處理期間，數據庫始終保持一致狀態，如果跨多個表更新了相關數據，則查詢將看到所有舊值或所有新值，而不是新舊值的混合。

舉個例子：

# 表 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 (表明事務已回滾或者正在執行)，要么是 a_new b_new (表明事務已經成功提交)，而不會出現 a_old b_new以及 a_new b_old 這兩種情況。

有的博客將一致性解釋為“數據符合現實世界中的約束，比如唯一性約束等等。” 我個人還是傾向于官方文檔的解釋，這點見仁見智吧，糾結這些概念意義不大。

3. Isolation(隔離性)

事務的隔離性是說，事務之間不能互相干擾，也不能看到彼此的未提交數據。這種隔離是通過鎖機制實現的。我們在操作系統里也了解過，使用鎖，往往就意味著并發性能的下降，因為可能會發生阻塞，甚至死鎖現象。

當然，用戶在確定事務確實不會相互干擾時，可以調整隔離級別，犧牲部分隔離性以提高性能和并發性，至于使用哪種隔離級別(isolation level)這就需要你自己做 trade off。

因為隔離性涉及的的內容很多，我把它放到下一篇文章詳細解釋。

4. Durability(持久性)

事務的持久性是說，一旦提交操作成功，該事務所做的更改就不會因為一些意外而丟失，比如電源斷電，系統崩潰等潛在威脅。MySQL 提供了很多機制，比如日志技術，doublewrite buffer等等。

MySQL 的日志恢復技術我將單獨寫一篇文章，這里說說 doublewrite buffer 技術。

雖然這個技術名字叫做 buffer，但實際上該緩沖區并不位于內存，而是位于磁盤。這可能聽起來很詭異——既然是把數據放入磁盤，為啥不直接寫入到 data file，反而多此一舉?

這是因為 InnoDB 的 Page Size 一般是 16kb，其數據校驗也是針對頁來計算的，在將數據刷入磁盤的過程中，如果發生斷電等故障，該頁可能只寫入了一部分(partial page write)。這種情況是 redo 日志無法解決的，因為 redo 日志中記錄的是對頁的物理操作，如果頁本身發生了損壞，再對其進行 redo 是沒有意義的。所以我們需要一個副本，在發生這種情況時還原該頁。

而且緩沖區是順序寫的，開銷相對隨機讀寫要小很多，所以 doublewrite 后，性能也不是降為原來的 50%。

二、事務中的常用語句

START TRANSACTION / BEGIN 顯式開啟一個事務
COMMIT 提交事務，永久性修改數據庫
SAVEPOINT 在事務里創建保存點
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 參數，默認情況下，如果不顯式使用 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)

在這里，我們看到盡管確實執行了 ROLLBACK，但 C 和 D 仍然插入到了 deposit_table 。這是因為沒有顯式標明事務，MySQL 會進行隱式事務，自動提交每次的修改，所以就無法進行回滾了。

責任編輯：趙寧寧來源： tobe的囈語

MySQL 事務 ACID