MySQL交易隔離等級的實現原理

MySQL交易隔離等級的實現原理
回顧

在MySQL的眾多儲存引擎中，只有InnoDB支援事務，所有這裡說的交易隔離等級指的是InnoDB下的交易隔離等級。

讀未提交：一個事務可以讀取到另一個事務未提交的修改。這會帶來髒讀、幻讀、不可重複讀取問題。（基本沒用）

讀已提交：一個事務只能讀取另一個事務已經提交的修改。其避免了髒讀，但仍然存在不可重複讀取和幻讀問題。

可重複讀：同一個事務中多次讀取相同的資料返回的結果是一樣的。其避免了髒讀和不可重複讀取問題，但幻讀依然存在。

序列化：事務串列執行。避免了以上所有問題。

以上是SQL-92標準中定義的四種隔離等級。在MySQL中，預設的隔離等級是REPEATABLE-READ（可重複讀），並且解決了幻讀問題。簡單的來說，mysql的預設隔離等級解決了髒讀、幻讀、不可重複讀取問題。

不可重複讀取重點在於update和delete，而幻讀的重點在於insert。

在這裡，我們只討論可重複讀。

知識儲備MVCC

譯註：

　　MVCC的全稱是“多版本並發控制”。這項技術使得InnoDB的交易隔離等級下執行一致性讀操作有了保證，換言之，就是為了查詢一些正在被另一個事務更新的行，並且可以看到它們被更新之前的值。這是一個可以用來增強並發性的強大的技術，因為這樣的一來的話查詢就不用等待另一個事務釋放鎖。這項技術在資料庫領域並不是普遍使用的。一些其它的資料庫產品，以及mysql其它的儲存引擎並不支援它。

 

說明

網上看到大量的文章講到MVCC都是說給沒一行增加兩個隱藏的欄位分別表示行的建立時間以及到期時間，它們儲存的並不是時間，而是事務版本號碼。

事實上，這種說法並不準確，嚴格的來講，InnoDB會給資料庫中的每一行增加三個欄位，它們分別是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

但是，為了理解的方便，我們可以這樣去理解，索引接下來的講解中也還是用這兩個欄位的方式去理解。

 

增刪查改

在InnoDB中，給每行增加兩個隱藏欄位來實現MVCC，一個用來記錄資料行的建立時間，另一個用來記錄行的到期時間（刪除時間）。在實際操作中，儲存的並不是時間，而是事務的版本號碼，每開啟一個新事務，事務的版本號碼就會遞增。

於是乎，預設的隔離等級（REPEATABLE READ）下，增刪查改變成了這樣：

• SELECT
  • 讀取建立版本小於或等於當前事務版本號碼，並且刪除版本為空白或大於當前事務版本號碼的記錄。這樣可以保證在讀取之前記錄是存在的。
• INSERT
  • 將當前事務的版本號碼儲存至行的建立版本號碼
• UPDATE
  • 新插入一行，並以當前事務的版本號碼作為新行的建立版本號碼，同時將原記錄行的刪除版本號碼設定為當前事務版本號碼
• DELETE
  • 將當前事務的版本號碼儲存至行的刪除版本號碼

 

快照讀和當前讀

快照讀：讀取的是快照版本，也就是曆史版本

當前讀：讀取的是最新版本

普通的SELECT就是快照讀，而UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是當前讀。

 

一致性非鎖定讀和鎖定讀鎖定讀

　　在一個事務中，標準的SELECT語句是不會加鎖，但是有兩種情況例外。SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。

　　SELECT ... LOCK IN SHARE MODE

　　給記錄假設共用鎖定，這樣一來的話，其它事務只能讀不能修改，直到當前事務提交

　　SELECT ... FOR UPDATE

　　給索引記錄加鎖，這種情況下跟UPDATE的加鎖情況是一樣的

一致性非鎖定讀

