MySQL事務隔離級別和MVCC

小孩子4919發表於2019-03-27

標籤：「我們都是小青蛙」公眾號文章

事前準備

為了故事的順利發展，我們需要建立一個表：

CREATE TABLE t (
    id INT PRIMARY KEY,
    c VARCHAR(100)
) Engine=InnoDB CHARSET=utf8;
複製程式碼

然後向這個表裡插入一條資料：

INSERT INTO t VALUES(1, '劉備');
複製程式碼

現在表裡的資料就是這樣的：

mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 劉備   |
+----+--------+
1 row in set (0.01 sec)
複製程式碼

隔離級別

MySQL是一個伺服器／客戶端架構的軟體，對於同一個伺服器來說，可以有若干個客戶端與之連線，每個客戶端與伺服器連線上之後，就可以稱之為一個會話（Session）。我們可以同時在不同的會話裡輸入各種語句，這些語句可以作為事務的一部分進行處理。不同的會話可以同時傳送請求，也就是說伺服器可能同時在處理多個事務，這樣子就會導致不同的事務可能同時訪問到相同的記錄。我們前邊說過事務有一個特性稱之為隔離性，理論上在某個事務對某個資料進行訪問時，其他事務應該進行排隊，當該事務提交之後，其他事務才可以繼續訪問這個資料。但是這樣子的話對效能影響太大，所以設計資料庫的大叔提出了各種隔離級別，來最大限度的提升系統併發處理事務的能力，但是這也是以犧牲一定的隔離性來達到的。

未提交讀（READ UNCOMMITTED）

如果一個事務讀到了另一個未提交事務修改過的資料，那麼這種隔離級別就稱之為未提交讀（英文名：READ UNCOMMITTED），示意圖如下：

image_1d6t5hhamcd61qkjk9v1ag8171o7u.png-95.6kB

如上圖，Session A和Session B各開啟了一個事務，Session B中的事務先將id為1的記錄的列c更新為'關羽'，然後Session A中的事務再去查詢這條id為1的記錄，那麼在未提交讀的隔離級別下，查詢結果就是'關羽'，也就是說某個事務讀到了另一個未提交事務修改過的記錄。但是如果Session B中的事務稍後進行了回滾，那麼Session A中的事務相當於讀到了一個不存在的資料，這種現象就稱之為髒讀，就像這個樣子：

image_1d6uqql7n55t1k7mmellrh14a495.png-105.3kB

髒讀違背了現實世界的業務含義，所以這種READ UNCOMMITTED算是十分不安全的一種隔離級別。

已提交讀（READ COMMITTED）

如果一個事務只能讀到另一個已經提交的事務修改過的資料，並且其他事務每對該資料進行一次修改並提交後，該事務都能查詢得到最新值，那麼這種隔離級別就稱之為已提交讀（英文名：READ COMMITTED），如圖所示：

image_1d6t64lgg1j4mtp818f61n09t6l8o.png-133.1kB

從圖中可以看到，第4步時，由於Session B中的事務尚未提交，所以Session A中的事務查詢得到的結果只是'劉備'，而第6步時，由於Session B中的事務已經提交，所以Session B中的事務查詢得到的結果就是'關羽'了。

對於某個處在在已提交讀隔離級別下的事務來說，只要其他事務修改了某個資料的值，並且之後提交了，那麼該事務就會讀到該資料的最新值，比方說：

image_1d6urs4l0g799959e1jsj1cvqai.png-170.6kB

我們在Session B中提交了幾個隱式事務，這些事務都修改了id為1的記錄的列c的值，每次事務提交之後，Session A中的事務都可以檢視到最新的值。這種現象也被稱之為不可重複讀。

可重複讀（REPEATABLE READ）

在一些業務場景中，一個事務只能讀到另一個已經提交的事務修改過的資料，但是第一次讀過某條記錄後，即使其他事務修改了該記錄的值並且提交，該事務之後再讀該條記錄時，讀到的仍是第一次讀到的值，而不是每次都讀到不同的資料。那麼這種隔離級別就稱之為可重複讀（英文名：REPEATABLE READ），如圖所示：

image_1d6useq9aagi9981sm21b011dt4bf.png-171.1kB

