mysql數據庫innodb引擎支持行級鎖，也就是說我們可以對表中某些行數據執行鎖定操作，鎖定操作的影響是：如果一個事物對表中某行執行了鎖定操作，而另一個事務也需要對同樣的行執行鎖定操作，這樣第二個事務的鎖定操作有可能被阻塞，一旦被阻塞第二個事務只能等到第一個事務執行完畢（提交或回滾）或超時。

本文主要介紹InnoDB中的行鎖相關概念，重點介紹行鎖的鎖定范圍：

• 什么樣的sql語句會加鎖？

• 加什么樣的鎖？

• 加鎖語句會鎖定哪些行？

背景知識

上面我們簡單的介紹了InnoDB的行級鎖，為了理解后面的驗證部分，需要補充一下背景知識。如果對相應知識非常了解，可以直接跳轉到驗證部分內容。

1. InnoDB鎖的類型

InnoDB引擎使用了七種類型的鎖，他們分別是：

• 共享排他鎖（Shared and Exclusive Locks）

• 意向鎖（Intention Locks）

• 記錄鎖（Record Locks）

• 間隙鎖（Gap Locks）

• Next-Key Locks

• 插入意圖鎖（Insert Intention Locks）

• 自增鎖（auto-INC Locks）

本文主要涉及Shared and Exclusive Locks，Record Locks，Gap Locks，Next-Key Locks這幾種鎖，其他類型鎖如果大家感興趣可以自己深入了解，在此不在詳述。

1.1 Shared and Exclusive Locks

共享鎖（S鎖）和排他鎖（X鎖）的概念在許多編程語言中都出現過。先來描述一下這兩種鎖在mysql中的影響結果：

• 如果一個事務對某一行數據加了S鎖，另一個事務還可以對相應的行加S鎖，但是不能對相應的行加X鎖。

• 如果一個事務對某一行數據加了X鎖，另一個事務既不能對相應的行加S鎖也不能加X鎖。

用一張經典的矩陣表格繼續說明共享鎖和排他鎖的互斥關系：

圖中S表示共享鎖X表示獨占鎖，0表示鎖兼容1表示鎖沖突，兼容不被阻塞，沖突被阻塞。由表可知一旦一個事務加了排他鎖，其他個事務加任何鎖都需要等待。多個共享鎖不會相互阻塞。

1.2 Record Locks、Gap Locks、Next-Key Locks

這三種類型的鎖都描述了鎖定的范圍，故放在一起說明。

以下定義摘自MySQL官方文檔

記錄鎖（Record Locks）:記錄鎖鎖定索引中一條記錄。間隙鎖（Gap Locks）:間隙鎖要么鎖住索引記錄中間的值，要么鎖住第一個索引記錄前面的值或者最后一個索引記錄后面的值。Next-Key Locks:Next-Key鎖是索引記錄上的記錄鎖和在索引記錄之前的間隙鎖的組合。

定義中都提到了索引記錄（index record）。為什么？行鎖和索引有什么關系呢？其實，InnoDB是通過搜索或者掃描表中索引來完成加鎖操作，InnoDB會為他遇到的每一個索引數據加上共享鎖或排他鎖。所以我們可以稱行級鎖（row-level locks）為索引記錄鎖（index-record locks），因為行級鎖是添加到行對應的索引上的。

三種類型鎖的鎖定范圍不同，且逐漸擴大。我們來舉一個例子來簡要說明各種鎖的鎖定范圍，假設表t中索引列有3、5、8、9四個數字值，根據官方文檔的確定三種鎖的鎖定范圍如下：

• 記錄鎖的鎖定范圍是單獨的索引記錄，就是3、5、8、9這四行數據。

• 間隙鎖的鎖定為行中間隙，用集合表示為(-∞,3)、(3,5)、(5,8)、(8,9)、(9,+∞)。

• Next-Key鎖是有索引記錄鎖加上索引記錄鎖之前的間隙鎖組合而成，用集合的方式表示為(-∞,3]、(3,5]、(5,8]、(8,9]、(9,+∞)。

最后對于間隙鎖還需要補充三點：

1. 間隙鎖阻止其他事務對間隙數據的并發插入，這樣可有有效的解決幻讀問題(Phantom Problem)。正因為如此，并不是所有事務隔離級別都使用間隙鎖，MySQL InnoDB引擎只有在Repeatable Read（默認）隔離級別才使用間隙鎖。

2. 間隙鎖的作用只是用來阻止其他事務在間隙中插入數據，他不會阻止其他事務擁有同樣的的間隙鎖。這就意味著，除了insert語句，允許其他SQL語句可以對同樣的行加間隙鎖而不會被阻塞。

