MySQL中事務(wù)和ACID的作用是什么

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

所謂事務(wù)(Transaction)，就是通過確保成批的操作要么完全執(zhí)行，要么完全不執(zhí)行，來維護(hù)數(shù)據(jù)庫的完整性。舉一個爛大街的例子：A 向 B 轉(zhuǎn)賬  1000 元，對應(yīng)的 SQL 語句為：(沒有顯式定義事務(wù))

UPDATE deposit_table set depositdeposit = deposit - 1000 WHERE name = 'A'; UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name = 'B';

運(yùn)行后的結(jié)果如下：

mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A    |    3000 | | B    |    5000 | +------+---------+

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

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

A 的 1000 塊錢平白無故消失了，這肯定不合適。事務(wù)就是為了解決類似的問題而出現(xiàn)的，如果使用事務(wù)來處理轉(zhuǎn)賬，對應(yīng)的 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 ，就可以保證即使轉(zhuǎn)賬操作失敗，A  的余額也不會減少。

仔細(xì)想一想發(fā)現(xiàn)這個例子不是特別合適，因?yàn)閿?shù)據(jù)庫的故障恢復(fù)技術(shù)(以后會談到)會影響最終的結(jié)果，也不容易模擬這種故障，最后結(jié)果只能靠猜 : )  但我也想不出其它更加合適的例子。

接下來就詳細(xì)討論事務(wù)的一些特性和(某些)實(shí)現(xiàn)細(xì)節(jié)。

ACID

• A：Atomicity(原子性)

• C：Consistency(一致性)

• I：Isolation(隔離性)

• D：Durability(持久性)

1. Atomicity(原子性)

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

事務(wù)是支持提交和回滾的工作單元，原子性，就是說事務(wù)對數(shù)據(jù)庫進(jìn)行多次更改時，要么在提交事務(wù)的時候所有更改都成功，要么在回滾事務(wù)的時候撤銷所有更改。這是官方文檔的表述，但有的人似乎錯誤理解了  commit 語句，實(shí)際上，哪怕事務(wù)里某一語句出現(xiàn)了錯誤，一旦你執(zhí)行 commit，前面正常的修改仍然會被提交，MySQL 不會自動判斷事務(wù)中的 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; #提交事務(wù)  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) 的時候，由于前一條語句已經(jīng)插入了  D，所以這一句 SQL 語句執(zhí)行失敗，報(bào)出 1062 - Duplicate entry 'D' for key 'PRIMARY' 錯誤，但執(zhí)行  COMMIT后，前面的修改仍然得到了提交，這顯然是不符合我們的預(yù)期的。

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

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

# 創(chuàng)建一個存儲過程 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`() BEGIN     # 創(chuàng)建一個標(biāo)志符，出現(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ā)生錯誤，回滾事務(wù)         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

接下來我們調(diào)用該存儲過程：

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)

結(jié)果里打印出了錯誤信息 SQL Err Invoked 表的內(nèi)容也沒有更改，表明我們的 ROLLBACK  成功回滾了事務(wù)，達(dá)到我們的預(yù)期。如果你是使用其他語言調(diào)用 MySQL 的接口，也只需要獲取錯誤標(biāo)志，相應(yīng)的執(zhí)行 ROLLBACK 或者 COMMIT 。

2. Consistency(一致性)

官網(wǎng)給出的解釋如下：

翻譯過來就是：在每次提交或回滾之后以及正在進(jìn)行的事務(wù)處理期間，數(shù)據(jù)庫始終保持一致狀態(tài)，如果跨多個表更新了相關(guān)數(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; 得到的結(jié)果要么是 a_old b_old  (表明事務(wù)已回滾或者正在執(zhí)行)，要么是 a_new b_new (表明事務(wù)已經(jīng)成功提交)，而不會出現(xiàn) a_old b_new以及 a_new b_old  這兩種情況。

有的博客將一致性解釋為“數(shù)據(jù)符合現(xiàn)實(shí)世界中的約束，比如唯一性約束等等?！?我個人還是傾向于官方文檔的解釋，這點(diǎn)見仁見智吧，糾結(jié)這些概念意義不大。

3. Isolation(隔離性)

事務(wù)的隔離性是說，事務(wù)之間不能互相干擾，也不能看到彼此的未提交數(shù)據(jù)。這種隔離是通過鎖機(jī)制實(shí)現(xiàn)的。我們在操作系統(tǒng)里也了解過，使用鎖，往往就意味著并發(fā)性能的下降，因?yàn)榭赡軙l(fā)生阻塞，甚至死鎖現(xiàn)象。

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

因?yàn)楦綦x性涉及的的內(nèi)容很多，我把它放到下一篇文章詳細(xì)解釋。

4. Durability(持久性)

事務(wù)的持久性是說，一旦提交操作成功，該事務(wù)所做的更改就不會因?yàn)橐恍┮馔舛鴣G失，比如電源斷電，系統(tǒng)崩潰等潛在威脅。MySQL  提供了很多機(jī)制，比如日志技術(shù)，doublewrite buffer等等。

MySQL 的日志恢復(fù)技術(shù)我將單獨(dú)寫一篇文章，這里說說 doublewrite buffer 技術(shù)。

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

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

而且緩沖區(qū)是順序?qū)懙模_銷相對隨機(jī)讀寫要小很多，所以 doublewrite 后，性能也不是降為原來的 50%。

二、事務(wù)中的常用語句

• START TRANSACTION / BEGIN 顯式開啟一個事務(wù)

• COMMIT 提交事務(wù)，永久性修改數(shù)據(jù)庫

• SAVEPOINT 在事務(wù)里創(chuàng)建保存點(diǎn)

• RELEASE SAVAPOINT 移除某保存點(diǎn)

• ROLLBACK 回滾事務(wù)，撤回所有未提交的更改，事務(wù)會終止

• ROLLBACK TO [SAVEPOINT] 回滾到給定保存點(diǎn)，但事務(wù)不終止，另外，該保存點(diǎn)后的行鎖不會被釋放，詳見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 設(shè)置事務(wù)隔離級別

• SET autocommit 0/1 是否自動提交(默認(rèn)自動提交)

強(qiáng)調(diào)一下 autocommit 參數(shù)，默認(rèn)情況下，如果不顯式使用 START TRANSACTION / BEGIN ，MySQL 會把每一句 SQL  當(dāng)做獨(dú)立的事務(wù)，舉個例子：

原來的表結(jié)構(gòu)：

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

調(diào)用的結(jié)果：

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)

在這里，我們看到盡管確實(shí)執(zhí)行了 ROLLBACK，但 C 和 D 仍然插入到了 deposit_table 。這是因?yàn)闆]有顯式標(biāo)明事務(wù)，MySQL  會進(jìn)行隱式事務(wù)，自動提交每次的修改，所以就無法進(jìn)行回滾了。

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