MySQL事務(wù)的隔離性是如何實現(xiàn)的？

并發(fā)場景

最近做了一些分布式事務(wù)的項目，對事務(wù)的隔離性有了更深的認(rèn)識，后續(xù)寫文章聊分布式事務(wù)。今天就復(fù)盤一下單機(jī)事務(wù)的隔離性是如何實現(xiàn)的？

「隔離的本質(zhì)就是控制并發(fā)」，如果SQL語句就是串行執(zhí)行的。那么數(shù)據(jù)庫的四大特性中就不會有隔離性這個概念了，也就不會有臟讀，不可重復(fù)讀，幻讀等各種問題了

「對數(shù)據(jù)庫的各種并發(fā)操作，只有如下四種，寫寫，讀讀，讀寫和寫讀」

寫-寫

事務(wù)A更新一條記錄的時候，事務(wù)B能同時更新同一條記錄嗎？

答案肯定是不能的，不然就會造成「臟寫」問題，那如何避免臟寫呢？答案就是「加鎖」

讀-讀

MySQL讀操作默認(rèn)情況下不會加鎖，所以可以并行的讀

讀-寫 和 寫-讀

「基于各種場景對并發(fā)操作容忍程度不同，MySQL就搞了個隔離性的概念」。你自己根據(jù)業(yè)務(wù)場景選擇隔離級別。

√ 為會發(fā)生，×為不會發(fā)生

「所以你看，MySQL是通過鎖和隔離級別對MySQL進(jìn)行并發(fā)控制的」

MySQL中的鎖

行級鎖

InnoDB存儲引擎中有如下兩種類型的行級鎖

1. 「共享鎖」（Shared Lock，簡稱S鎖），在事務(wù)需要讀取一條記錄時，需要先獲取改記錄的S鎖
2. 「排他鎖」（Exclusive Lock，簡稱X鎖），在事務(wù)要改動一條記錄時，需要先獲取該記錄的X鎖

如果事務(wù)T1獲取了一條記錄的S鎖之后，事務(wù)T2也要訪問這條記錄。如果事務(wù)T2想再獲取這個記錄的S鎖，可以成功，這種情況稱為鎖兼容，如果事務(wù)T2想再獲取這個記錄的X鎖，那么此操作會被阻塞，直到事務(wù)T1提交之后將S鎖釋放掉

如果事務(wù)T1獲取了一條記錄的X鎖之后，那么不管事務(wù)T2接著想獲取該記錄的S鎖還是X鎖都會被阻塞，直到事務(wù)1提交，這種情況稱為鎖不兼容。

「多個事務(wù)可以同時讀取記錄，即共享鎖之間不互斥，但共享鎖會阻塞排他鎖。排他鎖之間互斥」

S鎖和X鎖之間的兼容關(guān)系如下

「update，delete，insert 都會自動給涉及到的數(shù)據(jù)加上排他鎖，select 語句默認(rèn)不會加任何鎖」

那什么情況下會對讀操作加鎖呢？

1. select .. lock in share mode，對讀取的記錄加S鎖
2. select ... for update ，對讀取的記錄加X鎖
3. 在事務(wù)中讀取記錄，對讀取的記錄加S鎖
4. 事務(wù)隔離級別在 SERIALIZABLE 下，對讀取的記錄加S鎖

「InnoDB中有如下三種鎖」

1. Record Lock：對單個記錄加鎖
2. Gap Lock：間隙鎖，鎖住記錄前面的間隙，不允許插入記錄
3. Next-key Lock：同時鎖住數(shù)據(jù)和數(shù)據(jù)前面的間隙，即數(shù)據(jù)和數(shù)據(jù)前面的間隙都不允許插入記錄

寫個Demo演示一下

