前言

還是老規(guī)矩，首先提出幾個待解決的問題：

• MySQL InnoDB存儲引擎中事務(wù)的隔離級別有哪些？
• 對應(yīng)隔離級別的實現(xiàn)機制是什么？
• 并發(fā)事務(wù)下，MySQL如何保證事務(wù)的隔離性？

本文就將對上面這兩個問題進行解答，分析事務(wù)的隔離級別以及相關(guān)鎖機制。

隔離性簡介

隔離性主要是指數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的"獨立"環(huán)境執(zhí)行，意思就是多個事務(wù)并發(fā)執(zhí)行時，一個事務(wù)的執(zhí)行不應(yīng)影響其它事務(wù)的執(zhí)行。

4種隔離級別介紹

在SQL標(biāo)準(zhǔn)中定義了4種隔離級別，分別是：

• Read uncommitted: 未提交讀，事務(wù)中的修改，即使沒有提交，對其他事務(wù)也是可見的。存在臟讀
• Read committed: 提交讀，大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別(MySQL不是), 一個事務(wù)從開始到提交之前，所做的修改對其他事務(wù)不可見。解決臟讀，存在幻讀和不可重復(fù)讀
• repeatable read: 可重復(fù)讀，該級別保證在同一事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。解決臟讀和不可重復(fù)讀，理論上存在幻讀，但是在InnoDB引擎中解決了幻讀
• Serializable：可串行化，強制事務(wù)串行執(zhí)行。

上面4種隔離級別是SQL標(biāo)準(zhǔn)定義的，但是在不同的存儲引擎中，實現(xiàn)的隔離級別不盡相同。本文主要介紹MySQL InnoDB 存儲引擎中的隔離級別，在InnoDB存儲引擎中，Repeatable Read 是默認的事務(wù)隔離級別，同時該引擎的實現(xiàn)基于多版本的并發(fā)控制協(xié)議——MVCC (Multi-Version Concurrency Control)，解決了幻讀問題，當(dāng)然 臟讀和不可重復(fù)讀也是不存在的。MVCC最大的好處就在于讀不加鎖，讀寫不沖突，這樣極大的增加了系統(tǒng)的并發(fā)性能

Read uncommitted

未提交讀，這種情況下，一個事務(wù)A可以看到另一個事務(wù)B未提交的數(shù)據(jù)，如果此時事務(wù)B發(fā)生回滾，那么事務(wù)A拿到的就是臟數(shù)據(jù)，這也就是臟讀的含義。此隔離級別在MySQL InnoDB一般不會使用，不做過多說明。

Read Committed

提交讀，一個事務(wù)從開始直到提交之前，所做的任何修改對其他事務(wù)都是不可見的。解決了臟讀問題，但是存在幻讀現(xiàn)象。

所謂幻讀，指的是在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的SQL語句可能導(dǎo)致不同的結(jié)果，第二次的SQL語句可能會返回之前不存在的行，也就是"幻行"。

比如下面這個例子：

1. 首先創(chuàng)建一張表,

CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB

insert into t(a) values(1);
insert into t(a) values(2);
insert into t(a) values(4);

2. 分別執(zhí)行事務(wù)1和事務(wù)2：

read_committed

可以從上圖看出，Read Committed這種隔離級別存在幻讀現(xiàn)象。實際上，Read Committed還可能存在不可重復(fù)讀的問題，不可重復(fù)讀，指的是一個事務(wù)內(nèi)根據(jù)同一條件對行記錄進行多次查詢，但是查詢出的數(shù)據(jù)結(jié)果不一致，原因就是查詢區(qū)間數(shù)據(jù)被其他事務(wù)修改了。

不可重復(fù)讀感覺和幻讀有點像，實際上，前者強調(diào)是同一行記錄數(shù)據(jù)結(jié)果不一樣，后者強調(diào)的時多次查詢返回的結(jié)果集不一樣，增加了或減少了。

Repeatable Read

可重復(fù)讀，該級別保證在同一事務(wù)中多次讀取同樣記錄的結(jié)果是一致的，在InnoDB存儲引擎中同時解決了幻讀和不可重復(fù)讀問題。至于InnoDB通過什么方式解決幻讀和不可重復(fù)讀問題，后續(xù)內(nèi)容揭曉。

Serializable (可串行化)

Serializable 是最高的隔離級別，它通過 強制事務(wù)串行執(zhí)行，避免了幻讀的問題，但是 Serializable 會在讀取的每一行數(shù)據(jù)上都加鎖，所以可能導(dǎo)致大量的超時和鎖爭用的問題,因此并發(fā)度急劇下降，在MySQL InnoDB不被建議使用

Read Committed隔離級別下的加鎖分析

隔離級別的實現(xiàn)與鎖機制密不可分，所以需要引入鎖的概念，首先我們看下InnoDB存儲引擎提供的兩種標(biāo)準(zhǔn)的行級鎖：

• 共享鎖(S Lock)：又稱為讀鎖，可以允許多個事務(wù)并發(fā)的讀取同一資源，互不干擾。即如果一個事務(wù)T對數(shù)據(jù)A加上共享鎖后，其他事務(wù)只能對A再加共享鎖，不能再加排他鎖，只能讀數(shù)據(jù)，不能修改數(shù)據(jù)
• 排他鎖(X Lock): 又稱為寫鎖，如果事務(wù)T對數(shù)據(jù)A加上排他鎖后，其他事務(wù)不能再對A加上任何類型的鎖，獲取排他鎖的事務(wù)既能讀數(shù)據(jù)，也能修改數(shù)據(jù)。

注意： 共享鎖和排他鎖是不相容的。

