基础环境设置
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 只有一条记录的表
| 查询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) |