六個(gè)案例搞懂間隙鎖

作者：碼農(nóng)BookSea 2024-01-16 12:19:08

在本文中，我們討論了間隙鎖的加鎖規(guī)則。間隙鎖是MySQL中用于保護(hù)范圍查詢和防止并發(fā)問(wèn)題的重要機(jī)制，了解間隙鎖的加鎖規(guī)則對(duì)于優(yōu)化數(shù)據(jù)庫(kù)性能、減少數(shù)據(jù)沖突以及提高并發(fā)性能非常重要。

MySQL中的間隙是指索引中兩個(gè)索引鍵之間的空間，間隙鎖用于防止范圍查詢期間的幻讀，確保查詢結(jié)果的一致性和并發(fā)安全性。

概念解釋

記錄鎖（Record Lock）

記錄鎖也被稱為行鎖，顧名思義，它是針對(duì)數(shù)據(jù)庫(kù)中的行記錄進(jìn)行的鎖定。

比如：

SELECT * FROM `user` WHERE `id`=1 FOR UPDATE;

上面的SQL會(huì)在 id=1 的行記錄上加上記錄鎖，以阻止其他事務(wù)插入，更新，刪除這一行。

間隙鎖（Gap Lock）

間隙鎖就是對(duì)間隙加鎖，用于鎖定索引范圍之間的間隙，以避免其他事務(wù)在這個(gè)范圍內(nèi)插入新的數(shù)據(jù)。間隙鎖是排它鎖，阻止了其他事務(wù)在間隙中插入滿足條件的值，間隙鎖僅在可重復(fù)讀隔離級(jí)別下才有效。

關(guān)于間隙鎖的詳細(xì)講解放在下文，這里只是先做個(gè)概念上的介紹。

臨鍵鎖（Next-Key Lock）

臨鍵鎖由記錄鎖和間隙鎖組合而成，它在索引范圍內(nèi)的記錄上加上記錄鎖，并在索引范圍之間的間隙上加上間隙鎖。這樣可以避免幻讀（Phantom Read）的問(wèn)題，確保事務(wù)的隔離性。

切記：間隙鎖的區(qū)間是左開(kāi)右開(kāi)的，臨鍵鎖的區(qū)間是左開(kāi)右閉的。

間隙鎖詳解

間隙鎖是保證臨鍵鎖正常運(yùn)作的基礎(chǔ)，理解間隙鎖的概念對(duì)于深入理解這三種鎖非常重要。

間隙鎖的鎖定范圍是指在索引范圍之間的間隙

舉個(gè)簡(jiǎn)單例子來(lái)說(shuō)明：

假設(shè)有一個(gè)名為products的表，其中有一個(gè)整型列product_id作為主鍵索引。現(xiàn)在有兩個(gè)并發(fā)事務(wù)：事務(wù)A和事務(wù)B。

事務(wù)A執(zhí)行以下語(yǔ)句：

BEGIN;
SELECT * FROM `products` WHERE `product_id` BETWEEN 100 and 200 FOR UPDATE;

事務(wù)B執(zhí)行以下語(yǔ)句：

BEGIN;
INSERT INTO `products` (`product_id`, `name`) VALUES (150, 'Product 150');

在這種情況下，事務(wù)A會(huì)在products表中product_id值在 100 和 200 之間的范圍上設(shè)置間隙鎖。因此，在事務(wù)A運(yùn)行期間，其他事務(wù)無(wú)法在這個(gè)范圍內(nèi)插入新的數(shù)據(jù)，在事務(wù)B嘗試插入product_id為150的記錄時(shí)，由于該記錄位于事務(wù)A鎖定的間隙范圍內(nèi)，事務(wù)B將被阻塞，直到事務(wù)A釋放間隙鎖為止。

間隙鎖觸發(fā)條件

在可重復(fù)讀（Repeatable Read）事務(wù)隔離級(jí)別下，以下情況會(huì)產(chǎn)生間隙鎖：

