MySQL數(shù)據(jù)庫InnoDB引擎行級鎖鎖定范圍是什么

124次閱讀

共計 12636 個字符，預(yù)計需要花費 32 分鐘才能閱讀完成。

自動寫代碼機(jī)器人，免費開通

這篇文章主要介紹 MySQL 數(shù)據(jù)庫 InnoDB 引擎行級鎖鎖定范圍是什么，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

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

背景知識

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

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 鎖）的概念在許多編程語言中都出現(xiàn)過。先來描述一下這兩種鎖在 MySQL 中的影響結(jié)果：

如果一個事務(wù)對某一行數(shù)據(jù)加了 S 鎖，另一個事務(wù)還可以對相應(yīng)的行加 S 鎖，但是不能對相應(yīng)的行加 X 鎖。

如果一個事務(wù)對某一行數(shù)據(jù)加了 X 鎖，另一個事務(wù)既不能對相應(yīng)的行加 S 鎖也不能加 X 鎖。

用一張經(jīng)典的矩陣表格繼續(xù)說明共享鎖和排他鎖的互斥關(guān)系：

–SXS01X11

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

1.2 Record Locks、Gap Locks、Next-Key Locks

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

以下定義摘自 MySQL 官方文檔

記錄鎖（Record Locks）: 記錄鎖鎖定索引中一條記錄。

間隙鎖（Gap Locks）: 間隙鎖要么鎖住索引記錄中間的值，要么鎖住第一個索引記錄前面的值或者最后一個索引記錄后面的值。

Next-Key Locks:Next-Key 鎖是索引記錄上的記錄鎖和在索引記錄之前的間隙鎖的組合。

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

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

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

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

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

最后對于間隙鎖還需要補(bǔ)充三點：

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

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

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

2. 加鎖語句

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

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

select … from lock in share mode 語句：這條語句和普通 select 語句的區(qū)別就是后面加了 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 只會在將要插入的那一行上設(shè)置一個排他的索引記錄鎖。

最后補(bǔ)充兩點：

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

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

SQL 語句驗證

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

1. 測試環(huán)境

數(shù)據(jù)庫：MySQL 5.6.35
事務(wù)隔離級別：Repeatable read
數(shù)據(jù)庫訪問終端：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;

插入數(shù)據(jù)：

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

首先我們執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name= e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 a 不阻塞 b 不阻塞 d 阻塞 e 阻塞 f 阻塞 h 不阻塞 i 不阻塞

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

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

執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name= e for update;–3–SELECT * FROM user where name=#{name} for update;5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 d 不阻塞 e 阻塞 f 不阻塞

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

觀察執(zhí)行結(jié)果可知，d 和 f 為間隙鎖，e 為索引記錄鎖。

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

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

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

前兩個構(gòu)成了 next-key 鎖(c,e]。

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

說的這里細(xì)心的讀者可能已經(jīng)發(fā)現(xiàn)我們的測試數(shù)據(jù)中沒有間隙的邊界數(shù)據(jù) c 和 g。接下來我們就對間隙邊界值進(jìn)行測試。

執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name= e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name});5rollback;–6–rollback;

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

id 的值 name= c 執(zhí)行結(jié)果 id 的值 name= g 執(zhí)行結(jié)果 ——-3g 組塞 ——-2g 阻塞 -1c 不阻塞 -1g 阻塞 1c 不阻塞 1g 不阻塞 2c 不阻塞 2g 阻塞 3c 不阻塞 3g 不阻塞 4c 阻塞 4g 阻塞 5c 阻塞 5g 阻塞 6c 阻塞 6g 阻塞 7c 不阻塞 7g 不阻塞 8c 阻塞 8g 不阻塞 9c 不阻塞 9g 不阻塞 10c 阻塞 10g 不阻塞 11c 阻塞 —12c 阻塞 —

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

如果先不看 id= 5 這一行數(shù)據(jù)的結(jié)果，我們發(fā)現(xiàn)一個規(guī)律：

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

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

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

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

執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name= e for update;–3–SELECT * FROM user where id=#{id} for update;5rollback;–6–rollback;

替換步驟 5 中 id 的值，觀察結(jié)果：

id 的值執(zhí)行結(jié)果 3 不阻塞 4 不阻塞 5 阻塞 6 不阻塞 7 不阻塞

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

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

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

對輔助索引對應(yīng)的聚合索引加上索引記錄鎖。