從圖中可以看出來，Session A中的事務在第一次讀取id為1的記錄時，列c的值為'劉備'，之後雖然Session B中隱式提交了多個事務，每個事務都修改了這條記錄，但是Session A中的事務讀到的列c的值仍為'劉備'，與第一次讀取的值是相同的。

序列化（SERIALIZABLE）

以上3種隔離級別都允許對同一條記錄進行讀-讀、讀-寫、寫-讀的併發操作，如果我們不允許讀-寫、寫-讀的併發操作，可以使用SERIALIZABLE隔離級別，示意圖如下：

image_1d6uu0sk41213olj102t1tsa10o9ds.png-122.9kB

如圖所示，當Session B中的事務更新了id為1的記錄後，之後Session A中的事務再去訪問這條記錄時就被卡住了，直到Session B中的事務提交之後，Session A中的事務才可以獲取到查詢結果。

版本鏈

對於使用InnoDB儲存引擎的表來說，它的聚簇索引記錄中都包含兩個必要的隱藏列（row_id並不是必要的，我們建立的表中有主鍵或者非NULL唯一鍵時都不會包含row_id列）：

trx_id：每次對某條聚簇索引記錄進行改動時，都會把對應的事務id賦值給trx_id隱藏列。
roll_pointer：每次對某條聚簇索引記錄進行改動時，都會把舊的版本寫入到undo日誌中，然後這個隱藏列就相當於一個指標，可以通過它來找到該記錄修改前的資訊。

比方說我們的表t現在只包含一條記錄：

mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 劉備   |
+----+--------+
1 row in set (0.01 sec)
複製程式碼

假設插入該記錄的事務id為80，那麼此刻該條記錄的示意圖如下所示：

image_1d6vemvvn1db6h431ekvsp158m19.png-15kB

假設之後兩個id分別為100、200的事務對這條記錄進行UPDATE操作，操作流程如下：

image_1d6vfo4g814h019mj1jqb1ggu72o3j.png-106.5kB

小貼士：能不能在兩個事務中交叉更新同一條記錄呢？哈哈，這是不可以滴，第一個事務更新了某條記錄後，就會給這條記錄加鎖，另一個事務再次更新時就需要等待第一個事務提交了，把鎖釋放之後才可以繼續更新。本篇文章不是討論鎖的，有關鎖的更多細節我們之後再說。

每次對記錄進行改動，都會記錄一條undo日誌，每條undo日誌也都有一個roll_pointer屬性（INSERT操作對應的undo日誌沒有該屬性，因為該記錄並沒有更早的版本），可以將這些undo日誌都連起來，串成一個連結串列，所以現在的情況就像下圖一樣：

image_1d6vfrv111j4guetptcts1qgp40.png-57.1kB

對該記錄每次更新後，都會將舊值放到一條undo日誌中，就算是該記錄的一箇舊版本，隨著更新次數的增多，所有的版本都會被roll_pointer屬性連線成一個連結串列，我們把這個連結串列稱之為版本鏈，版本鏈的頭節點就是當前記錄最新的值。另外，每個版本中還包含生成該版本時對應的事務id，這個資訊很重要，我們稍後就會用到。

ReadView

對於使用READ UNCOMMITTED隔離級別的事務來說，直接讀取記錄的最新版本就好了，對於使用SERIALIZABLE隔離級別的事務來說，使用加鎖的方式來訪問記錄。對於使用READ COMMITTED和REPEATABLE READ隔離級別的事務來說，就需要用到我們上邊所說的版本鏈了，核心問題就是：需要判斷一下版本鏈中的哪個版本是當前事務可見的。所以設計InnoDB的大叔提出了一個ReadView的概念，這個ReadView中主要包含當前系統中還有哪些活躍的讀寫事務，把它們的事務id放到一個列表中，我們把這個列表命名為為m_ids。這樣在訪問某條記錄時，只需要按照下邊的步驟判斷記錄的某個版本是否可見：