使用普通索引鎖定：當(dāng)一個(gè)事務(wù)使用普通索引進(jìn)行條件查詢時(shí)，MySQL會(huì)在滿足條件的索引范圍之間的間隙上生成間隙鎖。
使用多列唯一索引：如果一個(gè)表存在多列組成的唯一索引，并且事務(wù)對(duì)這些列進(jìn)行條件查詢時(shí)，MySQL會(huì)在滿足條件的索引范圍之間的間隙上生成間隙鎖。
使用唯一索引鎖定多行記錄：當(dāng)一個(gè)事務(wù)使用唯一索引來(lái)鎖定多行記錄時(shí)，MySQL會(huì)在這些記錄之間的間隙上生成間隙鎖，以確保其他事務(wù)無(wú)法在這個(gè)范圍內(nèi)插入新的數(shù)據(jù)。

需要注意的是，上述情況僅在可重復(fù)讀隔離級(jí)別下才會(huì)產(chǎn)生間隙鎖。在其他隔離級(jí)別下，如讀提交（Read Committed）隔離級(jí)別，MySQL可能會(huì)使用臨時(shí)的意向鎖來(lái)避免并發(fā)問(wèn)題，而不是生成真正的間隙鎖。

為什么這里強(qiáng)調(diào)的是普通索引呢？因?yàn)閷?duì)唯一索引鎖定并不會(huì)觸發(fā)間隙鎖，請(qǐng)看下面這個(gè)例子：

假設(shè)我們有一個(gè)名為students的表，其中有兩個(gè)字段：id 和 name。id是主鍵，現(xiàn)在有兩個(gè)事務(wù)同時(shí)進(jìn)行操作：

事務(wù)A執(zhí)行以下語(yǔ)句：

SELECT * FROM students WHERE id = 1 FOR UPDATE;

事務(wù)B執(zhí)行以下語(yǔ)句：

INSERT INTO students (id, name) VALUES (2, 'John');

由于事務(wù)A使用了唯一索引鎖定，它會(huì)鎖定id為1的記錄，不會(huì)觸發(fā)間隙鎖。同時(shí)，在事務(wù)B中插入id為2的記錄也不會(huì)受到影響。這是因?yàn)槲ㄒ凰饕粫?huì)鎖定匹配條件的具體記錄，而不會(huì)鎖定不存在的記錄（如間隙）。

當(dāng)使用唯一索引鎖定一條存在的記錄時(shí)，會(huì)使用記錄鎖，而不是間隙鎖

但是當(dāng)搜索條件僅涉及到多列唯一索引的一部分列時(shí)，可能會(huì)產(chǎn)生間隙鎖。以下是一個(gè)例子：

假設(shè)students表，包含三個(gè)列：id、name和age。我們?cè)?name, age)上創(chuàng)建了一個(gè)唯一索引。

現(xiàn)在有兩個(gè)事務(wù)同時(shí)進(jìn)行操作：

事務(wù)A執(zhí)行以下語(yǔ)句：

SELECT * FROM students WHERE name = 'John' FOR UPDATE;

事務(wù)B執(zhí)行以下語(yǔ)句：

INSERT INTO students (id, name, age) VALUES (2, 'John', 25);

在這種情況下，事務(wù)A搜索的條件只涉及到了唯一索引的一部分列（name），而沒(méi)有涉及到完整的索引列（name, age）。因此，MySQL會(huì)對(duì)匹配的記錄加上行鎖，并且還會(huì)對(duì)與該條件范圍相鄰的間隙加上間隙鎖。

間隙鎖加鎖規(guī)則

間隙鎖有以下加鎖規(guī)則：

規(guī)則1：加鎖的基本單位是 Next-Key Lock，左開(kāi)右閉區(qū)間。
規(guī)則2：查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。
規(guī)則3：唯一索引上的范圍查詢會(huì)上鎖到不滿足條件的第一個(gè)值為止。
規(guī)則4：唯一索引等值查詢，并且記錄存在，Next-Key Lock 退化為行鎖。
規(guī)則5：索引上的等值查詢，會(huì)將距離最近的左邊界和右邊界作為鎖定范圍，如果索引不是唯一索引還會(huì)繼續(xù)向右匹配，直到遇見(jiàn)第一個(gè)不滿足條件的值，如果最后一個(gè)值不等于查詢條件，Next-Key Lock 退化為間隙鎖。

