事務的隔離級別與MVCC

蟬沐風發表於2022-04-21
MVC

提到資料庫，你多半會聯想到事務，進而還可能想起曾經背得滾瓜亂熟的ACID，不知道你有沒有想過這個問題，事務有原子性、隔離性、一致性和永續性四大特性，為什麼偏偏給隔離性設定了級別？

被隔離的隔離性

一切還得從事務說起。

1. 事務（transaction）的起源

學習資料庫事務的時候，一個典型的案例就是「轉賬」，這篇文章也不能免俗，故事就從招財向陀螺借100塊錢開始吧。

一個看似非常簡單的現實世界的狀態轉換，轉換成資料庫中的操作卻並沒有那麼單純。這個看起來很簡單的借錢操作至少包含了兩個動作：

  • 陀螺的賬戶餘額-100
  • 招財的賬戶餘額+100

要保證轉賬操作的成功，資料庫必須把這兩個操作作為一個邏輯整體來執行，這個邏輯整體就是一個事務。

1.1. 事務的定義

事務就是包含有限個（單條或多條）資料庫操作（增刪改查）的、最小的邏輯工作單元（不可再分）。

說到這裡不得不吐槽一下，事務的英文是transaction，直譯為“交易”的意思，但是不知道為什麼被意譯成了“事務”，讓人很難從字面上理解這個概念的含義。

中國人對翻譯的“信達雅”的偏執在計算機領域或多或少有點不討喜。

1.2. 哪些儲存引擎支援事務

並不是所有的資料庫或者所有的儲存引擎都支援事務。

對於MySQL而言，事務作為一種功能特性由儲存引擎提供。目前支援事務功能的儲存引擎只有InnoDB和NDB，鑑於InnoDB目前是MySQL預設的儲存引擎，我們的研究重點自然也就是InnoDB儲存引擎了。

因此文章接下來預設的儲存引擎就是InnoDB，特殊情況下會特別指出。

那麼InnoDB在什麼情況下才會出現事務呢？

2. MySQL的事務語法

如果你不是DBA，在平時和MySQL的互動中你可能極少直接使用到它的事務語法，一切都被程式設計框架封裝得很好了。但是現在我們要直接使用MySQL進行事務的研究了，拋開框架，跟我稍微回顧一下語法，這是非常必要的。

2.1. 自動提交

當我執行這樣單獨一條更新語句的時候，它會有事務嗎？

UPDATE user_innodb SET name = '蟬沐風' WHERE id = 1;

實際上，這條語句不僅會自動開啟一個事務，而且執行完畢之後還會自動提交事務，並持久化資料。

這是MySQL預設情況下使用的方式——自動提交。在此方式下，增刪改的SQL語句會自動開啟事務，並且是一條SQL一個事務。

自動提交的方式雖然簡單，但是對於轉賬這種涉及到多條SQL的業務，就不太適合了。因此，MySQL提供了手動開啟事務的方法。

2.2. 手動操作事務

2.2.1. 開啟事務

可以使用下面兩種語句開啟一個事務

  • BEGIN
  • START TRANSACTION

對比BEGIN而言，START TRANSACTION後面可以新增一些操作符，不過這不是我們的研究重點，可以不必理會。

2.2.2. 提交或回滾

開啟事務之後就可以繼續編寫需要放到當前事務中的SQL語句了。當寫完最後一條語句，如果你覺得寫得沒問題，你可以提交事務；反之你後悔了，想把資料庫恢復到之前的狀態，你可以回滾事務。

  • 提交事務 COMMIT
  • 回滾事務 ROLLBACK

2.3. autocommit系統變數

MySQL提供了一個叫做autocommit的系統變數，用來表示是否開啟自動提交：

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

autocommit的預設值為ON，表示預設開啟自動提交。但是自動提交和手動操作事務並不衝突，如果不顯式使用BEGIN或START TRANSACTION開啟一個事務，那麼InnoDB會在每一條增刪改語句執行之後提交事務。

如果我們把autocommit設為OFF，除非我們手動使用BEGIN或START TRANSACTION開啟一個事務，否則InnoDB絕不會自動開啟事務；同樣，除非我們使用COMMIT或ROLLBACK提交或回滾事務，否則InnoDB不會自動結束事務。