如果被訪問版本的trx_id屬性值小於m_ids列表中最小的事務id，表明生成該版本的事務在生成ReadView前已經提交，所以該版本可以被當前事務訪問。
如果被訪問版本的trx_id屬性值大於m_ids列表中最大的事務id，表明生成該版本的事務在生成ReadView後才生成，所以該版本不可以被當前事務訪問。
如果被訪問版本的trx_id屬性值在m_ids列表中最大的事務id和最小事務id之間，那就需要判斷一下trx_id屬性值是不是在m_ids列表中，如果在，說明建立ReadView時生成該版本的事務還是活躍的，該版本不可以被訪問；如果不在，說明建立ReadView時生成該版本的事務已經被提交，該版本可以被訪問。

如果某個版本的資料對當前事務不可見的話，那就順著版本鏈找到下一個版本的資料，繼續按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最後一個版本，如果最後一個版本也不可見的話，那麼就意味著該條記錄對該事務不可見，查詢結果就不包含該記錄。

在MySQL中，READ COMMITTED和REPEATABLE READ隔離級別的的一個非常大的區別就是它們生成ReadView的時機不同，我們來看一下。

READ COMMITTED --- 每次讀取資料前都生成一個ReadView

比方說現在系統裡有兩個id分別為100、200的事務在執行：

# Transaction 100
BEGIN;

UPDATE t SET c = '關羽' WHERE id = 1;

UPDATE t SET c = '張飛' WHERE id = 1;
複製程式碼

# Transaction 200
BEGIN;

# 更新了一些別的表的記錄
...
複製程式碼

小貼士：事務執行過程中，只有在第一次真正修改記錄時（比如使用INSERT、DELETE、UPDATE語句），才會被分配一個單獨的事務id，這個事務id是遞增的。

此刻，表t中id為1的記錄得到的版本連結串列如下所示：

image_1d6vgdl0j1c9d16rbelo1deh17324d.png-42.2kB

假設現在有一個使用READ COMMITTED隔離級別的事務開始執行：

# 使用READ COMMITTED隔離級別的事務
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值為'劉備'
複製程式碼

這個SELECT1的執行過程如下：

在執行SELECT語句時會先生成一個ReadView，ReadView的m_ids列表的內容就是[100, 200]。
然後從版本鏈中挑選可見的記錄，從圖中可以看出，最新版本的列c的內容是'張飛'，該版本的trx_id值為100，在m_ids列表內，所以不符合可見性要求，根據roll_pointer跳到下一個版本。
下一個版本的列c的內容是'關羽'，該版本的trx_id值也為100，也在m_ids列表內，所以也不符合要求，繼續跳到下一個版本。
下一個版本的列c的內容是'劉備'，該版本的trx_id值為80，小於m_ids列表中最小的事務id100，所以這個版本是符合要求的，最後返回給使用者的版本就是這條列c為'劉備'的記錄。

之後，我們把事務id為100的事務提交一下，就像這樣：

# Transaction 100
BEGIN;

UPDATE t SET c = '關羽' WHERE id = 1;

UPDATE t SET c = '張飛' WHERE id = 1;

COMMIT;
複製程式碼

然後再到事務id為200的事務中更新一下表t中id為1的記錄：

# Transaction 200
BEGIN;

# 更新了一些別的表的記錄
...

UPDATE t SET c = '趙雲' WHERE id = 1;

UPDATE t SET c = '諸葛亮' WHERE id = 1;
複製程式碼

此刻，表t中id為1的記錄的版本鏈就長這樣：

image_1d6vgrt5jeh2itl5e41ocl944q.png-57.6kB

然後再到剛才使用READ COMMITTED隔離級別的事務中繼續查詢這個id為1的記錄，如下：

# 使用READ COMMITTED隔離級別的事務
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值為'劉備'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值為'張飛'
複製程式碼

這個SELECT2的執行過程如下：

在執行SELECT語句時會先生成一個ReadView，ReadView的m_ids列表的內容就是[200]（事務id為100的那個事務已經提交了，所以生成快照時就沒有它了）。
然後從版本鏈中挑選可見的記錄，從圖中可以看出，最新版本的列c的內容是'諸葛亮'，該版本的trx_id值為200，在m_ids列表內，所以不符合可見性要求，根據roll_pointer跳到下一個版本。
下一個版本的列c的內容是'趙雲'，該版本的trx_id值為200，也在m_ids列表內，所以也不符合要求，繼續跳到下一個版本。
下一個版本的列c的內容是'張飛'，該版本的trx_id值為100，比m_ids列表中最小的事務id200還要小，所以這個版本是符合要求的，最後返回給使用者的版本就是這條列c為'張飛'的記錄。