MySQL InnoDB存儲引擎是使用多版本并發(fā)控制的，讀不加鎖，讀寫不沖突，除非特定場景下的顯示加讀鎖（這里不去探究）。本小節(jié)主要分析Read Committed隔離級別下的加鎖情況，在MVCC的作用下，一般也就是寫操作加X鎖了。

加鎖操作是和索引緊密相關(guān)的，對一個SQL語句進行加鎖分析時，也要仔細考究其屬性列上的索引類型。假設(shè)有數(shù)據(jù)表t1，有兩個列，name列和id列，插入了幾條數(shù)據(jù)，沒有明確索引情況：

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);

下面執(zhí)行 delete from t1 where id = 10 這條SQL語句，這里的隔離級別設(shè)置為Read Committed，從這條SQL語句不能得知id列的索引情況，所以需要分情況討論：

• id列是主鍵
• id列是二級唯一索引
• id列是二級非唯一索引
• id列上沒有索引

下面是對以上幾種情況的加鎖情況進行歸納總結(jié)，更詳細的內(nèi)容可以參閱數(shù)據(jù)庫大牛的文章：MySQL 加鎖處理分析

id列是主鍵

id是主鍵時，上述SQL只需要在id=10這條記錄上加X鎖即可

id列是二級唯一索引

若id列是唯一索引，而主鍵是name列，那么SQL需要加上兩個X鎖，一個對應(yīng)于id索引上的id=10的記錄，另一把鎖對應(yīng)于主鍵索引上的[name="a",id=10]的記錄

id列是二級非唯一索引

若id列上有非唯一索引，那么對應(yīng)的所有滿足SQL查詢條件的記錄，都會被加鎖，同時，這些記錄在主鍵索引上的記錄也會被加鎖。

id列上沒有索引

若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由于過濾是由MySQL Sever層面進行的，因此每條記錄，無論是否滿足條件，都會被加上X鎖。

Repeatable Read隔離級別下的加鎖分析

前面說過，在Repeatable Read隔離級別下，InnoDB存儲引擎解決了幻讀和不可重復(fù)讀問題，具體的原理是怎么樣的呢？

之前簡短的介紹了InnoDB中行鎖的知識，下面來看下行鎖的三種算法：

• Record Lock: 單個索引記錄上的鎖，即加X鎖
• Gap Lock： 間隙鎖，鎖定一個范圍，但不包含記錄自身
• Next-Key Lock: Gap Lock + Record Lock，鎖定一個范圍，并且鎖定本身。

Record Lock總是會去鎖住索引記錄，如果InnoDB存儲引擎在建表的時候沒有設(shè)置任何一個索引，那么這時InnoDB會使用隱式的主鍵來進行鎖定。(表沒有定義主鍵的情況，InnoDB會默認添加一個隱式的主鍵索引)

Next-Key Lock是結(jié)合了Gap Lock和Record Lock的一種鎖定算法，比如一個索引列有10,11,13和20這4個值，那么該索引可能被Next-Key Locking的區(qū)間為：

• ($-\infty$,10)
• (10,11]
• (11,13]
• (13,20]
• (20,$+\infty$)

需要注意一點的是，當(dāng)查詢的索引含有唯一屬性時，即是主鍵索引或者唯一索引時，InnoDB存儲引擎會對Next-Key Lock進行優(yōu)化，將其降級為Record Lock,即僅鎖住索引本身，一般加上X鎖。

Next-Key Lock機制設(shè)計的目的就是為了解決幻讀問題，主要針對查詢列索引為非唯一索引的時候。以下面這個例子進行說明：

1. 首先創(chuàng)建測試表t1，name是主鍵索引，id為非唯一索引，即輔助索引

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `id_indx` (`id`)
) ENGINE=InnoDB

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);

2. 執(zhí)行 delete from t1 where id = 11,其加鎖情況如下圖所示

gap_lock

這條SQL通過索引列id進行刪除操作，該索引為非唯一索引，所以其使用傳統(tǒng)的Next-Key
Locking 技術(shù)加鎖，并且由于有主鍵索引和輔助索引兩個，需要分別進行鎖定。對于主鍵索引（即聚集索引），其僅對列name = "b"的索引加上 Record Lock，實際上就是X鎖。

而對于非唯一索引，其加上的時Next-Key Lock，鎖定范圍是(10,11)，對其加上Gap Lock(間隙鎖)，GAP鎖實際上就是加在兩條邊界記錄之間的位置。還需要注意的是，InnoDB還會對輔助索引下一個鍵值加上gap lock，即看到在(11,13)之間加了一個GAP鎖。對于11值本身加上Record Lock，即X鎖。

若此時開啟另外一個事務(wù)執(zhí)行下面的語句，就會阻塞：

1. select * from t1 where name = "b";
2. insert into t1(name,id) values("c",12);

比如第一條語句不能執(zhí)行，因為在開始的事務(wù)中已經(jīng)對聚集索引中的列name="b"的值加上了X鎖。因此執(zhí)行會被阻塞。而第二個SQL，同樣不能執(zhí)行，插入的值12在鎖定范圍(11,13)中，需要阻塞等待。

所以，從上例就可以看出，GAP Lock的作用就是為了阻止多個事務(wù)將記錄插入到同一范圍內(nèi)，這樣就有效的解決了幻讀問題。

隔離級別總結(jié)

下面總結(jié)下InnoDB存儲引擎下的各種隔離級別：

參考資料 & 鳴謝

• MySQL技術(shù)內(nèi)幕:InnoDB存儲引擎(第2版)
• MySQL 加鎖處理分析