（7）MySQL進階篇SQL優化（InnoDB鎖-事務隔離級別）

2021 年 4 月 30 日
筆記
MySQL進階篇

1.概述

在我們在學習InnoDB鎖知識點之前，我覺得有必要讓大家了解它的背景知識，因為這樣才能讓我們更系統地學習好它。InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。

2.事務（Transaction）及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。
●原子性（Atomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
●一致性（Consistent）：在事務開始和結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的，數據都必須保持一致狀態，以保持數據的完整性。例如A向B銀行轉帳，不可能A扣了錢，而B收不到錢。
●隔離性（Isolation）：同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。隔離性有4個隔離級別：未提交讀、已提交讀、可重複讀和可序列化。
●持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。

3.並發事務處理帶來的問題

相對於串列處理來說，並發事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支援更多的用戶。但並發事務處理也會帶來一些問題，主要包括以下幾種情況：
●更新丟失（Lost Update）：事務A，B都更新同一數據，由於每個事務都不知道其他事務的存在，所以事務A修改完原始數據後，查詢看到的數據有可能是事務B修改後的數據，反之亦然。如果一個事務完成並提交之前，另一個事務不能訪問同一數據，則可避免此問題。
●臟讀（Dirty Reads）：事務A讀取了事務B更新的數據，然後B回滾操作，那麼A讀取到的數據是臟數據。
●不可重複讀（Non-Repeatable Reads）：事務A多次讀取同一數據，事務B在事務A多次讀取的過程中，對數據作了更新並提交，導致事務A多次讀取同一數據時，結果不一致。
●幻讀（Phantom Reads）：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。
注意：不可重複讀和幻讀其實很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

4.事務隔離級別（重點）

「臟讀」、「不可重複讀」和「幻讀」，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上可分為以下兩種：
●一種是在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
●另一種是不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot），並用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度來看，好象是資料庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。
資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的。同時，不同的應用程式對讀一致性和事務隔離程度的要求也是不同的，比如許多應用程式對「不可重複讀」和「幻讀」並不敏感，可能更關心數據並發訪問的能力。為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4種事務隔離級別：

事務隔離級別	臟讀	不可重複讀	幻讀
未提交讀（Read uncommitted）	是	是	是
已提交讀（Read committed）	否	是	是
可重複讀（Repeatable read）	否	否	是
可序列化（Serializable）	否	否	否

看到表格中4種事務隔離級別特性，相信大家起初都跟我一樣一頭霧水，不知道如何下手，後面我通過度娘搜索相關資料了解一下，用訂單表（orders）通過以下示例跟大家一一解釋。

4.1未提交讀（Read uncommitted）

●兩個事務，一個事務未提交的數據，另一個事務可以讀取到，這裡讀取到的數據叫做「臟數據」。
●這種隔離級別最低，這種級別一般是在理論上存在，實際應用中資料庫隔離級別一般都高於該級別。
MySQL默認的事務隔離級別為可重複讀（Repeatable read），輸入以下命令即可知道：

-- 查看事務隔離級別模式
SHOW VARIABLES LIKE 'transaction_isolation%';
-- 5.0版本
SELECT @@tx_isolation
-- 8.0版本
SELECT @@transaction_isolation

示例：

session_1	session_2
（1）將當前會話事務隔離級別修改為未提交讀並查詢訂單金額。	（1）將當前會話事務隔離級別修改為未提交讀並更新訂單金額。
— 將默認事務隔離級別修改為未提交讀 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 將默認事務隔離級別修改為未提交讀 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
（2）session_2會話事務未提交，當前會話就可以查詢到session_2已更新好的訂單金額了，這就是臟數據。	（2）查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)	— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)
	（3）當前會話事務因某些原因撤銷。
	— 回滾 MySQL [(none)]> ROLLBACK; Query OK, 0 rows affected (0.00 sec)
（3）當前會話再更新一次訂單金額。	（4）查詢訂單初始金額。
— 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)
（4）查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)
（5）當前會話更新訂單金額後，本來金額應為90000才對，結果卻是95000，從這就可以驗證，當前會話未更新之前第二次查詢到的訂單金額95000就是臟數據了。

注意：這裡需要注意一點是，修改事務隔離級別是可以設置為全局跟當前會話範圍的：
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
事務隔離級別的作用範圍分為兩種：
●全局級：對所有的會話有效。
●會話級：只對當前的會話有效。
所以上述示例修改當前會話事務隔離級別模式並不是全局生效的，而是只針對當前會話生效。例如session_1修改事務隔離級別為未提交讀模式，並不會對session_2、session_3…會話事務生效，而當前會話結束後，下次開啟新的會話時默認事務隔離級別還是可重複讀模式，除非修改為全局範圍。