以此類推，如果之後事務id為200的記錄也提交了，再此在使用READ COMMITTED隔離級別的事務中查詢表t中id值為1的記錄時，得到的結果就是'諸葛亮'了，具體流程我們就不分析了。總結一下就是：使用READ COMMITTED隔離級別的事務在每次查詢開始時都會生成一個獨立的ReadView。

`REPEATABLE READ` ---在第一次讀取資料時生成一個ReadView

對於使用REPEATABLE READ隔離級別的事務來說，只會在第一次執行查詢語句時生成一個ReadView，之後的查詢就不會重複生成了。我們還是用例子看一下是什麼效果。

比方說現在系統裡有兩個id分別為100、200的事務在執行：

# Transaction 100
BEGIN;

UPDATE t SET c = '關羽' WHERE id = 1;

UPDATE t SET c = '張飛' WHERE id = 1;
複製程式碼

# Transaction 200
BEGIN;

# 更新了一些別的表的記錄
...
複製程式碼

此刻，表t中id為1的記錄得到的版本連結串列如下所示：

假設現在有一個使用REPEATABLE READ隔離級別的事務開始執行：

# 使用REPEATABLE READ隔離級別的事務
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值為'劉備'
複製程式碼

這個SELECT1的執行過程如下：

在執行SELECT語句時會先生成一個ReadView，ReadView的m_ids列表的內容就是[100, 200]。
然後從版本鏈中挑選可見的記錄，從圖中可以看出，最新版本的列c的內容是'張飛'，該版本的trx_id值為100，在m_ids列表內，所以不符合可見性要求，根據roll_pointer跳到下一個版本。
下一個版本的列c的內容是'關羽'，該版本的trx_id值也為100，也在m_ids列表內，所以也不符合要求，繼續跳到下一個版本。
下一個版本的列c的內容是'劉備'，該版本的trx_id值為80，小於m_ids列表中最小的事務id100，所以這個版本是符合要求的，最後返回給使用者的版本就是這條列c為'劉備'的記錄。

之後，我們把事務id為100的事務提交一下，就像這樣：

# Transaction 100
BEGIN;

UPDATE t SET c = '關羽' WHERE id = 1;

UPDATE t SET c = '張飛' WHERE id = 1;

COMMIT;
複製程式碼

然後再到事務id為200的事務中更新一下表t中id為1的記錄：

# Transaction 200
BEGIN;

# 更新了一些別的表的記錄
...

UPDATE t SET c = '趙雲' WHERE id = 1;

UPDATE t SET c = '諸葛亮' WHERE id = 1;
複製程式碼

此刻，表t中id為1的記錄的版本鏈就長這樣：

然後再到剛才使用REPEATABLE READ隔離級別的事務中繼續查詢這個id為1的記錄，如下：

# 使用REPEATABLE READ隔離級別的事務
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值為'劉備'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值仍為'劉備'
複製程式碼

這個SELECT2的執行過程如下：

因為之前已經生成過ReadView了，所以此時直接複用之前的ReadView，之前的ReadView中的m_ids列表就是[100, 200]。
然後從版本鏈中挑選可見的記錄，從圖中可以看出，最新版本的列c的內容是'諸葛亮'，該版本的trx_id值為200，在m_ids列表內，所以不符合可見性要求，根據roll_pointer跳到下一個版本。
下一個版本的列c的內容是'趙雲'，該版本的trx_id值為200，也在m_ids列表內，所以也不符合要求，繼續跳到下一個版本。
下一個版本的列c的內容是'張飛'，該版本的trx_id值為100，而m_ids列表中是包含值為100的事務id的，所以該版本也不符合要求，同理下一個列c的內容是'關羽'的版本也不符合要求。繼續跳到下一個版本。
下一個版本的列c的內容是'劉備'，該版本的trx_id值為80，80小於m_ids列表中最小的事務id100，所以這個版本是符合要求的，最後返回給使用者的版本就是這條列c為'劉備'的記錄。

