logo
简体中文

导航菜单

MySQL 面试精讲

行锁、间隙锁、临键锁

🔴 困难

题目描述

解释 InnoDB 的行锁、间隙锁和临键锁的区别。以下 SQL 会加什么锁？

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    KEY idx_age (age)
);

-- 数据
INSERT INTO users VALUES
(1, 'Alice', 18),
(5, 'Bob', 20),
(10, 'Charlie', 25);

-- 查询 1
SELECT * FROM users WHERE id = 5 FOR UPDATE;

-- 查询 2
SELECT * FROM users WHERE age = 20 FOR UPDATE;

-- 查询 3
SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;

提示

  • 行锁：锁定单行记录
  • 间隙锁：锁定记录之间的间隙
  • 临键锁：行锁 + 间隙锁

解法

参考答案 (3 个标签)
行锁 间隙锁 临键锁

1. 行锁（Record Lock）

-- 锁定单行记录
SELECT * FROM users WHERE id = 5 FOR UPDATE;

-- 加锁：
-- Record Lock on id=5

-- 影响：
-- ✓ 可以插入 id=1, id=10
-- ✗ 不能修改 id=5
-- ✗ 其他事务不能锁定 id=5

特点：

  • 锁定索引记录
  • 只锁一行，并发度高
  • 主键或唯一索引等值查询

2. 间隙锁（Gap Lock）

-- 锁定记录之间的间隙
SELECT * FROM users WHERE age = 20 FOR UPDATE;

-- 数据：
-- (1, 'Alice', 18)
-- (5, 'Bob', 20)
-- (10, 'Charlie', 25)

-- 加锁：
-- Gap Lock: (18, 20)
-- Gap Lock: (20, 25)

-- 影响：
-- ✓ 可以修改 age=20 的记录
-- ✗ 不能插入 age=19, 21, 22, 23, 24
-- ✓ 可以插入 age=18, 25

特点：

  • 锁定索引记录之间的间隙
  • 不锁定记录本身
  • 防止幻读（防止插入新记录）

3. 临键锁（Next-Key Lock）

-- 行锁 + 间隙锁
SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;

-- 数据：
-- (1, 'Alice', 18)
-- (5, 'Bob', 20)
-- (10, 'Charlie', 25)

-- 加锁：
-- Next-Key Lock: (18, 20]  -- 间隙 + 记录
-- Next-Key Lock: (20, 25]  -- 间隙 + 记录

-- 影响：
-- ✗ 不能插入 age=19, 20, 21, 22, 23, 24
-- ✗ 不能修改 age=20
-- ✓ 可以插入 age=18, 25

特点：

  • 默认锁类型（REPEATABLE READ）
  • 行锁 + 间隙锁
  • 防止幻读

各查询分析

查询 1：主键等值查询

SELECT * FROM users WHERE id = 5 FOR UPDATE;

-- 加锁：
-- Record Lock on id=5

-- 原因：主键唯一，只需锁定该记录

查询 2：辅助索引等值查询

SELECT * FROM users WHERE age = 20 FOR UPDATE;

-- 加锁：
-- Next-Key Lock: (18, 20]
-- Gap Lock: (20, 25)

-- 原因：
-- 1. age=20 存在，加 Next-Key Lock (18, 20]
-- 2. 优化后，右边的间隙降级为 Gap Lock (20, 25)

-- 影响：
-- ✗ 不能插入 age=19, 21, 22, 23, 24
-- ✗ 不能修改 age=20 的记录

查询 3：辅助索引范围查询

SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;

-- 加锁：
-- Next-Key Lock: (18, 20]
-- Next-Key Lock: (20, 25]

-- 原因：范围查询，使用 Next-Key Lock

-- 影响：
-- ✗ 不能插入 age=19, 20, 21, 22, 23, 24
-- ✗ 不能修改 age=20 的记录

扩展：锁的兼容性

锁兼容矩阵

锁类型Record LockGap LockNext-Key Lock
Record LockX↔X 冲突Gap↔Gap 兼容X↔X 冲突
Gap LockGap↔Gap 兼容Gap↔Gap 兼容Gap↔Gap 兼容
Next-Key LockX↔X 冲突Gap↔Gap 兼容X↔X 冲突

注意：

  • Gap Lock 只与其他 Gap Lock 冲突
  • Gap Lock 不与 Record Lock 冲突
  • Gap Lock 的目的是防止插入，不是防止读取

隔离级别的影响

隔离级别Gap LockNext-Key Lock
READ COMMITTED✗✗
REPEATABLE READ✓✓
-- READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;
-- 只加 Record Lock，不加 Gap Lock
-- 可能出现幻读

-- REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;
-- 加 Next-Key Lock
-- 防止幻读

死锁案例

案例 1：不同顺序加锁

-- 事务 A
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 锁 id=1
SELECT * FROM users WHERE id = 5 FOR UPDATE;  -- 等待 id=5

-- 事务 B
START TRANSACTION;
SELECT * FROM users WHERE id = 5 FOR UPDATE;  -- 锁 id=5
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 等待 id=1

-- 死锁！

案例 2：间隙锁冲突

-- 事务 A
START TRANSACTION;
SELECT * FROM users WHERE age > 18 AND age < 25 FOR UPDATE;
-- Gap Lock: (18, 25]

-- 事务 B
START TRANSACTION;
INSERT INTO users (id, name, age) VALUES (2, 'David', 20);
-- 等待 Gap Lock

-- 事务 A
INSERT INTO users (id, name, age) VALUES (3, 'Eve', 19);
-- 等待自己的 Gap Lock

-- 死锁！

如何避免死锁

1. 固定加锁顺序

-- ❌ 不同顺序
-- A: 1 → 5
-- B: 5 → 1

-- ✓ 固定顺序
-- A: 1 → 5
-- B: 1 → 5

2. 使用较低隔离级别

-- READ COMMITTED 减少 Gap Lock
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 减少锁持有时间

-- ❌ 长事务
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 执行业务逻辑（耗时）
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- ✓ 短事务
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 立即提交

4. 添加合理索引

-- ✗ 索引失效，升级为表锁
SELECT * FROM users WHERE YEAR(created_at) = 2024 FOR UPDATE;

-- ✓ 使用索引
SELECT * FROM users WHERE created_at >= '2024-01-01' FOR UPDATE;

搜索