淺談MySQL的事務與ACID

NO IMAGE

最近把個人博客搭建好了,鏈接在這裡:tobe的囈語,文章會先在博客和公眾號更新~ 希望大家多多收藏啊

所謂事務(Transaction),就是通過確保成批的操作要麼完全執行,要麼完全不執行,來維護數據庫的完整性。舉一個爛大街的例子:A 向 B 轉賬 1000 元,對應的 SQL 語句為:(沒有顯式定義事務)

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

運行後的結果如下:

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

這樣做可能遇到問題,比如執行完第一條語句之後,數據庫崩潰了,最後的結果就可能會是這樣(畢竟咱不會模擬這種故障):

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

A 的 1000 塊錢平白無故消失了,這肯定不合適。事務就是為了解決類似的問題而出現的,如果使用事務來處理轉賬,對應的 SQL 就是:

START TRANSACTION;
UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';
COMMIT;

僅僅是在這原先的兩條 SQL 語句前後加上了 START TRANSACTIONCOMMIT ,就可以保證即使轉賬操作失敗,A 的餘額也不會減少。

仔細想一想發現這個例子不是特別合適,因為數據庫的故障恢復技術(以後會談到)會影響最終的結果,也不容易模擬這種故障,最後結果只能靠猜 : ) 但我也想不出其它更加合適的例子。。。如果你們有更好的例子歡迎留言討論。

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

ACID

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

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

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 這兩種情況。

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

Isolation(隔離性)

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

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

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

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](https://dev.mysql.com/doc/refman/8.0/en/savepoint.html):

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

事務的基本概念就介紹這麼多,以後我將會講到事務的隔離機制,範式設計等內容,敬請期待!

希望你在看完我的文章之後有所收穫,期待你的贊和轉發!

相關文章

Scala教程之:可變和不變集合

Hadoop完全分佈式安裝與部署

Elasticsearch調優篇慢查詢分析筆記

FishRedux完成一個玩安卓客戶端