4.2已提交讀（Read committed）

●Oracle默認隔離級別。
●兩個事務，數據只有一個事務提交了後，另一個事物才能讀取到，即：對方事物提交之後的數據，當前事物才能讀取到。
●已提交讀隔離級別高於未提交讀。這種級別可以避免「臟數據」，但是會導致「不可重複讀取」，存在幻讀問題。
示例：

session_1	session_2
（1）將當前會話事務隔離級別修改為已提交讀並查詢訂單金額。	（1）將當前會話事務隔離級別修改為已提交讀並更新訂單金額。
— 將默認事務隔離級別修改為已提交讀 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 將默認事務隔離級別修改為已提交讀 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
（2）session_2會話事務未提交，當前會話查詢到的訂單金額並沒有改變，在該模式下解決了之前存在臟數據的問題了。	（2）查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)
	（3）當前會話提交事務。
	— 提交事務 MySQL [(none)]> COMMIT; Query OK, 0 rows affected (0.01 sec)
（3）再次查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)

4.3可重複讀（Repeatable read）

●MySQL默認隔離級別。
●兩個事務，一個事務提交之後的數據，另一個事務讀取不到。
●這種隔離級別高於讀已提交，可以避免「不可重複讀取」，但是會導致「幻像讀」。
示例：

session_1	session_2
（1）將當前會話事務隔離級別修改為可重複讀並查詢訂單金額。	（1）將當前會話事務隔離級別修改為可重複讀並更新訂單金額。
— 將默認事務隔離級別修改為可重複讀MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 將默認事務隔離級別修改為可重複讀MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
（2）session_2會話事務未提交，當前會話查詢到的訂單金額並沒有改變，在該模式下也解決了臟數據的問題了。	（2）查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)
	（3）當前會話提交事務。
	— 提交事務 MySQL [(none)]> COMMIT; Query OK, 0 rows affected (0.00 sec)
（3）查詢訂單金額，三次查詢數據結果都一致，沒有出現不可重讀的情況。之所以數據的一致性得到保證，原因是：在可重複讀的隔離級別下，MySQL採用的是MVCC機制（不了解的可以點擊上文鏈接了解下），select操作不會更新版本號，是快照讀（歷史版本）。	（4）查詢訂單金額。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)	— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 95000.000000 \| +————–+ 1 row in set (0.00 sec)
（4）在當前會話再更新訂單金額。
— 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
（5）查詢訂單金額，明明只減5000金額，訂單金額卻變成90000（產生幻讀），原因是：在可重複讀的隔離級別下，MySQL採用的是MVCC機制，insert、update和delete會更新版本號，是當前讀（當前版本）。	（5）嘗試再更新訂單金額，會提示失敗，這說明可重複讀隔離級別下MySQL已經不出現幻讀的情況了。
— 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +————–+ \| OrderAmount \| +————–+ \| 90000.000000 \| +————–+ 1 row in set (0.00 sec)	— 更新訂單金額 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4.4可序列化（Serializable）

Serializable完全串列化的讀，每次讀都需要獲得表級共享鎖，讀寫操作相互互斥，這樣可以更好的解決數據一致性的問題，但是同樣會大大的降低資料庫的實際吞吐性能。所以該隔離級別因為並發性比較低、損耗太大，一般很少在開發中使用。
●MySQL中事務隔離級別為Serializable時會鎖表，因此不可能出現臟讀數據、不可重複讀、幻讀的情況。
●兩個事務，當一個事務操作資料庫時，另一個事務只能排隊等待。
●這種級別可以避免「幻像讀」，每一次讀取的都是資料庫中真實存在數據，多個事務之間串列，而不並發。
●這種隔離級別很少使用，吞吐量太低，用戶體驗差。
示例：

session_1	session_2
（1）將當前會話事務隔離級別修改為可序列化。	（1）將當前會話事務隔離級別修改為可序列化並查詢訂單金額。
— 將默認事務隔離級別修改為可序列化 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)	— 將默認事務隔離級別修改為可序列化 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) — 開啟事務 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) — 查詢訂單金額 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +—————+ \| OrderAmount \| +—————+ \| 100000.000000 \| +—————+ 1 row in set (0.00 sec)
	（2）插入一條訂單數據（其實已鎖住訂單表了）。
	— 插入訂單數據 MySQL [(none)]> INSERT INTO goods.orders(OrderAmount) VALUES (5000); Query OK, 1 row affected (0.00 sec)
（2）查詢訂單數據，會報錯，那是因為訂單表已被鎖了。
MySQL [(none)]> SELECT OrderAmount FROM goods.orders; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

參考文獻：
深入淺出MySQL大全
資料庫事務（以MySQL8.0.11為例）

Tags: MySQL進階篇