腳本之家服務器常用軟件

快捷導航

六個案例搞懂mysql間隙鎖

更新時間：2025年06月11日 10:52:44 作者：碼農(nóng)BookSea

MySQL中的間隙是指索引中兩個索引鍵之間的空間,間隙鎖用于防止范圍查詢期間的幻讀,本文主要介紹了六個案例搞懂mysql間隙鎖,具有一定的參考價值,感興趣的可以了解一下

概念解釋
間隙鎖詳解
間隙鎖觸發(fā)條件
間隙鎖加鎖規(guī)則
案例演示
案例一：唯一索引等值鎖定存在的數(shù)據(jù)
案例二：索引等值鎖定
案例三：唯一索引范圍鎖定
案例四：非唯一索引范圍鎖定
案例五：間隙鎖死鎖
案例六：limit對加鎖的影響
總結(jié)

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

概念解釋

記錄鎖（Record Lock）

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

比如：

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

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

間隙鎖（Gap Lock）

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

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

臨鍵鎖（Next-Key Lock）

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

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

間隙鎖詳解

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

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

舉個簡單例子來說明：

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

事務A執(zhí)行以下語句：

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

事務B執(zhí)行以下語句：

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

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

間隙鎖觸發(fā)條件

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

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

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

為什么這里強調(diào)的是普通索引呢？因為對唯一索引鎖定并不會觸發(fā)間隙鎖，請看下面這個例子：

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

事務A執(zhí)行以下語句：

SELECT * FROM students WHERE id = 1 FOR UPDATE;

事務B執(zhí)行以下語句：

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

由于事務A使用了唯一索引鎖定，它會鎖定id為1的記錄，不會觸發(fā)間隙鎖。同時，在事務B中插入id為2的記錄也不會受到影響。這是因為唯一索引只會鎖定匹配條件的具體記錄，而不會鎖定不存在的記錄（如間隙）。

當使用唯一索引鎖定一條存在的記錄時，會使用記錄鎖，而不是間隙鎖

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

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

現(xiàn)在有兩個事務同時進行操作：

事務A執(zhí)行以下語句：

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

事務B執(zhí)行以下語句：

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

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

間隙鎖加鎖規(guī)則

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

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

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

案例演示

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

數(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	小陳

在進行測試之前，我們先來看看 user 表中存在的隱藏間隙：

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

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

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

時刻	事務A	事務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,“小藍”) —不阻塞
T5	commit	commit

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

案例二：索引等值鎖定

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

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

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

時刻	事務A	事務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，會上鎖到不滿足條件的第一個值為止，也就是7，所以最終加鎖范圍是 [ 5，7 ]。

其實這里可以分為兩個步驟，第一次用 id=5 定位記錄的時候，其實加上了間隙鎖（ 1，5 ]，又因為是唯一索引等值查詢，所以退化為了行鎖，只鎖定 5。

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

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

時刻	事務A	事務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

參考上面那個例子。

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

案例五：間隙鎖死鎖

時刻	事務A	事務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,“小紫”) — 阻塞