記住上述這些規(guī)則，這些規(guī)則不太好理解，我們下面通過(guò)案例來(lái)講解。

案例演示

環(huán)境：MySQL，InnoDB，RR隔離級(jí)別。

數(shù)據(jù)表：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
   KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

數(shù)據(jù)：

id	age	name
1	1	小明
5	5	小王
7	7	小張
11	11	小陳

在進(jìn)行測(cè)試之前，我們先來(lái)看看 user 表中存在的隱藏間隙：

(-∞, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +∞]

案例一：唯一索引等值鎖定存在的數(shù)據(jù)

如下是事務(wù)A和事務(wù)B執(zhí)行的順序：

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	select * from user where id = 5 for update
T3		insert into user value(3,3,"小黑") ---不阻塞
T4		insert into user value(6,6,"小藍(lán)") ---不阻塞
T5	commit	commit

根據(jù)規(guī)則4，加的是記錄鎖，不會(huì)使用間隙鎖，所以只會(huì)鎖定 5 這一行記錄。

案例二：索引等值鎖定

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	select * from user where id = 3 for update --- 不存在的數(shù)據(jù)
T3		insert into user value(6,6,"小藍(lán)") --- 不阻塞
T4		insert into user value(2,2,"小黃") --- 阻塞
T5	commit

這是一個(gè)索引等值查詢，根據(jù)規(guī)則1和規(guī)則5，加鎖范圍是（ 1，5 ] ，又由于向右遍歷時(shí)最后一個(gè)值 5 不滿足查詢需求，Next-Key Lock 退化為間隙鎖。也就是最終鎖定范圍區(qū)間是（ 1，5 ）。

案例三：唯一索引范圍鎖定

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	select * from user where id >= 5 and id<6 for update
T3		insert into user value(7,7,"小趙") --- 阻塞
T4	commit

根據(jù)規(guī)則3，會(huì)上鎖到不滿足條件的第一個(gè)值為止，也就是7，所以最終加鎖范圍是 [ 5，7 ]。

其實(shí)這里可以分為兩個(gè)步驟，第一次用 id=5 定位記錄的時(shí)候，其實(shí)加上了間隙鎖（ 1，5 ]，又因?yàn)槭俏ㄒ凰饕戎挡樵儯酝嘶癁榱诵墟i，只鎖定 5。

第二次用 id<6 定位記錄的時(shí)候，其實(shí)加上了間隙鎖（ 5，7 ]，所以最終合起來(lái)鎖定區(qū)間是 [ 5，7 ]。

案例四：非唯一索引范圍鎖定

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	select * from user where age >= 5 and age<6 for update
T3		insert into user value(8,8,"小青") --- 不阻塞
T4		insert into user value(2,2,"小黃") --- 阻塞
T5	commit

參考上面那個(gè)例子。

第一次用 age =5 定位記錄的時(shí)候，加上了間隙鎖（ 1，5 ]，不是唯一索引，所以不會(huì)退化為行鎖，根據(jù)規(guī)則5，會(huì)繼續(xù)向右匹配，所以最終合起來(lái)鎖定區(qū)間是（ 1，7 ]。

案例五：間隙鎖死鎖

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	select * from user where id = 3 for update
T3		select * from user where id = 4 for update
T4		insert into user value(2,2,"小黃") --- 阻塞
T5	insert into user value(4,4,"小紫") --- 阻塞

間隙鎖之間不是互斥的，如果一個(gè)事務(wù)A獲取到了（ 1,5 ] 之間的間隙鎖，另一個(gè)事務(wù)B仍然可以獲取到（ 1,5 ] 之間的間隙鎖。這時(shí)就可能會(huì)發(fā)生死鎖問(wèn)題。

在事務(wù)A事務(wù)提交，間隙鎖釋放之前，事務(wù)B也獲取到了間隙鎖（ 1,5 ] ，這時(shí)兩個(gè)事務(wù)就處于死鎖狀態(tài)。

案例六：limit對(duì)加鎖的影響

時(shí)刻	事務(wù)A	事務(wù)B
T1	begin	begin
T2	deletet user where age = 6 limt 1
T3		insert into user value(7,7,"小趙") --- 不阻塞
T4
T5	commit	commit