MySQL中事務和ACID的作用是什么

今天就跟大家聊聊有關MySQL中事務和ACID的作用是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

所謂事務(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(一致性)

官網給出的解釋如下：

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

舉個例子：

# 表 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  會進行隱式事務，自動提交每次的修改，所以就無法進行回滾了。

看完上述內容，你們對MySQL中事務和ACID的作用是什么有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。