實際上，InnoDB會因為某些特殊語句的執行或客戶端連線斷開等特殊情況而導致事務自動提交（即使我們沒有手動輸入COMMIT），這種情況叫做隱式提交。

3. 事務併發執行導致的讀問題

MySQL會使用獨立的執行緒處理每一個客戶端的連線，這就是多執行緒。每個執行緒都可以開啟事務，這就是事務的併發。

不管是多執行緒的併發執行還是事務的併發執行（其實本質上是一回事兒），如果不採取點措施，都會帶來一些問題。

3.1. 髒讀

假設事務T1和T2併發執行，都要訪問user_innodb表中id為1的資料，不同的是T1先讀取資料，緊接著T2修改了資料的name欄位，需要注意的是，T2並沒有提交！

image-20220418142616460

此時，T1再次執行相同的查詢操作，會發現資料發生了變化，name欄位由「王剛蛋」變成了「蟬沐風」。

如果一個事務讀到了另一個未提交事務修改過的資料，而導致了前後兩次讀取的資料不一致的情況，這種事務併發問題叫做髒讀。

3.2. 不可重複讀

同樣是T1和T2兩個事務，T1通過id=1查詢到了一條資料，然後T2緊接著UPDATE（DELETE也可以）了該條記錄，不同的是，T2緊接著通過COMMIT提交了事務。

image-20220418144329110

此時，T1再次執行相同的查詢操作，會發現資料發生了變化，name欄位由「王剛蛋」變成了「蟬沐風」。

如果一個事務讀到了另一個已提交事務修改過的（或者是刪除的）資料，而導致了前後兩次讀取的資料不一致的情況，這種事務併發問題叫做不可重複讀。

看到這裡是不是有點懵了？怎麼讀到未提交事務修改的資料是併發問題，讀到已提交事務修改的資料還是併發問題呢？

這裡先不急著回答你，因為還有個幻讀呢。

3.3. 幻讀

還是T1和T2這倆貨，T1先查詢了所有name為「王剛蛋」的使用者資訊，此時發現擁有這個硬漢名字的使用者只有一個。然後T2插入了一個同樣叫做「王剛蛋」的使用者的資訊，並且提交了。

image-20220418150817586

此時，T1再次執行相同的查詢操作，發現相比上次的查詢結果多了一行資料，不由得懷疑自己是不是出了幻覺。

如果一個事務首先根據某些搜尋條件P查詢出一些結果，另一個事務寫入（可以是INSERT，UPDATE）了一些符合搜尋條件P的資料，此時前一個事務再次讀取符合條件P的記錄時就會獲取到之前沒有讀取過的記錄。這個現象叫做幻讀。

4. 回答一些可能存在的問題

現在是中場答疑時間。

  1. 一個事務讀到未提交事務修改的資料不行，讀到已提交事務修改的資料為什麼還不行？

你是不是覺得一個事務讀取到其他事務最新提交的資料是一種正常現象？或者說在多數情況下這是我們期望的一種行為？沒錯，這種現象確實是正常的。不是說不行，而是針對我們討論的讀一致性問題上，這兩種現象都算是併發問題，因為談這個問題的時候我們已經把語境固定死了，就是在同一個事務中的前後兩次SELECT的操作結果不該和其他事務產生瓜葛，否則就是出現了讀一致性問題。

  1. 我只聽說過事務的一致性，沒聽說過讀一致性

事務在併發執行時一共有下面3種情況：

  • 讀-讀：併發事務相繼讀取相同記錄，由於讀取操作本身不會改變記錄的值，因此這種情況下自然不會有併發問題；
  • 讀-寫/寫-讀：一個事務進行讀取操作，另一個事務進行寫（增刪改）操作；
  • 寫-寫：併發事務相繼對相同記錄進行寫（增刪改）操作。

不知道你有沒有注意到上一節的標題是「事務併發執行導致的讀問題」。並且髒讀、不可重複讀和幻讀都是在讀-寫/寫-讀的情況下出現的，那寫-寫情況怎麼辦？

一切的併發問題都可以通過序列化解決，但是序列化效率太低了！