3. 對于唯一索引的加鎖行為，間隙鎖就會失效，此時只有記錄鎖起作用。

2. 加鎖語句

前面我們已經介紹了InnoDB的是在SQL語句的執行過程中通過掃描索引記錄的方式來實現加鎖行為的。那哪些些語句會加鎖？加什么樣的鎖？接下來我們逐一描述：

• select … from語句：InnoDB引擎采用多版本并發控制（MVCC）的方式實現了非阻塞讀，所以對于普通的select讀語句，InnoDB并不會加鎖【注1】。

• select … from lock in share mode語句：這條語句和普通select語句的區別就是后面加了lock in share mode，通過字面意思我們可以猜到這是一條加鎖的讀語句，并且鎖類型為共享鎖（讀鎖）。InnoDB會對搜索的所有索引記錄加next-key鎖，但是如果掃描的唯一索引的唯一行，next-key降級為索引記錄鎖。

• select … from for update語句：和上面的語句一樣，這條語句加的是排他鎖（寫鎖）。InnoDB會對搜索的所有索引記錄加next-key鎖，但是如果掃描唯一索引的唯一行，next-key降級為索引記錄鎖。

• update … where …語句：。InnoDB會對搜索的所有索引記錄加next-key鎖，但是如果掃描唯一索引的唯一行，next-key降級為索引記錄鎖。【注2】

• delete … where …語句：。InnoDB會對搜索的所有索引記錄加next-key鎖，但是如果掃描唯一索引的唯一行，next-key降級為索引記錄鎖。

• insert語句：InnoDB只會在將要插入的那一行上設置一個排他的索引記錄鎖。

最后補充兩點：

1. 如果一個查詢使用了輔助索引并且在索引記錄加上了排他鎖，InnoDB會在相對應的聚合索引記錄上加鎖。

2. 如果你的SQL語句無法使用索引，這樣MySQL必須掃描整個表以處理該語句，導致的結果就是表的每一行都會被鎖定，并且阻止其他用戶對該表的所有插入。

SQL語句驗證

閑言少敘，接下來我們進入本文重點SQL語句驗證部分。

1.測試環境

數據庫：MySQL 5.6.35 ?
事務隔離級別：Repeatable read ?
數據庫訪問終端：mysql client

2.驗證場景

2.1 場景一

建表：

CREATE TABLE `user` (   `id` int(11) NOT NULL,   `name` varchar(8) NOT NULL,   PRIMARY KEY (`id`),   KEY `name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數據：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');  INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');  INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');  INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');  INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我們執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

觀察結果，我們發現SQL語句
SELECT * FROM user where name=’e’ for update
一共鎖住索引name中三行記錄，(c,e]區間應該是next-key鎖而(e,h)區間是索引記錄e后面的間隙。

接下來我們確定next-key鎖中哪部分是索引記錄鎖哪部分是間隙鎖。

執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

因為間隙鎖只會阻止insert語句，所以同樣的索引數據，insert語句阻塞而select for update語句不阻塞的就是間隙鎖，如果兩條語句都阻塞就是索引記錄鎖。

觀察執行結果可知，d和f為間隙鎖，e為索引記錄鎖。

結論：通過兩條SQL，我們確定了對于輔助索引name在查詢條件為 where name=’e’ ?時的加鎖范圍為（c,e],(e,g),其中：

• 對SQL語句掃描的索引記錄e加索引記錄鎖[e]。

• 鎖定了e前面的間隙，c到e之間的數據(c,e)加了間隙鎖

• 前兩個構成了next-key鎖(c,e]。

• 值得注意的是還鎖定了e后面的間隙(e,g)。

說的這里細心的讀者可能已經發現我們的測試數據中沒有間隙的邊界數據c和g。接下來我們就對間隙邊界值進行測試。

執行SQL語句的模板：

替換步驟5中id，name的值，觀察結果：

通過觀察以上執行結果，我們發現，name等于c和e時insert語句的結果隨著id值得不同一會兒鎖定，一會兒不鎖定。那一定是id列加了鎖才會造成這樣的結果。

如果先不看id=5這一行數據的結果，我們發現一個規律：

• 當name=c時，name=c對應的id=3的id聚合索引數據記錄之后的間隙(3,5)，(5,7)，(7,9)，(9,∞)都被加上了鎖。

• 當name=e時，name=e對應的id=7的id聚合索引數據記錄之前的間隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了鎖。

• 我們可用select * from user where id = x for update;語句判斷出以上間隙上加的鎖都為間隙鎖。

接下來我們解釋一下id=5的鎖定情況

執行SQL語句的模板：

替換步驟5中id的值，觀察結果：

通過觀察執行結果可知，id=5的聚合索引記錄上添加了索引記錄鎖。根據MySQL官方文檔描述，InnoDB引擎在對輔助索引加鎖的時候，也會對輔助索引所在行所對應的聚合索引（主鍵）加鎖。而主鍵是唯一索引，在對唯一索引加鎖時，間隙鎖失效，只使用索引記錄鎖。所以SELECT * FROM user where name=’e’ for update;不僅對輔助索引name=e列加上了next-key鎖，還對對應的聚合索引id=5列加上了索引記錄鎖。

最終結論： ?
對于SELECT * FROM user where name=’e’ for update;一共有三種鎖定行為:

1. 對SQL語句掃描過的輔助索引記錄行加上next-key鎖（注意也鎖住記錄行之后的間隙）。

2. 對輔助索引對應的聚合索引加上索引記錄鎖。

3. 當輔助索引為間隙鎖“最小”和“最大”值時，對聚合索引相應的行加間隙鎖。“最小”鎖定對應聚合索引之后的行間隙。“最大”值鎖定對應聚合索引之前的行間隙。

上面我們將對輔助索引加鎖的情況介紹完了，接下來我們測試一下對聚合索引和唯一索引加鎖。

2.2 場景二

建表：

CREATE TABLE `user` (    `id` int(11) NOT NULL,    `name` varchar(8) NOT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `index_name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意與場景一表user不同的是name列為唯一索引。

插入數據：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');  INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');  INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');  INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');  INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我們執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