CREATE?TABLE?`girl`?(
??`id`?int(11)?NOT?NULL,
??`name`?varchar(255),
??`age`?int(11),
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

insert?into?girl?values
(1,?'西施',?20),
(5,?'王昭君',?23),
(8,?'貂蟬',?25),
(10,?'楊玉環(huán)',?26),
(12,?'陳圓圓',?20);

Record Lock

「對單個記錄加鎖」

如把id值為8的數(shù)據(jù)加一個Record Lock，示意圖如下Record Lock也是有S鎖和X鎖之分的，兼容性和之前描述的一樣。

SQL執(zhí)行加什么樣的鎖受很多條件的制約，比如事務(wù)的隔離級別，執(zhí)行時使用的索引（如，聚集索引，非聚集索引等），因此就不詳細(xì)分析了，舉幾個簡單的例子。

--?READ?UNCOMMITTED/READ?COMMITTED/REPEATABLE?READ?利用主鍵進(jìn)行等值查詢
--?對id=8的記錄加S型Record?Lock
select?*?from?girl?where?id?=?8?lock?in?share?mode;

--?READ?UNCOMMITTED/READ?COMMITTED/REPEATABLE?READ?利用主鍵進(jìn)行等值查詢
--?對id=8的記錄加X型Record?Lock
select?*?from?girl?where?id?=?8?for?update;

Gap Lock

「鎖住記錄前面的間隙，不允許插入記錄」

「MySQL在可重復(fù)讀隔離級別下可以通過MVCC和加鎖來解決幻讀問題」

當(dāng)前讀：加鎖

快照讀：MVCC

但是該如何加鎖呢？因為第一次執(zhí)行讀取操作的時候，這些幻影記錄并不存在，我們沒有辦法加Record Lock，此時可以通過加Gap Lock解決，即對間隙加鎖。如一個事務(wù)對id=8的記錄加間隙鎖，則意味著不允許別的事務(wù)在id=8的記錄前面的間隙插入新記錄，即id值在(5, 8)這個區(qū)間內(nèi)的記錄是不允許立即插入的。直到加間隙鎖的事務(wù)提交后，id值在(5, 8)這個區(qū)間中的記錄才可以被提交

我們來看如下一個SQL的加鎖過程

--?REPEATABLE?READ?利用主鍵進(jìn)行等值查詢
--?但是主鍵值并不存在
--?對id=8的聚集索引記錄加Gap?Lock
SELECT?*?FROM?girl?WHERE?id?=?7?LOCK?IN?SHARE?MODE;

由于id=7的記錄不存在，為了禁止幻讀現(xiàn)象（避免在同一事務(wù)下執(zhí)行相同的語句得到的結(jié)果集中有id=7的記錄），所以在當(dāng)前事務(wù)提交前我們要預(yù)防別的事務(wù)插入id=7的記錄，此時在id=8的記錄上加一個Gap Lock即可，即不允許別的事務(wù)插入id值在(5, 8)這個區(qū)間的新記錄

「給大家提一個問題，Gap Lock只能鎖定記錄前面的間隙，那么最后一條記錄后面的間隙該怎么鎖定？」

其實mysql數(shù)據(jù)是存在頁中的，每個頁有2個偽記錄

1. Infimum記錄，表示該頁面中最小的記錄
2. upremum記錄，表示該頁面中最大的記錄

為了防止其它事務(wù)插入id值在(12, +∞)這個區(qū)間的記錄，我們可以給id=12記錄所在頁面的Supremum記錄加上一個gap鎖，此時就可以阻止其他事務(wù)插入id值在(12, +∞)這個區(qū)間的新記錄

Next-key Lock

「同時鎖住數(shù)據(jù)和數(shù)據(jù)前面的間隙，即數(shù)據(jù)和數(shù)據(jù)前面的間隙都不允許插入記錄」所以你可以這樣理解Next-key Lock=Record Lock+Gap Lock

--?REPEATABLE?READ?利用主鍵進(jìn)行范圍查詢
--?對id=8的聚集索引記錄加S型Record?Lock
--?對id>8的所有聚集索引記錄加S型Next-key?Lock（包括Supremum偽記錄）
SELECT?*?FROM?girl?WHERE?id?>=?8?LOCK?IN?SHARE?MODE;

因為要解決幻讀的問題，所以需要禁別的事務(wù)插入id>=8的記錄，所以

• 對id=8的聚集索引記錄加S型Record Lock
• 對id>8的所有聚集索引記錄加S型Next-key Lock（包括Supremum偽記錄）

表級鎖

「表鎖也有S鎖和X鎖之分」

在對某個表執(zhí)行select，insert，update，delete語句時，innodb存儲引擎是不會為這個表添加表級別的S鎖或者X鎖。

在對表執(zhí)行一些諸如ALTER TABLE，DROP TABLE這類的DDL語句時，會對這個表加X鎖，因此其他事務(wù)對這個表執(zhí)行諸如SELECT INSERT UPDATE DELETE的語句會發(fā)生阻塞

在系統(tǒng)變量autocommit=0，innodb_table_locks = 1時，手動獲取InnoDB存儲引擎提供的表t的S鎖或者X鎖，可以這么寫

對表t加表級別的S鎖

lock?tables?t?read

對表t加表級別的X鎖

lock?tables?t?write

「如果一個事務(wù)給表加了S鎖，那么」

• 別的事務(wù)可以繼續(xù)獲得該表的S鎖
• 別的事務(wù)可以繼續(xù)獲得表中某些記錄的S鎖
• 別的事務(wù)不可以繼續(xù)獲得該表的X鎖
• 別的事務(wù)不可以繼續(xù)獲得表中某些記錄的X鎖

「如果一個事務(wù)給表加了X鎖，那么」

• 別的事務(wù)不可以繼續(xù)獲得該表的S鎖
• 別的事務(wù)不可以繼續(xù)獲得表中某些記錄的S鎖
• 別的事務(wù)不可以繼續(xù)獲得該表的X鎖
• 別的事務(wù)不可以繼續(xù)獲得表中某些記錄的X鎖

「所以修改線上的表時一定要小心，因為會使大量事務(wù)阻塞」，目前有很多成熟的修改線上表的方法，不再贅述

隔離級別

讀未提交：每次讀取最新的記錄，沒有做特殊處理 串行化：事務(wù)串行執(zhí)行，不會產(chǎn)生并發(fā)

所以我們重點關(guān)注「讀已提交」和「可重復(fù)讀」的隔離實現(xiàn)！

「這兩種隔離級別是通過MVCC（多版本并發(fā)控制）來實現(xiàn)的，本質(zhì)就是MySQL通過undolog存儲了多個版本的歷史數(shù)據(jù)，根據(jù)規(guī)則讀取某一歷史版本的數(shù)據(jù)，這樣就可以在無鎖的情況下實現(xiàn)讀寫并行，提高數(shù)據(jù)庫性能」

「那么undolog是如何存儲修改前的記錄？」

「對于使用InnoDB存儲引擎的表來說，聚集索引記錄中都包含下面2個必要的隱藏列」

「trx_id」：一個事務(wù)每次對某條聚集索引記錄進(jìn)行改動時，都會把該事務(wù)的事務(wù)id賦值給trx_id隱藏列

「roll_pointer」：每次對某條聚集索引記錄進(jìn)行改動時，都會把舊的版本寫入undo日志中。這個隱藏列就相當(dāng)于一個指針，通過他找到該記錄修改前的信息

如果一個記錄的name從貂蟬被依次改為王昭君，西施，會有如下的記錄，多個記錄構(gòu)成了一個版本鏈

「為了判斷版本鏈中哪個版本對當(dāng)前事務(wù)是可見的，MySQL設(shè)計出了ReadView的概念」。4個重要的內(nèi)容如下

「m_ids」：在生成ReadView時，當(dāng)前系統(tǒng)中活躍的事務(wù)id列表「min_trx_id」：在生成ReadView時，當(dāng)前系統(tǒng)中活躍的最小的事務(wù)id，也就是m_ids中的最小值「max_trx_id」：在生成ReadView時，系統(tǒng)應(yīng)該分配給下一個事務(wù)的事務(wù)id值「creator_trx_id」：生成該ReadView的事務(wù)的事務(wù)id

當(dāng)對表中的記錄進(jìn)行改動時，執(zhí)行insert，delete，update這些語句時，才會為事務(wù)分配唯一的事務(wù)id，否則一個事務(wù)的事務(wù)id值默認(rèn)為0。

max_trx_id并不是m_ids中的最大值，事務(wù)id是遞增分配的。比如現(xiàn)在有事務(wù)id為1，2，3這三個事務(wù)，之后事務(wù)id為3的事務(wù)提交了，當(dāng)有一個新的事務(wù)生成ReadView時，m_ids的值就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4

執(zhí)行過程如下：

1. 如果被訪問版本的trx_id=creator_id，意味著當(dāng)前事務(wù)在訪問它自己修改過的記錄，所以該版本可以被當(dāng)前事務(wù)訪問
2. 如果被訪問版本的trx_id
3. 被訪問版本的trx_id>=max_trx_id，表明生成該版本的事務(wù)在當(dāng)前事務(wù)生成ReadView后才開啟，該版本不可以被當(dāng)前事務(wù)訪問
4. 被訪問版本的trx_id是否在m_ids列表中 4.1 是，創(chuàng)建ReadView時，該版本還是活躍的，該版本不可以被訪問。順著版本鏈找下一個版本的數(shù)據(jù)，繼續(xù)執(zhí)行上面的步驟判斷可見性，如果最后一個版本還不可見，意味著記錄對當(dāng)前事務(wù)完全不可見 4.2 否，創(chuàng)建ReadView時，生成該版本的事務(wù)已經(jīng)被提交，該版本可以被訪問

「好了，我們知道了版本可見性的獲取規(guī)則，那么是怎么實現(xiàn)讀已提交和可重復(fù)讀的呢？」

其實很簡單，就是生成ReadView的時機(jī)不同

舉個例子，先建立如下表

CREATE?TABLE?`girl`?(
??`id`?int(11)?NOT?NULL,
??`name`?varchar(255),
??`age`?int(11),
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

Read Committed

「Read Committed（讀已提交），每次讀取數(shù)據(jù)前都生成一個ReadView」

下面是3個事務(wù)執(zhí)行的過程，一行代表一個時間點「先分析一下5這個時間點select的執(zhí)行過程」

1. 系統(tǒng)中有兩個事務(wù)id分別為100，200的事務(wù)正在執(zhí)行
2. 執(zhí)行select語句時生成一個ReadView，mids=[100,200]，min_trx_id=100，max_trx_id=201，creator_trx_id=0（select這個事務(wù)沒有執(zhí)行更改操作，事務(wù)id默認(rèn)為0）
3. 最新版本的name列為西施，該版本trx_id值為100，在mids列表中，不符合可見性要求，根據(jù)roll_pointer跳到下一個版本
4. 下一個版本的name列王昭君，該版本的trx_id值為100，也在mids列表內(nèi)，因此也不符合要求，繼續(xù)跳到下一個版本
5. 下一個版本的name列為貂蟬，該版本的trx_id值為10，小于min_trx_id，因此最后返回的name值為貂蟬

「再分析一下8這個時間點select的執(zhí)行過程」

1. 系統(tǒng)中有一個事務(wù)id為200的事務(wù)正在執(zhí)行（事務(wù)id為100的事務(wù)已經(jīng)提交）
2. 執(zhí)行select語句時生成一個ReadView，mids=[200]，min_trx_id=200，max_trx_id=201，creator_trx_id=0
3. 最新版本的name列為楊玉環(huán)，該版本trx_id值為200，在mids列表中，不符合可見性要求，根據(jù)roll_pointer跳到下一個版本
4. 下一個版本的name列為西施，該版本的trx_id值為100，小于min_trx_id，因此最后返回的name值為西施

當(dāng)事務(wù)id為200的事務(wù)提交時，查詢得到的name列為楊玉環(huán)。

Repeatable Read

「Repeatable Read（可重復(fù)讀），在第一次讀取數(shù)據(jù)時生成一個ReadView」可重復(fù)讀因為只在第一次讀取數(shù)據(jù)的時候生成ReadView，所以每次讀到的是相同的版本，即name值一直為貂蟬，具體的過程上面已經(jīng)演示了兩遍了，我這里就不重復(fù)演示了，相信你一定會自己分析了。