也就是說兩次SELECT查詢得到的結果是重複的，記錄的列c值都是'劉備'，這就是可重複讀的含義。如果我們之後再把事務id為200的記錄提交了，之後再到剛才使用REPEATABLE READ隔離級別的事務中繼續查詢這個id為1的記錄，得到的結果還是'劉備'，具體執行過程大家可以自己分析一下。

MVCC總結

從上邊的描述中我們可以看出來，所謂的MVCC（Multi-Version Concurrency Control ，多版本併發控制）指的就是在使用READ COMMITTD、REPEATABLE READ這兩種隔離級別的事務在執行普通的SEELCT操作時訪問記錄的版本鏈的過程，這樣子可以使不同事務的讀-寫、寫-讀操作併發執行，從而提升系統效能。READ COMMITTD、REPEATABLE READ這兩個隔離級別的一個很大不同就是生成ReadView的時機不同，READ COMMITTD在每一次進行普通SELECT操作前都會生成一個ReadView，而REPEATABLE READ只在第一次進行普通SELECT操作前生成一個ReadView，之後的查詢操作都重複這個ReadView就好了。

小冊

想看更多MySQL進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～

MySQL之事務隔離級別和MVCC
2022-06-01
MySqlMVC
mysql事務隔離級別和鎖
2021-06-30
MySql
MySQL 事務隔離級別
2020-07-03
MySql
MySQL事務隔離級別
2024-04-02
MySql
[Mysql]事務/隔離級別
2024-07-05
MySql
mysql(InnoDB)事務隔離級別(REPEATABLE READ) 與鎖,MVCC
2018-01-01
MySqlMVC
PostgreSQL DBA(23) - MVCC#3(事務快照和隔離級別)
2019-01-09
SQLMVCC#
MySQL事務的隔離級別
2019-03-27
MySql
MySQL的事務隔離級別
2020-11-29
MySql
理解MySQL事務隔離級別
2022-11-14
MySql
mysql修改事務隔離級別
2016-06-29
MySql
MySQL 事務隔離級別解析和實戰
2019-03-13
MySql
mysql如何修改事務隔離級別
2021-09-11
MySql
啥是 MySQL 事務隔離級別？
2021-07-26
MySql
Mysql 四種事務隔離級別
2021-01-30
MySql
Mysql鎖與事務隔離級別
2020-12-07
MySql
理解mysql的事務隔離級別
2022-03-01
MySql
MySQL事務隔離級別詳解
2017-10-23
MySql
一文讀懂MySQL的事務隔離級別及MVCC機制
2020-10-19
MySqlMVC
【MySQL】MySQL的四種事務隔離級別
2017-07-09
MySql
MySQL 的四種事務隔離級別
2019-07-09
MySql
MySQL 事務的隔離級別初窺
2021-01-05
MySql
MySQL的四種事務隔離級別
2018-01-04
MySql
MySQL 事務隔離級別實現原理
2024-10-15
MySql
SqlServer事務詳解（事務隔離性和隔離級別詳解）
2021-05-31
SQLServer
PostgreSQL事務隔離級別
2021-07-03
SQL
事務、特性、隔離級別
2020-10-30
徹底搞懂 MySQL 事務的隔離級別
2020-02-05
MySql
MySQL的事務隔離級別是什麼？
2020-04-20
MySql
四個案例看懂 MySQL 事務隔離級別
2021-09-09
MySql
Mysql事務隔離級別與鎖機制
2020-09-24
MySql
MySQL的事務處理及隔離級別
2018-06-20
MySql
MySQL事務隔離級別的實現原理
2018-01-27
MySql
【mysql】官方的SQL事務隔離級別文件
2012-03-23
MySql
Oracle-事務隔離級別
2014-03-11
Oracle
JDBC 事務（一）隔離級別
2013-12-06
JDBC
MySQL資料庫引擎、事務隔離級別、鎖
2018-05-18
MySql資料庫
mysql 5.5 innodb事務隔離級別之開篇
2017-12-22
MySql