MySQL间隙锁在查询时锁定的范围

基础环境设置

CREATE TABLE test_gap (
    id INT PRIMARY KEY,
    value INT,
    name VARCHAR(50),
    INDEX idx_value (value)
) ENGINE=InnoDB;

INSERT INTO test_gap VALUES
(1, 10, 'A'),
(3, 20, 'B'), 
(5, 30, 'C'),
(7, 40, 'D'),
(9, 50, 'E');

一、等值查询的间隙锁

1.1 查询不存在的值

查询SQL	锁定范围	说明
SELECT * FROM test_gap WHERE value = 15 FOR UPDATE;	(10, 20)	15位于10和20之间
SELECT * FROM test_gap WHERE value = 25 FOR UPDATE;	(20, 30)	25位于20和30之间
SELECT * FROM test_gap WHERE value = 5 FOR UPDATE;	(-∞, 10)	5小于最小值10
SELECT * FROM test_gap WHERE value = 60 FOR UPDATE;	(50, +∞)	60大于最大值50

1.2 查询存在的值

查询SQL	锁类型	说明
SELECT * FROM test_gap WHERE value = 20 FOR UPDATE;	临键锁	记录锁+前后间隙锁
SELECT * FROM test_gap WHERE id = 3 FOR UPDATE;	记录锁	主键查询，无间隙锁

二、范围查询的间隙锁

2.1 闭区间范围查询

查询SQL	具体锁定区间
WHERE value >= 20 AND value <= 40	(10, 20] + (20, 30] + (30, 40] + (40, 50)
WHERE value >= 15 AND value <= 35	(10, 20] + (20, 30] + (30, 40)
WHERE value >= 25 AND value <= 45	(20, 30] + (30, 40] + (40, 50)

2.2 开区间范围查询

查询SQL	具体锁定区间
WHERE value > 20 AND value < 40	(20, 30] + (30, 40)
WHERE value > 15 AND value < 35	(10, 20] + (20, 30] + (30, 40)
WHERE value > 25 AND value < 45	(20, 30] + (30, 40] + (40, 50)

2.3 单边界范围查询

查询SQL	锁定范围
WHERE value >= 30	(20, 30] + (30, 40] + (40, 50] + (50, +∞)
WHERE value > 30	(30, 40] + (40, 50] + (50, +∞)
WHERE value <= 30	(-∞, 10] + (10, 20] + (20, 30] + (30, 40)
WHERE value < 30	(-∞, 10] + (10, 20] + (20, 30)

三、特殊情况的间隙锁

3.1 空表的间隙锁

查询SQL	锁定范围	说明
SELECT * FROM empty_table WHERE value = 100 FOR UPDATE;	(-∞, +∞)	整个值域间隙
SELECT * FROM empty_table WHERE value > 50 FOR UPDATE;	(-∞, +∞)	整个值域间隙

3.2 只有一条记录的表

-- 假设只有一条记录：value = 100

查询SQL	锁定范围	说明
WHERE value = 50	(-∞, 100)	50不存在，锁定前间隙
WHERE value = 150	(100, +∞)	150不存在，锁定后间隙
WHERE value = 100	(-∞, 100] + (100, +∞)	存在，临键锁覆盖所有间隙

四、锁定范围的边界规则总结

规则	说明	示例
记录存在	加临键锁 = 记录锁 + 间隙锁	value=20 → (10,20] + (20,30)
记录不存在	只加间隙锁	value=25 → (20,30)
范围查询	锁定所有可能插入影响结果的间隙	value>20 → (20,30] + (30,40] + ...
边界保护	查询边界外也要锁定相邻间隙	20≤value≤40 → 还要锁(40,50)