再優化一下，一切併發問題都可以通過加鎖來解決，這種方案我們稱為基於鎖的併發控制（Lock Bases Concurrency Control, LBCC）！但是在讀多寫少的環境下，客戶端連讀取幾條記錄都需要排隊，效率還是太低了！

難不成資料庫有避免給讀操作加鎖就可以解決一致性問題的方法？沒錯，接下來我們要講的就是這個方法，所以我們才把一致性問題分為讀一致性和寫一致性，而寫一致性就得依賴資料庫的鎖機制了。

心急吃不了熱豆腐，這篇文章先給你講明白讀一致性問題。

  1. 不可重複讀和幻讀的最大區別是什麼？

這個問題的答案在網上五花八門，要回答這個問題自然要找官方了。這個官方不是MySQL官方，而是美國國家標準協會（ANSI）。

我們上面談到的髒讀、不可重複讀和幻讀問題都是理論知識，並不涉及到具體的資料庫。考慮到所有資料庫在設計的過程中都可能遇到這些問題，ANSI就制定了一個SQL標準，其中最著名的就是SQL92標準，其中定義了「不可重複讀」和「幻讀」（當然也定義了髒讀，但鑑於沒啥異議，我就沒截圖），我把其中的重點單詞給大家標註了一下，希望大家能徹底搞懂兩者的區別。

image-20220418173409077

我用中文翻譯一下就是：

不可重複讀：事務T1讀取了一條記錄，事務T2修改或者刪除了同一條記錄，並且提交。如果事務T1試圖再次讀取同一條記錄的時候，會讀到被事務T2修改的資料或者壓根讀不到。

幻讀：事務T1首先讀取了符合某些搜尋條件P的一些記錄。然後事務T2執行了某些SQL語句產生了符合搜尋條件P的一條或多條記錄。如果事務T1再次讀取符合條件P的記錄，將會得到不同於之前的資料集。

SQL標準對於不可重複讀已經說得很清楚了，事務T2要對T1讀取的記錄進行修改或者刪除操作，並且必須要提交事務。但是對於幻讀的定義就說得很模糊，尤其是文中使用了generate（生成/產生），再結合one or more rows，我們可以認為事務T2執行了INSERT語句插入了之前沒有讀到的記錄，或者是執行了更新記錄鍵值的UPDATE語句生成了符合T1之前的搜尋條件的記錄，總之只要是事務T1之前沒有讀到的資料，都算是幻影資料，至於事務T2需不需要提交壓根兒沒提。

5. SQL標準與4種隔離級別

如果按照對一致性影響的嚴重程度，對上面提到的3種併發讀問題排個序的話，就是下圖這樣：

image-20220418221702496

我們剛才也提到了，這3種併發讀問題都是理論知識，並不涉及到具體的資料庫。因此SQL標準再次發揮了作用，他們建議資料庫廠家按照他們的規範，提供給使用者4種隔離級別，讓使用者根據自己的業務需要權衡利弊，選擇合適的隔離級別，以此解決所有的併發讀問題（髒讀、不可重複讀、幻讀）或者對某些無關緊要的併發讀問題做出妥協。

SQL標準中定義的隔離級別有如下4種：

  • READ UNCOMMITTED：未提交讀
  • READ COMMITTED：已提交讀
  • REPEATABLE READ：可重複讀
  • SERIALIZABLE：序列化

SQL標準中規定，針對不同的隔離級別，併發事務執行過程中可以發生不同的併發讀問題。

image-20220418225443159

其中綠色箭頭表示隔離級別由弱到強，紅色箭頭表示併發問題的嚴重程度由弱變強。翻譯一下上面的表格就是：

  • 在READ UNCOMMITTED隔離級別下，髒讀、不可重複讀和幻讀都有可能發生。也就是這種隔離級別啥也沒幹；
  • 在READ COMMITTED隔離級別下，不可能發生髒讀現象，但是不可重複讀和幻讀有可能發生；
  • 在REPEATABLE READ隔離級別下，可能發生幻讀現象，但是絕不可能發生髒讀和不可重複讀；
  • 在SERIALIZABLE隔離級別下，上述所有現象都不可能發生。