　　consistent read （一致性讀），InnoDB用多版本來提供查詢資料庫在某個時間點的快照。如果隔離等級是REPEATABLE READ，那麼在同一個事務中的所有一致性讀都讀的是事務中第一個這樣的讀讀到的快照；如果是READ COMMITTED，那麼一個事務中的每一個一致性讀都會讀到它自己重新整理的快照版本。Consistent read（一致性讀）是READ COMMITTED和REPEATABLE READ隔離等級下普通SELECT語句預設的模式。一致性讀不會給它所訪問的表加任何形式的鎖，因此其它事務可以同時並發的修改它們。

 

悲觀鎖和樂觀鎖

悲觀鎖，正如它的名字那樣，資料庫總是認為別人會去修改它所要操作的資料，因此在資料庫處理過程中將資料加鎖。其實現依靠資料庫底層。

樂觀鎖，如它的名字那樣，總是認為別人不會去修改，只有在提交更新的時候去檢查資料的狀態。通常是給資料增加一個欄位來標識資料的版本。

 

鎖

有這樣三種鎖我們需要瞭解

• Record Locks（記錄鎖）：在索引記錄上加鎖。
• Gap Locks（間隙鎖）：在索引記錄之間加鎖，或者在第一個索引記錄之前加鎖，或者在最後一個索引記錄之後加鎖。
• Next-Key Locks：在索引記錄上加鎖，並且在索引記錄之前的間隙加鎖。它相當於是Record Locks與Gap Locks的一個結合。

假設一個索引包含以下幾個值：10,11,13,20。那麼這個索引的next-key鎖將會覆蓋以下區間：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

 

瞭解了以上概念之後，接下來具體就簡單分析下REPEATABLE READ隔離等級是如何?的

理論分析

之所以說是理論分析，是因為要是實際操作證明的話我也不知道怎麼去證明，畢竟作者水平實在有限。

但是，這並不意味著我在此胡說八道，有官方文檔為證。

這段話的大致意思是，在預設的隔離等級中，普通的SELECT用的是一致性讀不加鎖。而對於鎖定讀、UPDATE和DELETE，則需要加鎖，至於加什麼鎖視情況而定。如果你對一個唯一索引使用了唯一的檢索條件，那麼只需鎖定索引記錄即可；如果你沒有使用唯一索引作為檢索條件，或者用到了索引範圍掃描，那麼將會使用間隙鎖或者next-key鎖以此來阻塞其它會話向這個範圍內的間隙插入資料。

作者曾經有一個誤區，認為按照前面說MVCC下的增刪查改的行為就不會出現任何問題，也不會出現不可重複讀取和幻讀。但其實是大錯特錯。

舉個很簡單的例子，假設事務A更新表中id=1的記錄，而事務B也更新這條記錄，並且B先提交，如果按照前面MVVC說的，事務A讀取id=1的快照版本，那麼它看不到B所提交的修改，此時如果直接更新的話就會覆蓋B之前的修改，這就不對了，可能B和A修改的不是一個欄位，但是這樣一來，B的修改就丟失了，這是不允許的。

所以，在修改的時候一定不是快照讀，而是當前讀。

而且，前面也講過只有普通的SELECT才是快照讀，其它諸如UPDATE、刪除都是當前讀。修改的時候加鎖這是必然的，同時為了防止幻讀的出現還需要加間隙鎖。

• 一致性讀保證了可用重複讀
• 間隙鎖防止了幻讀

回想一下

1、利用MVCC實現一致性非鎖定讀，這就有保證在同一個事務中多次讀取相同的資料返回的結果是一樣的，解決了不可重複讀取的問題

2、利用Gap Locks和Next-Key可以阻止其它事務在鎖定區間插入入資料，因此解決了幻讀問題

綜上所述，預設隔離等級的實現依賴於MVCC和鎖，再具體一點是一致性讀和鎖。

 

示範

上面四幅對比，可以看到由於id是主鍵，用id作為檢索條件時只鎖定那一個索引記錄。接下來，看索引範圍的例子

這兩幅，可以看出，由於沒有使用唯一索引作為檢索條件，導致不光鎖定了索引記錄，還鎖定了索引之間的間隙，應該是是使用了next-key鎖。

參考 https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html