Mysql 四種事務隔離級別

一、前提

　　時過一年重新拾起博文記錄，希望後面都能堅持下來。 接著之前MySql的學習，先記錄下這篇。

　　以下都是基於mysql8 innodb儲存引擎進行分析的。　　

二、事務的ACID特性

1. A(Atomicity) 原子性

　　指整個資料庫事務是不可分割的單位，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾。只有使事務中所有的資料庫操作都執行成功，才算整個事務執行成功。否則事務中任何一個SQL語句執行失敗，那麼這個事務就是執行失敗的， 已執行成功的SQL語句也必須撤銷，資料庫狀態應該退回到執行事務前的狀態。

2. C(consistency) 一致性

　　一致性事務將資料庫從一種狀態轉變為下一種一致的狀態。在事務開始之前和事務結束以後，資料庫的完整性約束沒有被破壞。

　　例如：在表中有個欄位為姓名為唯一約束，即在表中姓名不能重複。如果一個事務對姓名欄位進行了修改，但是在事務提交或事務操作發生回滾後，表中的姓名變得非唯一了，這就破壞了事務的一致性要求，即事務將資料從一種狀態變為了一種不一致的狀態。

3. I(isolation) 隔離性

　　事務的隔離性要求每個讀寫事務的物件對其他事務的操作物件能相互分離，即該事務提交前對其他事務都不可見。通常可以使用鎖來實現。

4. D(durability) 永續性

　　事務一旦提交，其結果就是永久性的。即使發生當機等故障，資料庫也能將資料恢復。

　　需要注意的是：只能從事務本身的角度來保證結果的永久性，例如：在事務提交後，所有變化都是永久的。即使當資料因為崩潰而需要恢復時，也能保證恢復後提交的資料都不會丟失。但如果不是資料庫本身發生故障，而是一些外部的原因導致資料庫發生問題，則有可能是提交的資料丟失(RAID卡損壞)。

　　因此永續性保證事務系統的高可靠性，而不是高可用性。對於高可用性的實現，事務本身並不能保證，需要一些系統共同配合完成。

 

三、事務的4種隔離級別

• Read Uncommitted - 未提交讀

　　在該隔離級別下的事務會讀取到其未提交事務的資料，此種現象也稱之為髒讀。

步驟	事務1	事務2
1	設定隔離級別 mysql> set @@session.transaction_isolation = 'READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED | +-------------------------+ 1 row in set (0.00 sec)
2	開啟事務1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; Empty set (0.00 sec)
3		無需管隔離級別，只開啟事務2並插入記錄 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 1, '1'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
4	此時能查到事務2中未提交事務中的資料，這就是髒讀。 mysql> select * from t; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec)

　　注意：設定隔離級別之前，記得檢視當前隔離級別的key, 有可能版本不一樣對應的key不一樣。（另外表可以自己隨意選擇）

mysql> show variables like '%isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

在實際的業務場景中應該都不允許髒讀出現，既然這麼評價不好為什麼還會出現呢？ 但其實這個隔離級別下的資料庫併發效能是最好的。

 

• Read Committed - 提交讀

一個事務可以讀取另一個已提交的事務，多次讀取會造成不一樣的結果。這種現象也被稱為不可重複讀。舉例說明：

步驟	事務1	事務2
1	先設定隔離級別 mysql> set @@session.transaction_isolation = 'READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-COMMITTED | +-------------------------+ 1 row in set (0.00 sec)
2	開啟事務1，查詢t表記錄為空 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; Empty set (0.00 sec)
3		無需管隔離級別，只開啟事務2並插入記錄 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 2, '2'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
4	繼續查詢表t依然顯示為空 (沒有讀取到事務2未提交的資料，所以不存在髒讀問題) mysql> select * from t; Empty set (0.00 sec)
5		提交事務2 mysql> commit;
6	繼續查詢表t，此時能查詢事務2已提交的資料記錄（出現前後查詢不一致） mysql> select * from t; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+

總結：在隔離級別中解決了髒讀問題，但存在不可重複讀的問題（事務中會讀取其他已提交事務中的資料） 

• Repeatable Read - 可重複讀

該隔離級別是MySQL預設的隔離級別，在同一個事務裡select的結果是事務開始時間時間點的狀態，解決了不可重複讀問題。

但其中會出現幻讀現象：基於可重複讀的基礎上查詢結果是一樣的，但是當對某些行進行更新或者插入時卻會受到影響操作不了，就形成了幻讀。例如：

步驟	事務1	事務2
1	設定隔離級別 mysql> set @@session.transaction_isolation = 'REPEATABLE-READ'; mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+
2	開啟事務1，並查詢 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+
3		無需管隔離級別，只開啟事務2並插入記錄 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 4, '4'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
4	繼續查詢表t依然只有一條記錄id=3 (沒有讀取到事務2未提交的資料，所以不存在髒讀問題) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+
5		將第二個視窗中的事務提交。 mysql> commit;
6	繼續查詢表t依然只有一條記錄id=3 (沒有讀取到事務2已提交的資料，所以不存在不可重複讀問題) mysql> select * from t; +------+------+ | id | name | +------+------+ | 3 | 3 | +------+------+
7	接著插入一條4的記錄，但提示插入不了，提示主鍵衝突問題。 然而查詢卻沒有這條id=4記錄。 這就是幻讀現象。 mysql> insert into t select 4, '4'; ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' mysql> select * from t; +----+------+ | id | name | +----+------+ | 3 | 3 | +----+------+ 1 row in set (0.00 sec)
8	另一種幻讀現象：接著上面操作，不插入記錄只更新記錄，將name 更新成 '5'後，發現有兩行受影響
	mysql> update t set name = '5'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from t; +----+------+ | id | name | +----+------+ | 3 | 5 | | 4 | 5 | +----+------+

總結：在隔離級別中解決了髒讀問題、不可重複讀的問題，但會存在幻讀問題。

但Innodb引擎提供了間隙鎖:innodb-next-key-locks, 解決了幻讀問題。基於上面結果演示下：

步驟	事務1	事務2
9	查詢時加上間隙鎖 mysql> begin; mysql> select * from t where id > 0 for update; +----+------+ | id | name | +----+------+ | 3 | 5 | | 4 | 5 | +----+------+
10		插入記錄為6的資料就會發現插入這條記錄獲取鎖超時，自動異常 insert into t select 6, '6'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

這樣成功的避免了幻讀問題，阻止了其他事務可能影響到我當前事務所涉及到的資料範圍。

• Serializable - 可序列化

在該隔離級別下事務都是序列順序執行的，MySQL 資料庫的 InnoDB 引擎會給讀操作隱式加一把讀共享鎖，從而避免了髒讀、不可重讀復讀和幻讀問題。

 

四 總結

　　1、髒讀：在一個事務中會讀取到其未提交事務的資料，此種現象也稱之為髒讀

　　2、不可重複讀：一個事務可以讀取另一個已提交的事務，多次讀取會造成不一樣的結果。這種現象也被稱為不可重複讀

　　3、幻讀：基於可重複讀的基礎上查詢結果是一樣的，但是當對某些行進行更新或者插入時卻會受到影響操作不了，就形成了幻讀。

隔離級別	髒讀	不可重複讀	幻讀
讀未提交(uncommitted read)	可能出現	可能出現	可能出現
讀提交(committed read)	不會出現	可能出現	可能出現
可重複讀(Repeatable Read)	不會出現	不會出現	可能出現（加上間隙鎖就不會）
可序列化(Serializable)	不會出現	不會出現	不會出現

五 參考文獻

《MySql 技術內幕（Innodb）第二版》

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html