說完這些，有些人可能像當時的我一樣，依舊是懵的。為什麼要設定隔離級別？事務T1讀到其他事務最新修改的資料難道不好嗎？為什麼這些隔離級別的中文翻譯這麼蹩腳，感覺好不通順啊。為什麼單單給隔離性設定了級別？

5.1. 為什麼要設定隔離級別？

說實話，我至今還沒遇到過需要我手動修改MySQL隔離級別的業務，而且我也相信，短時間也不會出現這種場景。我相信大部分開發者也是一樣。因此，在沒有機會實戰的情況下，要能記住隔離級別的這個概念，必須從需求出發，來理解為什麼需要隔離級別。

我舉一個例子，假設你有一個賬單系統，每個月底需要對你所有的客戶的借貸操作和賬戶餘額進行對賬。對此你寫了一個定時任務，每個月初1號的00:00:00時刻開始啟動對賬業務，由於是隻對上個月的業務進行對賬，所以該時刻之後所有的對該使用者賬戶的寫操作都不應該對對賬事務的讀操作可見。

現在你知道並不是任何情況下都要讀取到最新修改的資料了吧。

5.2. 蹩腳的中文翻譯

至於中文蹩腳的問題，純屬是我個人揣測的了。因為直到現在我都覺得隔離級別的中文翻譯不順口，因此猜測可能讀這篇文章的其中一個你也會和我有同樣的問題呢。我的辦法就是直接用英文代替中文翻譯，純屬個人方法，不好使不要怪我。

5.3. 為什麼單單給隔離性設定了級別？

終於聊到了為什麼單單給隔離性設定了級別這個問題了。如果想想事務的4個特性，也就自然明白這個問題了。

原子性

簡單總結就是一個事務中的語句，要麼全部執行成功，要麼全部執行失敗，不允許存在中間狀態。所以對於原子性沒有級別可以設定，我們總不能提出至少有80%的SQL語句執行成功這種無理的要求吧。

一致性

一致性是事務的最終目標。簡而言之就是資料庫的資料操作之後的最終結果符合我們的預期，符合現實世界的規定。比如，陀螺賬戶裡有100塊錢，招財分文無有，不管陀螺借給招財多少次，招財分成多少次還，他倆的賬戶總額必須是100，有借必有貸，借貸必相等，這就是一致性。

呃。。。好像也沒找到可以商量商量打個折扣的點。

永續性

這個特性最簡單，就是要把事務的所有寫操作持久化到磁碟。我們自然也不可能提出至少80%的寫操作被持久化到磁碟這樣傻兮兮的要求吧。

隔離性

我們唯獨可以在這個隔離性上做點手腳。

以新冠疫情為例。疫情緊張的時候，我們最常聽到的詞就是隔離，雖然都是隔離，卻有居家隔離、方艙隔離、酒店單間隔離之分。

再舉個例子，你和鄰居以牆相隔，這是一種很強的隔離性。但是某一天，你鑿壁偷了個光，你們兩家依然是有隔離性的，畢竟牆還在那立著呢，但是隔離性顯然沒有原來那麼強了。

說到這兒，不知道你理解了嗎？

6. MySQL支援的4種隔離級別

標準歸標準，不同的資料庫廠商或者儲存引擎對標準的實現有一定的差異。比如Oracle資料庫只提供了READ COMMITTED和SERIALIZABLE兩種隔離級別。

說回MySQL。 InnoDB支援的4個隔離級別和SQL標準定義的完全一致，隔離級別越高，事務的併發程度就越低，但是出現併發問題的概率就越小。

image-20220419144332647

上圖中還有非常重要的一點，就是InnoDB在REPEATABLE READ隔離級別下，在很大程度上就解決了幻讀的問題，讓幻讀的發生成為一種小概率事件。在這一點上InnoDB不僅完成了SQL標準，一定程度上也可以說是超越了標準。因此，REPEATABLE READ也成了InnoDB預設的隔離級別。

那什麼時候幻讀還會發生呢？我舉個例子。我用兩個終端分別開啟兩個MySQL會話，每個會話中開啟了一個事務，並且保證了每個會話的事務隔離級別都是REPEATABLE READ。

