MYSQL--再谈间隙锁和临键锁
✅ 表结构和数据如下：
CREATE TABLE users (id INT PRIMARY KEY,score INT,INDEX idx_score(score) );
id score 10 100 20 200 30 300 40 400 50 500 60 600 70 700 80 800 90 900
🔒 加锁行为分析表（总览）
# SQL 使用索引 匹配 锁类型 锁定范围（主键/score） 1 id = 30主键 命中 记录锁 [30]2 id = 35主键 无匹配 间隙锁 (30, 40)3 score = 300二级索引 命中 记录锁 + 回表 [300]（idx_score），id=30（主键）4 score = 350二级索引 无匹配 间隙锁 (300, 400)5 id > 30 AND id < 60主键 命中 临键锁 (30,40),[40,50),(50,60)6 id > 35 AND id < 60主键 命中 临键锁 (30,40),[40,50],[50,60)7 score > 300 AND score < 600二级索引 命中 临键锁 + 回表 (300,400),[400,500),[500,600)8 score > 350 AND score < 600二级索引 命中 临键锁 + 回表 (300,400),[400,500),[500,600)9 id > 30主键 命中 临键锁 (30,40),[40,50),[50,60),[60,70),[70,80),[80,90),[90,+∞)10 id > 35主键 命中 临键锁 (30,40),[40,50),[50,60),[60,70),[70,80),[80,90),[90,+∞)11 score > 300二级索引 命中 临键锁 + 回表 (300,400],(400,500],(500,600],(600,700],(700,800],(800,900],(900,+∞)12 score > 350二级索引 命中 临键锁 + 回表 (300,400],(400,500],(500,600],(600,700],(700,800],(800,900],(900,+∞)
📘 每条 SQL 详解（带图形）
🔹 1.
SELECT * FROM users WHERE id = 30 FOR UPDATE;
✅ 主键等值查询，唯一索引 → 只加记录锁
✅ 锁住
[30]
🔹 2.
SELECT * FROM users WHERE id = 35 FOR UPDATE;
🔍 主键范围查找，未命中 → 加间隙锁
✅ 锁住间隙
(30, 40)，防止插入 35
🔹 3.
SELECT * FROM users WHERE score = 300 FOR UPDATE;
🔍 二级索引命中 → 加记录锁（score=300）+ 回表锁（id=30）
✅ 锁住：
[300]（在idx_score上）
[30]（主键）
🔹 4.
SELECT * FROM users WHERE score = 350 FOR UPDATE;
🔍 二级索引未命中 → 加间隙锁
✅ 锁住
(300, 400)（在idx_score上）
🔹 5.
SELECT * FROM users WHERE id > 30 AND id < 60 FOR UPDATE;
🔍 主键范围 → 临键锁（Next-Key）
✅ 锁住：
(30,40)-->是间隙锁
[40,50)-->是临键锁{就相当于间隙锁（40,50）+行锁[40]}
[50,60)-->是临键锁{就相当于间隙锁（50,60）+行锁[50]}
🔹 6.
SELECT * FROM users WHERE id > 35 AND id < 60 FOR UPDATE;
🔍 主键范围（不命中30，但间隙要锁）→ 临键锁
✅ 锁住：
(30,40)（间隙）
[40,50)
[50,60)
🔹 7.
SELECT * FROM users WHERE score > 300 AND score < 600 FOR UPDATE;
🔍 二级索引范围查询 → 临键锁
✅ 锁住：
(300, 400)
[400, 500)
[500, 600)（只锁 gap）加主键回表锁：id=40, 50
🔹 8.
SELECT * FROM users WHERE score > 350 AND score < 600 FOR UPDATE;
🔍 二级索引范围查询 → 临键锁
✅ 锁住：
(300, 400)（350 落在这里）
[400, 500)
[500, 600)加主键回表锁：id=40, 50
🔹 9.
SELECT * FROM users WHERE id > 30 FOR UPDATE;
🔍 主键范围 → 临键锁
✅ 锁住全部后继区间：
(30,40),[40,50),[50,60),[60,70),[70,80),[80,90),[90,+∞)
🔹 10.
SELECT * FROM users WHERE id > 35 FOR UPDATE;
🔍 主键范围 → 临键锁
✅ 锁住：
(30,40)（间隙锁，35 落在这里）
[40,50),[50,60),[60,70),[70,80),[80,90),[90,+∞)
🔹 11.
SELECT * FROM users WHERE score > 300 FOR UPDATE;
🔍 二级索引范围 → 临键锁 + 回表
✅ 锁住：
(300,400),[400,500),[500,600),[600,700),[700,800),[800,900),[900,+∞)回表主键加锁：id=40, 50, 60, 70, 80, 90
🔹 12.
SELECT * FROM users WHERE score > 350 FOR UPDATE;
🔍 二级索引范围 → 临键锁 + 回表
✅ 锁住：
(300,400)（350 落在这里）
[400,500),[500,600),[600,700),[700,800),[800,900),[900,+∞)回表主键加锁：id=40, 50, 60, 70, 80, 90
🧠 Tips 小结
锁类型 特征 记录锁 精确匹配唯一值（主键或唯一索引） 间隙锁 精确匹配失败 + 非唯一索引或主键 → 锁住相邻 gap 临键锁（Next-Key Lock） 范围查询 → 锁住记录 + 相邻间隙（组合锁）