一文徹底讀懂MySQL事務的四大隔離級別

Jay_huaxiao發表於2020-04-05
MySql

前言

之前分析一個死鎖問題，發現自己對資料庫隔離級別理解還不夠清楚，所以趁著這幾天假期，整理一下MySQL事務的四大隔離級別相關知識，希望對大家有幫助~

一文徹底讀懂MySQL事務的四大隔離級別

事務

什麼是事務？

事務，由一個有限的資料庫操作序列構成，這些操作要麼全部執行,要麼全部不執行，是一個不可分割的工作單位。

假如A轉賬給B 100 元，先從A的賬戶里扣除 100 元，再在 B 的賬戶上加上 100 元。如果扣完A的100元后，還沒來得及給B加上，銀行系統異常了，最後導致A的餘額減少了，B的餘額卻沒有增加。所以就需要事務，將A的錢回滾回去，就是這麼簡單。

事務的四大特性

一文徹底讀懂MySQL事務的四大隔離級別

  • 原子性： 事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部都執行，要麼都不執行。
  • 一致性： 指在事務開始之前和事務結束以後，資料不會被破壞，假如A賬戶給B賬戶轉10塊錢，不管成功與否，A和B的總金額是不變的。
  • 隔離性： 多個事務併發訪問時，事務之間是相互隔離的，一個事務不應該被其他事務干擾，多個併發事務之間要相互隔離。。
  • 永續性： 表示事務完成提交後，該事務對資料庫所作的操作更改，將持久地儲存在資料庫之中。

事務併發存在的問題

事務併發執行存在什麼問題呢，換句話說就是，一個事務是怎麼干擾到其他事務的呢？看例子吧~