# 事務T1首先開啟事務
mysql> BEGIN;

# 事務T1搜尋id為1的記錄，結果集為空
mysql> SELECT * FROM user_innodb WHERE id = 1;
Empty set (0.01 sec)

# 事務T2插入一條id為1的記錄，並且提交事務
# INSERT INTO user_innodb VALUES(1,'wanggangdan',0);
# COMMIT;

# 事務T1在重新搜尋之前，修改一下事務T2剛插入的那條記錄
mysql> UPDATE user_innodb SET name = 'chanmufeng' WHERE id = 1;
Query OK, 1 row affected (0.03 sec)

# 事務T1再搜尋id為1的記錄，發現多了一條記錄
mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+------------+--------+
| id | name       | gender |
+----+------------+--------+
|  1 | chanmufeng |      0 |
+----+------------+--------+
1 row in set (0.00 sec)

要說清楚這個問題就牽扯到MySQL鎖的知識了，這個以後再說，知道這麼回事兒就行了。

回到我們的主線。我們現在想讓事務在自己的一畝三分地兒裡隨便折騰，其他事務的增刪改操作我不想知道（或者我想知道，就放開一下隔離級別）。怎麼辦？

或許你用過git？

我們用git進行開發任務的時候，通常情況下都會自己建立一個分支，在自己的分支上完成自己的任務，這樣和其他開發者不會造成衝突。我們可以借鑑一下這個思路。

image-20220419153627609

git的每個分支都有一個分支id，那事務也該有自己的唯一標識吧，這是自然的，下面稍微回顧一下行格式。

7. 再聊行格式

為什麼叫再聊？因為我之前好幾篇文章都提到了行格式，你要是早點讀到那幾篇文章也就不用我再說一遍了。（所以趕緊關注我公眾號啊。。。[旺柴]）。

但是總有些新朋友嘛。

7.1. 簡易版行格式

你存入MySQL的每一條記錄都會以某一種MySQL提供的行格式來進行儲存，具體有哪些行格式我不打算說明，你也沒必要記住，他們之間的最大區別只是對磁碟佔用率的優化程度不同罷了。

我們把所有行格式的公有部分拿出來，總之，一條使用者資料可以用下面的圖來表示

image-20220419161338770

注：圖中標識的欄位順序和實際MySQL的欄位儲存順序並不一致，這樣畫是為了能更清晰地說明問題。

  • roll_pointer：是我們接下來聊的重點，這裡先不管它；
  • trx_id：它就是事務id了，每條使用者記錄都有這個欄位。千萬不要忘了一個至關重要的前提，我們用的儲存引擎是InnoDB；
  • 其他：就不用多說了吧。

7.2. 分配事務id的時機

對於讀寫事務而言，只有在它第一次對某個表進行增刪改操作時，才會為這個事務分配一個事務id，否則不會分配。

更特殊地，如果一個讀寫事務中全是查詢語句，沒有增刪改的操作，這個事務也不會被分配事務id。

如果不分配事務id，事務id的值預設為0。

8. MVCC登場

8.1. 版本鏈

當一個事務T1讀到了一條記錄，我們當然希望能禁止其他事務對該條記錄進行修改和刪除的操作，直到T1結束，但是這種滿足一己之私的行為在併發領域是要遭到唾罵的。這嚴重拖系統後腿啊。

於是InnoDB的設計者提出了一種和git類似的想法，每對記錄做一次修改操作，都要記錄一條修改之前的日誌，並且該日誌還儲存了當前事務的id，和行格式類似，這條日誌也有一個roll_pointer節點。

實際InnoDB的這個功能和git沒有半毛錢關係，這裡單純為了類比。

當對同一條記錄更新的次數多了，所有的這些日誌會被roll_pointer屬性連線成一個單連結串列，這個連結串列就是版本鏈，而版本鏈的頭節點就是當前記錄的最新值。

image-20220419165538395

注：這種日誌的格式和普通記錄的格式其實並不相同，上圖中我們只關注兩者之間共同的部分。

上圖展示了一條記錄的版本鏈。該條記錄的最初始版本是由id為21的事務進行UPDATE得到的（大家可以想一下，這裡為什麼不可能是INSERT呢？）

