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

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為例）

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/281996.html

標籤：MySQL

上一篇：【趙強老師】Kafka的訊息持久化

下一篇：基本的SQL陳述句

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