當(dāng)輔助索引為間隙鎖“最小”和“最大”值時，對聚合索引相應(yīng)的行加間隙鎖。“最小”鎖定對應(yīng)聚合索引之后的行間隙。“最大”值鎖定對應(yīng)聚合索引之前的行間隙。

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

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 列為唯一索引。

插入數(shù)據(jù)：

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

首先我們執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name= e for update;3–begin;4–INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 e 阻塞 f 不阻塞 g 不阻塞 h 不阻塞 i 不阻塞

由測試結(jié)果可知，只有 name= e 這行數(shù)據(jù)被鎖定。

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

2.3 場景三

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

借用場景一的表和數(shù)據(jù)。

建表：

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;

插入數(shù)據(jù)：

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

執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`) VALUES (10 , #{name});5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 a 阻塞 b 阻塞 c 阻塞 d 阻塞 e 阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞

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

我們執(zhí)行以下的 SQL 語句執(zhí)行計劃：

 explain select * from user where name e  for update;

執(zhí)行結(jié)果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| 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)

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

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

接下來我們再制造一組數(shù)據(jù)。
建表：

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;

插入數(shù)據(jù)：

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

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

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

 explain select * from user where name e  for update;

執(zhí)行結(jié)果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| 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)

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

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

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

g 和 i 對應(yīng) id 列中的 7 和 9 加索引記錄鎖。

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

接下來我們逐一測試：

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`, `age`) VALUES (10 , #{name}, 18 5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞 j 阻塞 k 阻塞

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–SELECT * FROM user where name=#{name} for update;5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 e 不阻塞 f 不阻塞 g 阻塞 h 不阻塞 i 阻塞 j 不阻塞

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–SELECT * FROM user where id=#{id} for update;5rollback;–6–rollback;

替換步驟 5 中 id 的值，觀察結(jié)果：

id 的值執(zhí)行結(jié)果 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, e , 18 5rollback;–6–rollback;

替換步驟 5 中 id 的值，觀察結(jié)果：

id 的值執(zhí)行結(jié)果 - 1 不阻塞 1 不阻塞 2 不阻塞 3 不阻塞 4 不阻塞 5 不阻塞 6 阻塞 7 阻塞 8 阻塞 9 阻塞 10 阻塞 11 阻塞 12 阻塞

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

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

2.4 場景四

我們通過場景三驗證了普通索引的范圍查詢語句加鎖范圍，現(xiàn)在我們來驗證一下唯一索引的范圍查詢情況下的加鎖范圍。有了場景三的鋪墊我們直接跳過掃描全部索引的情況，創(chuàng)建可以掃描范圍記錄的表結(jié)構(gòu)并插入相應(yīng)數(shù)據(jù)測試。

建表：

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;

插入數(shù)據(jù)：

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 這兩條記錄加上記錄鎖無法避免幻讀的發(fā)生，索引鎖定范圍應(yīng)該還是兩條數(shù)據(jù) next-key 鎖鎖的組合：(e,g],(g,i],(i,+∞)。其中 g,i 為索引記錄鎖。

我們通過 SQL 驗證我們的結(jié)論，執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`, `age`) VALUES (10 , #{name}, 18 5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞 j 阻塞 k 阻塞

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–SELECT * FROM user where name=#{name} for update;5rollback;–6–rollback;

替換步驟 5 中 name 的值，觀察結(jié)果：

name 的值執(zhí)行結(jié)果 e 不阻塞 f 不阻塞 g 阻塞 h 不阻塞 i 阻塞 j 不阻塞

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

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

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–SELECT * FROM user where id=#{id} for update;5rollback;–6–rollback;

替換步驟 5 中 id 的值，觀察結(jié)果：

id 的值執(zhí)行結(jié)果 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞

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

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

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

執(zhí)行 SQL 語句的模板：

步驟 client 1client 21begin;–2SELECT * FROM user where name e for update;–3–begin;4–INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, e , 18 5rollback;–6–rollback;

替換步驟 5 中 id 的值，觀察結(jié)果：

id 的值執(zhí)行結(jié)果 - 1 不阻塞 1 不阻塞 2 不阻塞 3 不阻塞 4 不阻塞 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞 11 不阻塞 12 不阻塞

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

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

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

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

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

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

結(jié)論

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

備注

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

以上是“MySQL 數(shù)據(jù)庫 InnoDB 引擎行級鎖鎖定范圍是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

向 AI 問一下細(xì)節(jié)

正文完