假設現在有表：

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_name_idx` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
複製程式碼

表中有資料：

一文徹底讀懂MySQL事務的四大隔離級別

髒讀（dirty read）

假設現在有兩個事務A、B：

  • 假設現在A的餘額是100，事務A正在準備查詢Jay的餘額
  • 這時候，事務B先扣減Jay的餘額，扣了10
  • 最後A 讀到的是扣減後的餘額

一文徹底讀懂MySQL事務的四大隔離級別

由上圖可以發現，事務A、B交替執行，事務A被事務B干擾到了，因為事務A讀取到事務B未提交的資料,這就是髒讀。

不可重複讀（unrepeatable read）

假設現在有兩個事務A和B：

  • 事務A先查詢Jay的餘額，查到結果是100
  • 這時候事務B 對Jay的賬戶餘額進行扣減，扣去10後，提交事務
  • 事務A再去查詢Jay的賬戶餘額發現變成了90

一文徹底讀懂MySQL事務的四大隔離級別

事務A又被事務B干擾到了！在事務A範圍內，兩個相同的查詢，讀取同一條記錄，卻返回了不同的資料，這就是不可重複讀。

幻讀

假設現在有兩個事務A、B：

  • 事務A先查詢id大於2的賬戶記錄，得到記錄id=2和id=3的兩條記錄
  • 這時候，事務B開啟，插入一條id=4的記錄，並且提交了
  • 事務A再去執行相同的查詢，卻得到了id=2,3,4的3條記錄了。

一文徹底讀懂MySQL事務的四大隔離級別

事務A查詢一個範圍的結果集，另一個併發事務B往這個範圍中插入/刪除了資料，並靜悄悄地提交，然後事務A再次查詢相同的範圍，兩次讀取得到的結果集不一樣了，這就是幻讀。

事務的四大隔離級別實踐

既然併發事務存在髒讀、不可重複、幻讀等問題，InnoDB實現了哪幾種事務的隔離級別應對呢？

  • 讀未提交（Read Uncommitted）
  • 讀已提交（Read Committed）
  • 可重複讀（Repeatable Read）
  • 序列化（Serializable）

讀未提交（Read Uncommitted）

想學習一個知識點，最好的方式就是實踐之。好了，我們去資料庫給它設定讀未提交隔離級別，實踐一下吧~

一文徹底讀懂MySQL事務的四大隔離級別

先把事務隔離級別設定為read uncommitted，開啟事務A，查詢id=1的資料

set session transaction isolation level read uncommitted;
begin;
select * from account where id =1;
複製程式碼

結果如下：

一文徹底讀懂MySQL事務的四大隔離級別
這時候，另開一個視窗開啟mysql，也把當前事務隔離級別設定為read uncommitted，開啟事務B，執行更新操作

set session transaction isolation level read uncommitted;
begin;
update account set balance=balance+20 where id =1;
複製程式碼

接著回事務A的視窗，再查account表id=1的資料，結果如下：

一文徹底讀懂MySQL事務的四大隔離級別

可以發現，在讀未提交（Read Uncommitted） 隔離級別下，一個事務會讀到其他事務未提交的資料的，即存在髒讀問題。事務B都還沒commit到資料庫呢，事務A就讀到了，感覺都亂套了。。。實際上，讀未提交是隔離級別最低的一種。

已提交讀（READ COMMITTED）

為了避免髒讀，資料庫有了比讀未提交更高的隔離級別，即已提交讀。

一文徹底讀懂MySQL事務的四大隔離級別

把當前事務隔離級別設定為已提交讀（READ COMMITTED），開啟事務A，查詢account中id=1的資料

set session transaction isolation level read committed;
begin;
select * from account where id =1;
複製程式碼

另開一個視窗開啟mysql，也把事務隔離級別設定為read committed，開啟事務B，執行以下操作

set session transaction isolation level read committed;
begin;
update account set balance=balance+20 where id =1;
複製程式碼

接著回事務A的視窗，再查account資料，發現資料沒變：

一文徹底讀懂MySQL事務的四大隔離級別

我們再去到事務B的視窗執行commit操作：

commit;
複製程式碼

最後回到事務A視窗查詢，發現資料變了：

一文徹底讀懂MySQL事務的四大隔離級別

由此可以得出結論，隔離級別設定為已提交讀（READ COMMITTED） 時，已經不會出現髒讀問題了，當前事務只能讀取到其他事務提交的資料。但是，你站在事務A的角度想想，存在其他問題嗎？

提交讀的隔離級別會有什麼問題呢？

在同一個事務A裡，相同的查詢sql，讀取同一條記錄（id=1），讀到的結果是不一樣的，即不可重複讀。所以，隔離級別設定為read committed的時候，還會存在不可重複讀的併發問題。

可重複讀（Repeatable Read）

如果你的老闆要求，在同個事務中，查詢結果必須是一致的，即老闆要求你解決不可重複的併發問題，怎麼辦呢？老闆，臣妾辦不到？來實踐一下可重複讀（Repeatable Read） 這個隔離級別吧~

一文徹底讀懂MySQL事務的四大隔離級別

哈哈，步驟1、2、6的查詢結果都是一樣的，即repeatable read解決了不可重複讀問題，是不是心裡美滋滋的呢，終於解決老闆的難題了~

RR級別是否解決了幻讀問題呢？

再來看看網上的一個熱點問題，有關於RR級別下，是否解決了幻讀問題？我們來實踐一下：

一文徹底讀懂MySQL事務的四大隔離級別
由圖可得，步驟2和步驟6查詢結果集沒有變化，看起來RR級別是已經解決幻讀問題了~ 但是呢，RR級別還是存在這種現象：
一文徹底讀懂MySQL事務的四大隔離級別

其實，上圖如果事務A中，沒有update account set balance=200 where id=5;這步操作，select * from account where id>2查詢到的結果集確實是不變，這種情況沒有幻讀問題。但是，有了update這個騷操作，同一個事務，相同的sql，查出的結果集不同，這個是符合了幻讀的定義~

這個問題，親愛的朋友，你覺得它算幻讀問題嗎？

序列化（Serializable）

前面三種資料庫隔離級別，都有一定的併發問題，現在放大招吧，實踐SERIALIZABLE隔離級別。

把事務隔離級別設定為Serializable，開啟事務A，查詢account表資料

set session transaction isolation level serializable;
select @@tx_isolation;
begin;
select * from account;
複製程式碼

另開一個視窗開啟mysql，也把事務隔離級別設定為Serializable，開啟事務B，執行插入一條資料：

set session transaction isolation level serializable;
select @@tx_isolation;
begin;
insert into account(id,name,balance) value(6,'Li',100);
複製程式碼

執行結果如下：

一文徹底讀懂MySQL事務的四大隔離級別

由圖可得，當資料庫隔離級別設定為serializable的時候，事務B對錶的寫操作，在等事務A的讀操作。其實，這是隔離級別中最嚴格的，讀寫都不允許併發。它保證了最好的安全性，效能卻是個問題~

MySql隔離級別的實現原理

實現隔離機制的方法主要有兩種：

  • 讀寫鎖
  • 一致性快照讀，即 MVCC

MySql使用不同的鎖策略(Locking Strategy)/MVCC來實現四種不同的隔離級別。RR、RC的實現原理跟MVCC有關，RU和Serializable跟鎖有關。

讀未提交（Read Uncommitted）

官方說法：

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.

讀未提交，採取的是讀不加鎖原理。

  • 事務讀不加鎖，不阻塞其他事務的讀和寫
  • 事務寫阻塞其他事務寫，但不阻塞其他事務讀；

序列化（Serializable)

官方的說法:

InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

  • 所有SELECT語句會隱式轉化為SELECT ... FOR SHARE，即加共享鎖。
  • 讀加共享鎖，寫加排他鎖，讀寫互斥。如果有未提交的事務正在修改某些行，所有select這些行的語句都會阻塞。

MVCC的實現原理

MVCC，中文叫多版本併發控制，它是通過讀取歷史版本的資料，來降低併發事務衝突，從而提高併發效能的一種機制。它的實現依賴於隱式欄位、undo日誌、快照讀&當前讀、Read View，因此，我們先來了解這幾個知識點。

隱式欄位

對於InnoDB儲存引擎，每一行記錄都有兩個隱藏列DB_TRX_ID、DB_ROLL_PTR，如果表中沒有主鍵和非NULL唯一鍵時，則還會有第三個隱藏的主鍵列DB_ROW_ID。

  • DB_TRX_ID，記錄每一行最近一次修改（修改/更新）它的事務ID，大小為6位元組；
  • DB_ROLL_PTR，這個隱藏列就相當於一個指標，指向回滾段的undo日誌，大小為7位元組；
  • DB_ROW_ID，單調遞增的行ID，大小為6位元組；

一文徹底讀懂MySQL事務的四大隔離級別

undo日誌

  • 事務未提交的時候，修改資料的映象（修改前的舊版本），存到undo日誌裡。以便事務回滾時，恢復舊版本資料，撤銷未提交事務資料對資料庫的影響。
  • undo日誌是邏輯日誌。可以這樣認為，當delete一條記錄時，undo log中會記錄一條對應的insert記錄，當update一條記錄時，它記錄一條對應相反的update記錄。
  • 儲存undo日誌的地方，就是回滾段。

多個事務並行操作某一行資料時，不同事務對該行資料的修改會產生多個版本，然後通過回滾指標（DB_ROLL_PTR）連一條Undo日誌鏈。

我們通過例子來看一下~

mysql> select * from account ;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | Jay  |     100 |
+----+------+---------+
1 row in set (0.00 sec)
複製程式碼
  • 假設表accout現在只有一條記錄，插入該該記錄的事務Id為100
  • 如果事務B（事務Id為200），對id=1的該行記錄進行更新，把balance值修改為90

事務B修改後，形成的Undo Log鏈如下：

一文徹底讀懂MySQL事務的四大隔離級別

快照讀&當前讀

快照讀：

讀取的是記錄資料的可見版本（有舊的版本），不加鎖,普通的select語句都是快照讀,如：

select * from account where id>2;
複製程式碼

當前讀：

讀取的是記錄資料的最新版本，顯示加鎖的都是當前讀

select * from account where id>2 lock in share mode;
select * from  account where id>2 for update;
複製程式碼

Read View

  • Read View就是事務執行快照讀時，產生的讀檢視。
  • 事務執行快照讀時，會生成資料庫系統當前的一個快照，記錄當前系統中還有哪些活躍的讀寫事務，把它們放到一個列表裡。
  • Read View主要是用來做可見性判斷的，即判斷當前事務可見哪個版本的資料~

為了下面方便討論Read View可見性規則，先定義幾個變數

  • m_ids:當前系統中那些活躍的讀寫事務ID,它資料結構為一個List。
  • min_limit_id:m_ids事務列表中，最小的事務ID
  • max_limit_id:m_ids事務列表中，最大的事務ID
  • 如果DB_TRX_ID < min_limit_id，表明生成該版本的事務在生成ReadView前已經提交(因為事務ID是遞增的)，所以該版本可以被當前事務訪問。
  • 如果DB_TRX_ID > m_ids列表中最大的事務id，表明生成該版本的事務在生成ReadView後才生成，所以該版本不可以被當前事務訪問。
  • 如果 min_limit_id =<DB_TRX_ID<= max_limit_id,需要判斷m_ids.contains(DB_TRX_ID)，如果在，則代表Read View生成時刻，這個事務還在活躍，還沒有Commit，你修改的資料，當前事務也是看不見的；如果不在，則說明，你這個事務在Read View生成之前就已經Commit了，修改的結果，當前事務是能看見的。

注意啦！！ RR跟RC隔離級別，最大的區別就是：RC每次讀取資料前都生成一個ReadView，而RR只在第一次讀取資料時生成一個ReadView。

已提交讀（READ COMMITTED） 存在不可重複讀問題的分析歷程

我覺得理解一個新的知識點，最好的方法就是居於目前存在的問題/現象，去分析它的來龍去脈~ RC的實現也跟MVCC有關，RC是存在重複讀併發問題的，所以我們來分析一波RC吧，先看一下執行流程

一文徹底讀懂MySQL事務的四大隔離級別
假設現在系統裡有A，B兩個事務在執行，事務ID分別為100、200，並且假設存在的老資料，插入事務ID是50哈~

事務A 先執行查詢1的操作

# 事務A，Transaction ID 100
begin ;
查詢1：select *  from account WHERE id = 1; 
複製程式碼

事務 B 執行更新操作，id =1記錄的undo日誌鏈如下

begin;
update account set balance =balance+20 where id =1;
複製程式碼

一文徹底讀懂MySQL事務的四大隔離級別
回到事務A，執行查詢2的操作

begin ;
查詢1：select *  from account WHERE id = 1; 
查詢2：select *  from account WHERE id = 1; 
複製程式碼

查詢2執行分析：

  • 事務A在執行到SELECT語句時，重新生成一個ReadView，因為事務B（200）在活躍，所以ReadView的m_ids列表內容就是[200]
  • 由上圖undo日誌鏈可得，最新版本的balance為1000，它的事務ID為200，在活躍事務列表裡，所以當前事務（事務A）不可見。
  • 我們繼續找下一個版本，balance為100這行記錄，事務Id為50，小於活躍事務ID列表最小記錄200，所以這個版本可見，因此，查詢2的結果，就是返回balance=100這個記錄~~

我們回到事務B，執行提交操作，這時候undo日誌鏈不變

begin;
update account set balance =balance+20 where id =1;
commit
複製程式碼

一文徹底讀懂MySQL事務的四大隔離級別

再次回到事務A，執行查詢3的操作

begin ;
查詢1：select *  from account WHERE id = 1; 
查詢2：select *  from account WHERE id = 1; 
查詢3：select *  from account WHERE id = 1; 
複製程式碼

查詢3執行分析：

  • 事務A在執行到SELECT語句時，重新生成一個ReadView，因為事務B（200）已經提交，不載活躍，所以ReadView的m_ids列表內容就是空的了。
  • 所以事務A直接讀取最新紀錄，讀取到balance =120這個版本的資料。

所以，這就是RC存在不可重複讀問題的過程啦~有不理解的地方可以多讀幾遍哈~

可重複讀（Repeatable Read）解決不可重複讀問題的一次分析

我們再來分析一波，RR隔離級別是如何解決不可重複讀併發問題的吧~

你可能會覺得兩個併發事務的例子太簡單了，好的！我們現在來點刺激的，開啟三個事務~

一文徹底讀懂MySQL事務的四大隔離級別
假設現在系統裡有A，B，C兩個事務在執行，事務ID分別為100、200，300，存量資料插入的事務ID是50~

# 事務A，Transaction ID 100
begin ;
UPDATE account SET balance = 1000  WHERE id = 1;
複製程式碼
# 事務B，Transaction ID 200
begin ; //開個事務，佔坑先
複製程式碼

這時候，account表中，id =1記錄的undo日誌鏈如下：

一文徹底讀懂MySQL事務的四大隔離級別

# 事務C，Transaction ID 300
begin ;
//查詢1：select * from  account WHERE id = 1;
複製程式碼

查詢1執行過程分析：

  • 事務C在執行SELECT語句時，會先生成一個ReadView。因為事務A（100）、B（200）在活躍，所以ReadView的m_ids列表內容就是[100, 200]。
  • 由上圖undo日誌鏈可得，最新版本的balance為1000，它的事務ID為100，在活躍事務列表裡，所以當前事務（事務C）不可見。
  • 我們繼續找下一個版本，balance為100這行記錄，事務Id為50，小於活躍事務ID列表最小記錄100，所以這個版本可見，因此，查詢1的結果，就是返回balance=100這個記錄~~

接著，我們把事務A提交一下：

# 事務A，Transaction ID 100
begin ;
UPDATE account SET balance = 1000  WHERE id = 1;
commit;
複製程式碼

在事務B中，執行更新操作，把id=1的記錄balance修改為2000，更新完後，undo 日誌鏈如下：

# 事務B，Transaction ID 200
begin ; //開個事務，佔坑先
UPDATE account SET balance = 2000  WHERE id = 1;
複製程式碼

一文徹底讀懂MySQL事務的四大隔離級別

回到事務C，執行查詢2

# 事務C，Transaction ID 300
begin ;
//查詢1：select * from  account WHERE id = 1;
//查詢2：select * from  account WHERE id = 1;
複製程式碼

查詢2:執行分析：

  • 在RR 級別下，執行查詢2的時候，因為前面ReadView已經生成過了，所以直接服用之前的ReadView，活躍事務列表為[100,200].
  • 由上圖undo日誌鏈可得，最新版本的balance為2000，它的事務ID為200，在活躍事務列表裡，所以當前事務（事務C）不可見。
  • 我們繼續找下一個版本，balance為1000這行記錄，事務Id為100,也在活躍事務列表裡，所以當前事務（事務C）不可見。
  • 繼續找下一個版本，balance為100這行記錄，事務Id為50，小於活躍事務ID列表最小記錄100，所以這個版本可見，因此，查詢2的結果，也是返回balance=100這個記錄~~

鎖相關概念補充（附）：

共享鎖與排他鎖

InnoDB 實現了標準的行級鎖，包括兩種：共享鎖（簡稱 s 鎖）、排它鎖（簡稱 x 鎖）。

  • 共享鎖（S鎖）：允許持鎖事務讀取一行。
  • 排他鎖（X鎖）：允許持鎖事務更新或者刪除一行。

如果事務 T1 持有行 r 的 s 鎖，那麼另一個事務 T2 請求 r 的鎖時，會做如下處理：

  • T2 請求 s 鎖立即被允許，結果 T1 T2 都持有 r 行的 s 鎖
  • T2 請求 x 鎖不能被立即允許

如果 T1 持有 r 的 x 鎖，那麼 T2 請求 r 的 x、s 鎖都不能被立即允許，T2 必須等待T1釋放 x 鎖才可以，因為X鎖與任何的鎖都不相容。

記錄鎖（Record Locks）

  • 記錄鎖是最簡單的行鎖，僅僅鎖住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
  • 記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB也會隱式的建立一個索引，並使用這個索引實施記錄鎖。
  • 會阻塞其他事務對其插入、更新、刪除

記錄鎖的事務資料（關鍵詞：lock_mode X locks rec but not gap），記錄如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;
複製程式碼

間隙鎖（Gap Locks）

  • 間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。
  • 使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條資料。
  • 間隙鎖只阻止其他事務插入到間隙中，他們不阻止其他事務在同一個間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用。

Next-Key Locks

  • Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。

RC級別存在幻讀分析

因為RC是存在幻讀問題的，所以我們先切到RC隔離級別，分析一波~

假設account表有4條資料。

  • 開啟事務A，執行當前讀，查詢id>2的所有記錄。
  • 再開啟事務B，插入id=5的一條資料。
  • 事務B插入資料成功後，再修改id=3的記錄
  • 回到事務A,再次執行id>2的當前讀查詢

一文徹底讀懂MySQL事務的四大隔離級別

  • 事務B可以插入id=5的資料，卻更新不了id=3的資料，陷入阻塞。證明事務A在執行當前讀的時候在id =3和id=4這兩條記錄上加了鎖，但是並沒有對 id > 2 這個範圍加鎖~
  • 事務B陷入阻塞後，切回事務A執行當前讀操作時，死鎖出現。因為事務B在 insert 的時候，會在新紀錄（id=5）上加鎖，所以事務A再次執行當前讀，想獲取id> 3 的記錄，就需要在 id=3,4,5 這3條記錄上加鎖，但是 id = 5這條記錄已經被事務B 鎖住了，於是事務A被事務B阻塞，同時事務B還在等待 事務A釋放 id = 3上的鎖，最終產生了死鎖。

一文徹底讀懂MySQL事務的四大隔離級別

因此，我們可以發現，RC隔離級別下，加鎖的select, update, delete等語句，使用的是記錄鎖，其他事務的插入依然可以執行，因此會存在幻讀~

RR 級別解決幻讀分析

因為RR是解決幻讀問題的，怎麼解決的呢，分析一波吧~

假設account表有4條資料，RR級別。

  • 開啟事務A，執行當前讀，查詢id>2的所有記錄。
  • 再開啟事務B，插入id=5的一條資料。
    一文徹底讀懂MySQL事務的四大隔離級別
    可以發現，事務B執行插入操作時，阻塞了~因為事務A在執行select ... lock in share mode的時候，不僅在 id = 3,4 這2條記錄上加了鎖，而且在id > 2 這個範圍上也加了間隙鎖。

因此，我們可以發現，RR隔離級別下，加鎖的select, update, delete等語句，會使用間隙鎖+ 臨鍵鎖，鎖住索引記錄之間的範圍，避免範圍間插入記錄，以避免產生幻影行記錄。

參考與感謝

個人公眾號

一文徹底讀懂MySQL事務的四大隔離級別

  • 覺得寫得好的小夥伴給個點贊+關注啦，謝謝~
  • 如果有寫得不正確的地方，麻煩指出，感激不盡。
  • 同時非常期待小夥伴們能夠關注我公眾號，後面慢慢推出更好的乾貨~嘻嘻

相關文章