由測試結果可知，只有name=’e’這行數據被鎖定。

通過SQL語句我們驗證了，對于唯一索引列加鎖，間隙鎖失效，

2.3 場景三

場景一和場景二都是在查詢條件等于的情況下做出的范圍判斷，現在我們嘗試一下其他查詢條件，看看結論是否一致。

借用場景一的表和數據。

建表：

CREATE TABLE `user` (    `id` int(11) NOT NULL,    `name` varchar(8) NOT NULL,    PRIMARY KEY (`id`),    KEY `index_name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數據：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');  INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');  INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');  INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');  INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

這個結果是不是和你想象的不太一樣，這個結果表明where name>’e’這個查詢條件并不是鎖住’e’列之后的數據，而鎖住了所有name列中所有數據和間隙。這是為什么呢？

我們執行以下的SQL語句執行計劃：

 explain select * from user where name>'e' for update;

執行結果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+  | id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |  +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+  |  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |  +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+  1 row in set (0.00 sec)

如果你的結果與上面不同先執行一下OPTIMIZE TABLE user;再執行以上語句。

通過觀察SQL語句的執行計劃我們發現，語句使用了name列索引，且rows參數等于5，user表中一共也只有5行數據。SQL語句的執行過程中一共掃描了name索引記錄5行數據且對這5行數據都加上了next-key鎖，符合我們上面的執行結果。

接下來我們再制造一組數據。 ?
建表：

CREATE TABLE `user` (   `id` int(11) NOT NULL,   `name` varchar(8) NOT NULL,   `age` int(11) NOT NULL,   PRIMARY KEY (`id`),   KEY `index_name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數據：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

這張表和前表的區別是多了一列非索引列age。

我們再執行一下同樣的SQL語句執行計劃：

 explain select * from user where name>'e' for update;

執行結果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+  | id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |  +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+  |  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |  +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+  1 row in set (0.00 sec)

是不是和第一次執行結果不同了，rows參數等于2，說明掃描了兩行記錄，結合SQL語句select * from user where name>’e’ for update;執行后返回結果我們判斷這兩行記錄應該為g和i。

因為select * from user where name>’e’ for update;語句掃描了兩行索引記錄分別是g和i，所以我們將g和i的鎖定范圍疊就可以得到where name>’e’的鎖定范圍：

1. 索引記錄g在name列鎖定范圍為(e,g],(g,i)。索引記錄i的在name列鎖定范圍為(g,i],(i,+∞)。兩者疊加后鎖定范圍為(e,g],(g,i],(i,+∞)。其中g,i為索引記錄鎖。

2. g和i對應id列中的7和9加索引記錄鎖。

3. 當name列的值為鎖定范圍上邊界e時，還會在e所對應的id列值為5之后的所有值之間加上間隙鎖，范圍為(5,7),(7,9),(9,+∞)。下邊界為+∞無需考慮。

接下來我們逐一測試：

首先測試驗證了next-key鎖范圍，執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

下面驗證next-key鎖中哪部分是間隙鎖，哪部分是索引記錄鎖，執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

接下來驗證對id列加索引記錄鎖，執行SQL語句的模板：

替換步驟5中id的值，觀察結果：

最后我們驗證name列的值為邊界數據e時，id列間隙鎖的范圍，執行SQL語句的模板：

替換步驟5中id的值，觀察結果：

注意7和9是索引記錄鎖記錄鎖。

觀察上面的所有SQL語句執行結果，可以驗證select * from user where name>’e’ for update的鎖定范圍為此語句掃描name列索引記錄g和i的鎖定范圍的疊加組合。

2.4 場景四

我們通過場景三驗證了普通索引的范圍查詢語句加鎖范圍，現在我們來驗證一下唯一索引的范圍查詢情況下的加鎖范圍。有了場景三的鋪墊我們直接跳過掃描全部索引的情況，創建可以掃描范圍記錄的表結構并插入相應數據測試。

建表：

CREATE TABLE `user` (    `id` int(11) NOT NULL,    `name` varchar(8) NOT NULL,    `age` int(11) NOT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `index_name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數據：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');  INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

和場景三表唯一不同是name列為唯一索引。

SQL語句select * from user where name>’e’掃描name列兩條索引記錄g和i。如果需要只對g和i這兩條記錄加上記錄鎖無法避免幻讀的發生，索引鎖定范圍應該還是兩條數據next-key鎖鎖的組合：(e,g],(g,i],(i,+∞)。其中g,i為索引記錄鎖。

我們通過SQL驗證我們的結論，執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

下面驗證next-key鎖中哪部分是間隙鎖，哪部分是索引記錄鎖，執行SQL語句的模板：

替換步驟5中name的值，觀察結果：

通過上面兩條SQL語句的驗證結果，我們證明了我們的g和i的鎖定范圍趨勢為兩者next-key疊加組合。

接下來我們驗證一下對輔助索引加鎖后對聚合索引的鎖轉移，執行SQL語句的模板：

替換步驟5中id的值，觀察結果：

由結果可知對輔助索引name中的g和i列對應的聚合索引id列中的7和9加上了索引記錄鎖。

到目前為止所有實驗結果和場景三完全一樣，這也很好理解，畢竟場景四和場景三只是輔助索引name的索引類型不同，一個是唯一索引，一個是普通索引。

最后驗證意向，next-key鎖邊界數據e，看看結論時候和場景三相同。

執行SQL語句的模板：

替換步驟5中id的值，觀察結果：

注意7和9是索引記錄鎖記錄鎖。

通過結果可知，當name列為索引記錄上邊界e時，并沒有對id有加鎖行為，這點與場景三不同。

對于唯一索引的范圍查詢和普通索引的范圍查詢類似，唯一不同的是當輔助索引等于上下范圍的邊界值是不會對主鍵加上間隙鎖。

唯一索引范圍查詢加鎖范圍：

• 對于掃描的輔助索引記錄的鎖定范圍就是多個索引記錄next-key范圍的疊加組合。

• 對于聚合索引（主鍵）的鎖定范圍，會對多個輔助索引對應的聚合索引列加索引記錄鎖。

結論

InnoDB引擎會對他掃描過的索引記錄加上相應的鎖，通過“場景一”我們已經明確了掃描一條普通索引記錄的鎖定范圍，通過“場景三”我們可以推斷任意多個掃描普通索引索引記錄的鎖定范圍。通過“場景二”我們確定了掃描一條唯一索引記錄（或主鍵）的鎖定范圍。通過“場景四”我們可以推斷任意多個掃描索唯一引記錄（或主鍵）的鎖定范圍。在實際的應用可以靈活使用，判斷兩條SQL語句是否相互鎖定。這里還需要注意的是對于索引的查詢條件，不能想當然的理解，他往往不是我們理解的樣子，需要結合執行計劃判斷索引最終掃描的記錄數，否則會對加鎖范圍理解產生偏差。

備注

注1：在事務隔離級別為SERIALIZABLE時，普通的select語句也會對語句執行過程中掃描過的索引加上next-key鎖。如果語句掃描的是唯一索引，那就將next-key鎖降級為索引記錄鎖了。 ?
注2：當更新語句修改聚合索引（主鍵）記錄時，會對受影響的輔助索引執行隱性的加鎖操作。當插入新的輔助索引記錄之前執行重復檢查掃描時和當插入新的輔助索引記錄時，更新操作還對受影響的輔助索引記錄添加共享鎖。

相關推薦：

mysql執行sql文件報錯Error: Unknown storage engine‘InnoDB如何解決

mysql執行sql文件報錯Error: Unknown storage engine‘InnoDB如何解決

mysql執行sql文件報錯Error: Unknown storage engine‘InnoDB如何解決