徹底搞懂 MySQL 事務的隔離級別

zhangdeTalk發表於2020-02-05
MySql

事前準備資料

mysql> create table city(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> primary key (id)
    -> )engine=innodb charset=utf8mb4;

insert into city(name) values('武漢市');

mysql> select * from city;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 武漢市 |
+----+-----------+

事務併發可能出現的情況

髒讀（Dirty Read）

一個事務讀到了另一個未提交事務修改過的資料

從根上理解MySQL事務的隔離級別

會話B開啟一個事務，把id=1的name為武漢市修改成溫州市，此時另外一個會話A也開啟一個事務，讀取id=1的name，此時的查詢結果為溫州市，會話B的事務最後回滾了剛才修改的記錄，這樣會話A讀到的資料是不存在的，這個現象就是髒讀。（髒讀只在讀未提交隔離級別才會出現）

不可重複讀（Non-Repeatable Read）

一個事務只能讀到另一個已經提交的事務修改過的資料，並且其他事務每對該資料進行一次修改並提交後，該事務都能查詢得到最新值。（不可重複讀在讀未提交和讀已提交隔離級別都可能會出現）

從根上理解 MySQL 事務的隔離級別

會話A開啟一個事務，查詢id=1的結果，此時查詢的結果name為武漢市。接著會話B把id=1的name修改為溫州市（隱式事務，因為此時的autocommit為1，每條SQL語句執行完自動提交），此時會話A的事務再一次查詢id=1的結果，讀取的結果name為溫州市。會話B再此修改id=1的name為杭州市，會話A的事務再次查詢id=1，結果name的值為杭州市，這種現象就是不可重複讀。

幻讀（Phantom）

一個事務先根據某些條件查詢出一些記錄，之後另一個事務又向表中插入了符合這些條件的記錄，原先的事務再次按照該條件查詢時，能把另一個事務插入的記錄也讀出來。（幻讀在讀未提交、讀已提交、可重複讀隔離級別都可能會出現）

從根上理解 MySQL 事務的隔離級別

會話A開啟一個事務，查詢id>0的記錄，此時會查到name=武漢市的記錄。接著會話B插入一條name=溫州市的資料（隱式事務，因為此時的autocommit為1，每條SQL語句執行完自動提交），這時會話A的事務再以剛才的查詢條件（id>0）再一次查詢，此時會出現兩條記錄（name為武漢市和溫州市的記錄），這種現象就是幻讀。

事務的隔離級別

MySQL的事務隔離級別一共有四個，分別是讀未提交、讀已提交、可重複讀以及可序列化。

MySQL的隔離級別的作用就是讓事務之間互相隔離，互不影響，這樣可以保證事務的一致性。

隔離級別比較：可序列化>可重複讀>讀已提交>讀未提交

隔離級別對效能的影響比較：可序列化>可重複讀>讀已提交>讀未提交

由此看出，隔離級別越高，所需要消耗的MySQL效能越大（如事務併發嚴重性），為了平衡二者，一般建議設定的隔離級別為可重複讀，MySQL預設的隔離級別也是可重複讀。

讀未提交（READ UNCOMMITTED）

徹底搞懂 MySQL 事務的隔離級別

在讀未提交隔離級別下，事務A可以讀取到事務B修改過但未提交的資料。

可能發生髒讀、不可重複讀和幻讀問題，一般很少使用此隔離級別。

讀已提交（READ COMMITTED）

徹底搞懂 MySQL 事務的隔離級別

在讀已提交隔離級別下，事務B只能在事務A修改過並且已提交後才能讀取到事務B修改的資料。

讀已提交隔離級別解決了髒讀的問題，但可能發生不可重複讀和幻讀問題，一般很少使用此隔離級別。

可重複讀（REPEATABLE READ）

徹底搞懂 MySQL 事務的隔離級別

在可重複讀隔離級別下，事務B只能在事務A修改過資料並提交後，自己也提交事務後，才能讀取到事務B修改的資料。

可重複讀隔離級別解決了髒讀和不可重複讀的問題，但可能發生幻讀問題。

提問：為什麼上了寫鎖（寫操作），別的事務還可以讀操作？

因為InnoDB有MVCC機制（多版本併發控制），可以使用快照讀，而不會被阻塞。

可序列化（SERIALIZABLE）

徹底搞懂 MySQL 事務的隔離級別

徹底搞懂 MySQL 事務的隔離級別

徹底搞懂 MySQL 事務的隔離級別

徹底搞懂 MySQL 事務的隔離級別

各種問題（髒讀、不可重複讀、幻讀）都不會發生，通過加鎖實現（讀鎖和寫鎖）。

徹底搞懂 MySQL 事務的隔離級別

徹底搞懂 MySQL 事務的隔離級別

隔離級別的實現原理

使用MySQL的預設隔離級別（可重複讀）來進行說明。

每條記錄在更新的時候都會同時記錄一條回滾操作（回滾操作日誌undo log）。同一條記錄在系統中可以存在多個版本，這就是資料庫的多版本併發控制（MVCC）。即通過回滾（rollback操作），可以回到前一個狀態的值。

假設一個值從 1 被按順序改成了 2、3、4，在回滾日誌裡面就會有類似下面的記錄。

徹底搞懂 MySQL 事務的隔離級別

當前值是 4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的 read-view。如圖中看到的，在檢視 A、B、C 裡面，這一個記錄的值分別是 1、2、4，同一條記錄在系統中可以存在多個版本，就是資料庫的多版本併發控制（MVCC）。對於 read-view A，要得到 1，就必須將當前值依次執行圖中所有的回滾操作得到。

同時你會發現，即使現在有另外一個事務正在將 4 改成 5，這個事務跟 read-view A、B、C 對應的事務是不會衝突的。

提問：回滾操作日誌（undo log）什麼時候刪除？

MySQL會判斷當沒有事務需要用到這些回滾日誌的時候，回滾日誌會被刪除。

提問：什麼時候不需要了？

當系統裡麼有比這個回滾日誌更早的read-view的時候。

檢視當前會話隔離級別

方式1

命令：SHOW VARIABLES LIKE 'transaction_isolation';

mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name  | Value |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+

方式2

命令：SELECT @@transaction_isolation;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+

設定隔離級別

方式1：通過set命令

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中level有4種值：
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}
關鍵詞：GLOBAL
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
* 只對執行完該語句之後產生的會話起作用
* 當前已經存在的會話無效
關鍵詞：SESSION
SET SESSION TRANSACTION ISOLATION LEVEL level;
* 對當前會話的所有後續的事務有效
* 該語句可以在已經開啟的事務中間執行，但不會影響當前正在執行的事務
* 如果在事務之間執行，則對後續的事務有效。
無關鍵詞
SET TRANSACTION ISOLATION LEVEL level;
* 只對當前會話中下一個即將開啟的事務有效
* 下一個事務執行完後，後續事務將恢復到之前的隔離級別
* 該語句不能在已經開啟的事務中間執行，會報錯的

方式2：通過服務啟動項命令

可以修改啟動引數transaction-isolation的值

比方說我們在啟動伺服器時指定了--transaction-isolation=READ UNCOMMITTED，那麼事務的預設隔離級別就從原來的REPEATABLE READ變成了READ UNCOMMITTED。

參考資料：

掘金小冊：《MySQL 是怎樣執行的：從根兒上理解 MySQL》

極客時間專欄：《MySQL實戰45講》

本作品採用《CC 協議》，轉載必須註明作者和本文連結

阿德

相關文章