後來，這條記錄分別被事務280和事務300各自連續UPDATE了兩次。這裡有個細節，事務280和事務300並沒有交叉著更新這條記錄，這是為什麼呢？也留給親愛的你思考吧。

InnoDB正是利用這個版本鏈來控制不同事務訪問相同記錄的行為，這種機制就是MySQL大名鼎鼎的MVCC（Multi-Version Concurrency Control），多版本併發控制。

而上文中我們一直提及的日誌，就是大名鼎鼎的undo日誌。

除了標題，在正文中我儘量沒有提及MVCC術語，可把我憋壞了。因為對於沒有了解過這個概念的讀者而言，這個術語確實有點讓人害怕。不過看到這兒的話，是不是覺得也不過如此呢？

image-20220419172512354

接下來就是看一下MySQL的各個隔離級別是怎麼利用MVCC的。

8.2. ReadView

READ UNCOMMITTED隔離級別啥也不是，髒讀、不可重讀和幻讀問題一個都解決不了，所以乾脆在這個隔離級別下直接讀取記錄的最新版本就得了。

而SERIALIZALE隔離級別又矯枉過正，必須得用鎖機制才能實現，所以就先按下不表了。

對於使用READ COMMITTED和REPEATABLE READ隔離級別的事務而言，決不允許發生髒讀現象（忘記了的話再回去看看錶格），也就是說如果事務T2已經修改了記錄但是沒有提交，那T1就不能直接讀取T2修改之後的內容。

現在的核心問題就是，怎麼判斷版本鏈中的哪個版本是當前事務可見的。

為此，InnoDB的設計者提出了ReadView的概念，其中包含了4個比較重要的內容：

  • m_ids：生成ReadView時，當前系統中活躍的讀寫事務id列表；
  • min_trx_id：生成ReadView時，當前系統中活躍的讀寫事務中最小的事務id，也就是m_ids中的最小值；
  • max_trx_id：生成ReadView時，待分配給下一個事務的id號；
  • creator_trx_id：生成當前ReadView的事務的事務id。

image-20220419222014136

有了ReadView這個資料結構，事務判斷可見性的規則就是這樣的：

  1. 從版本鏈中的最新版本開始判斷
  2. 如果被訪問版本的trx_id = creator_trx_id，說明這個版本就是當前事務修改的，允許訪問；
  3. 如果被訪問版本的trx_id < min_trx_id（未提交事務的最小id），說明生成這個版本的事務在當前ReadView生成之前就已經提交了，允許訪問；
  4. 如果被訪問版本的trx_id > max_trx_id（待分配的事務id），說明生成這個版本的事務是在當前ReadView生成之後建立的，不允許訪問；
  5. 如果被訪問版本的trx_id在min_trx_id和max_trx_id之間，那就需要判斷trx_id是否在m_ids之中，如果在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問。否則，可以訪問；
  6. 如果當前版本不可見，就沿著版本鏈找到下一個版本，重複上面的1～4步。

READ COMMITTED和REPEATABLE READ隔離級別之間的不同之處就是生成ReadView的時機不同。接下來具體看一下它們之間的區別。

8.2.1. READ COMMITTED

READ COMMITTED是每次讀取資料之前都生成一個ReadView。

我們來做個實驗，實驗之前先看一下我們的目標記錄現在的值：

mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | wanggangdan |      1 |
+----+-------------+--------+

假設系統中有兩個事務id分別為100，200的事務T1、T2在執行：

# 事務T1(100)開始執行
mysql> BEGIN;

mysql> UPDATE user_innodb SET name = 'chanmufeng' WHERE id = 1;
mysql> UPDATE user_innodb SET name = 'zhaosi' WHERE id = 1;

# 注意，事務T1(100)並沒有提交

# 事務T2(200)開始執行
mysql> BEGIN;

# 做了其他表的一些增刪改操作

# 注意，事務T2(200)並沒有提交

此時，表user_innodb中id為1的記錄的版本鏈的形式如下圖所示：

image-20220420154617575

接下來我們在新的會話中將隔離級別設定為READ COMMITTED，並開始事務T3

# 在新的會話中設定SESSION級別的隔離級別，這種設定方式對當前會話的後續所有事務生效
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# 檢視當前會話預設的隔離級別，發現是READ-COMMITTED，說明設定成功
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

# T3開啟事務
mysql> BEGIN;

# T3查詢id為1的記錄資訊，發現是最原始的、事務T1修改之前的版本
mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | wanggangdan |      1 |
+----+-------------+--------+

我們對照著上文說過的可見性判斷規則，來捋一遍整個流程：

  1. T3執行SELECT時會首先生成一個ReadView資料結構，這個ReadView的資訊如下
    • m_ids列表的內容是[100,200]
    • min_trx_id為100
    • max_trx_id為201（這裡我們假設待分配給下一個事務就是201）
    • creator_trx_id為0（因為事務T3只是SELECT而已，沒有做增刪改操作，所以事務id為0）
  2. 從版本鏈中的最新版本開始判斷；
  3. 最新版本的trx_id是100，在min_trx_id和max_trx_id之間，繼續判斷trx_id是否在m_ids之中，發現在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問，根據連結串列找到下一個版本；
  4. 當前版本的trx_id是100，不允許訪問，理由同上，繼續跳到下一個版本；
  5. 當前版本的trx_id是99，小於min_trx_id值100，所以當前版本對T3可見，返回的資料就是name為'wanggangdan'的這條記錄。

接著，實驗繼續，我們把T1提交一下：

# 事務T1提交
mysql> COMMIT;

然後在事務T2中（目前還沒有提交）再次更新id為1的記錄

# 事務T2繼續執行id為1的記錄的更新操作，但是依然不提交
mysql> UPDATE user_innodb SET name = 'wangwu' WHERE id = 1;
mysql> UPDATE user_innodb SET name = 'wanger' WHERE id = 1;

現在，版本鏈就變成了這個樣子：

image-20220420170613608

最後在事務T3中重新執行查詢，再來看一下結果集會是什麼：

# 事務T3再次執行查詢
mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | zhaosi |      1 |
+----+--------+--------+

我們來捋一下這次的查詢的執行過程：

  1. 因為T3的隔離級別是READ COMMITTED，所以再次執行查詢會重新生成一個ReadView，此時這個ReadView裡邊的資訊如下：

    • m_ids列表的內容是[200]，因為T1已經提交了
    • min_trx_id為200
    • max_trx_id為201（這裡我們假設待分配給下一個事務就是201）
    • creator_trx_id為0（因為事務T3只是SELECT而已，沒有做增刪改操作，所以事務id為0）

  2. 從版本鏈中的最新版本開始判斷；

  3. 最新版本的trx_id是200，在min_trx_id和max_trx_id之間，繼續判斷trx_id是否在m_ids之中，發現在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問，根據連結串列找到下一個版本；

  4. 當前版本的trx_id是200，不允許訪問，理由同上，繼續跳到下一個版本；

  5. 當前版本的trx_id是100，小於min_trx_id值200，所以當前版本對T3可見，返回的資料就是name為'zhaosi'的這條記錄。

重點就是：READ COMMITTED在每次SELECT的時候都重新生成一個ReadView。

注意，在做實驗的時候如果長時間未操作終端，可能導致和MySQL伺服器的連線自動斷開，連線一旦斷開，事務會自動進行提交。做實驗的小夥伴需要注意一下。

8.2.2. REPEATABLE READ

學會了READ COMMITTED，REPEATABLE READ也是同樣的道理了，唯一的區別是：

REPEATABLE READ只會在第一次執行SELECT的時候生成一個ReadView，之後不管SELECT多少次，都是用最開始生成的ReadView中的變數進行判斷。

還是拿上面的事務id為100和200的事務為例，在實驗之前，先將資料重置到最初的狀態。

mysql> UPDATE user_innodb SET name = 'wanggangdan' WHERE id = 1;

事務T1先執行：

# 事務T1(100)開始執行
mysql> BEGIN;

mysql> UPDATE user_innodb SET name = 'chanmufeng' WHERE id = 1;
mysql> UPDATE user_innodb SET name = 'zhaosi' WHERE id = 1;

# 注意，事務T1(100)並沒有提交

# 事務T2(200)開始執行
mysql> BEGIN;

# 做了其他表的一些增刪改操作

# 注意，事務T2(200)並沒有提交

此時，表user_innodb中id為1的記錄的版本鏈的形式如下圖所示：

image-20220420154617575

接下來我們在新的會話中將隔離級別設定為REPEATABLE READ，並開始事務T3

# 在新的會話中設定SESSION級別的隔離級別，這種設定方式對當前會話的後續所有事務生效
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 檢視當前會話預設的隔離級別，發現是READ-COMMITTED，說明設定成功
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

# T3開啟事務
mysql> BEGIN;

# T3查詢id為1的記錄資訊，發現是最原始的、事務T1修改之前的版本
mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | wanggangdan |      1 |
+----+-------------+--------+

現在捋這個流程你應該已經熟悉很多了：

  1. T3初次執行SELECT時會生成一個ReadView資料結構，這個ReadView的資訊如下
    • m_ids列表的內容是[100,200]
    • min_trx_id為100
    • max_trx_id為201（這裡我們假設待分配給下一個事務就是201）
    • creator_trx_id為0（因為事務T3只是SELECT而已，沒有做增刪改操作，所以事務id為0）
  2. 從版本鏈中的最新版本開始判斷；
  3. 最新版本的trx_id是100，在min_trx_id和max_trx_id之間，繼續判斷trx_id是否在m_ids之中，發現在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問，根據連結串列找到下一個版本；
  4. 當前版本的trx_id是100，不允許訪問，理由同上，繼續跳到下一個版本；
  5. 當前版本的trx_id是99，小於min_trx_id值100，所以當前版本對T3可見，返回的資料就是name為'wanggangdan'的這條記錄。

接著，實驗繼續，我們把T1提交一下：

# 事務T1提交
mysql> COMMIT;

然後在事務T2中（目前還沒有提交）再次更新id為1的記錄

# 事務T2繼續執行id為1的記錄的更新操作，但是依然不提交
mysql> UPDATE user_innodb SET name = 'wangwu' WHERE id = 1;
mysql> UPDATE user_innodb SET name = 'wanger' WHERE id = 1;

現在，版本鏈就變成了這個樣子：

image-20220420170613608

最後在事務T3中重新執行查詢，再來看一下結果集會是什麼：

# 事務T3再次執行查詢
mysql> SELECT * FROM user_innodb WHERE id = 1;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | wanggangdan |      1 |
+----+-------------+--------+

我們來捋一下這次的查詢的執行過程：

  1. 因為T3的隔離級別是REPEATABLE READ，所以還是沿用一開始生成的那個ReadView，再抄一遍：

    • m_ids列表的內容是[100,200]
    • min_trx_id為100
    • max_trx_id為201
    • creator_trx_id為0

  2. 從版本鏈中的最新版本開始判斷；

  3. 最新版本的trx_id是200，在min_trx_id和max_trx_id之間，繼續判斷trx_id是否在m_ids之中，發現在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問，根據連結串列找到下一個版本；

  4. 當前版本的trx_id是200，不允許訪問，理由同上，繼續跳到下一個版本；

  5. 當前版本的trx_id是100，在min_trx_id和max_trx_id之間，繼續判斷trx_id是否在m_ids之中，發現在，說明生成當前ReadView時，生成該版本的事務還是活躍的，因此不允許訪問，根據連結串列找到下一個版本；

  6. 當前版本的trx_id是100，不允許訪問，理由同上，繼續跳到下一個版本；

  7. 當前版本的trx_id是99，小於min_trx_id值100，所以當前版本對T3可見，返回的資料就是name為'wanggangdan'的這條記錄。

也就是說，READ COMMITTED隔離級別下，T3前後兩次SELECT得到的結果完全一致，跟其他事務提交不提交沒有關係，即使事務T2後來也提交了，也不影響T3的搜尋結果。怎麼樣，是不是一致性的程度比READ COMMITTED更強了呢？

好了，到目前為止你已經知道怎麼利用MVCC來解決一致性讀問題了，但是寫一致性該怎麼辦呢？那就是下一篇事務的隔離級別與鎖的內容了。敬請關注嘍。

相關文章