MySQL InnoDB行锁与表锁的加锁规则

InnoDB 存储引擎支持行锁和表锁，但为了保证高并发下的数据一致性，其加锁逻辑非常复杂。理解这些规则，能帮助你快速定位死锁原因或优化锁等待。

一、 理解基础锁模式与兼容性

在深入具体规则前，先弄清楚两种核心锁类型及其兼容关系。如果不理解兼容性，就无法判断多线程场景下谁会被阻塞。

1. 锁的类型

• 共享锁：通常由 SELECT ... FOR SHARE（旧版为 LOCK IN SHARE MODE）产生，允许其他事务也加 S 锁，但阻止 X 锁。
• 排他锁：通常由 UPDATE、DELETE、INSERT 或 SELECT ... FOR UPDATE 产生，禁止其他事务加任何锁（S 或 X）。
• 意向锁：这是表级锁。当事务想在表中对某行加 S 锁时，会先在表上加 IS 锁；想加 X 锁时，先加 IX 锁。它们的作用是快速判断表中是否已有行被锁定，而无需扫描每一行。

2. 锁兼容矩阵

下表展示了不同锁模式之间的兼容关系（Y 表示兼容，N 表示阻塞）。

二、 判定加锁类型的流程

InnoDB 的加锁并非固定不变，它取决于你的 SQL 语句、索引类型以及当前数据的隔离级别（默认为 REPEATABLE READ）。我们可以通过以下流程图快速判定一条 SQL 语句大致的加锁范围。

三、 实操加锁规则详解

以下步骤将结合具体 SQL 案例，详细拆解不同场景下的加锁逻辑。假设表结构为 t(id PK, k KEY, a)，其中 id 为主键，k 为普通二级索引。

1. 主键/唯一索引的等值查询

当查询条件命中主键或唯一索引的唯一值时，加锁最为精准。

1. 执行 以下语句查询不存在的数据：

SELECT * FROM t WHERE id = 1 FOR UPDATE;

假设 id=1 不存在。
2. 观察 加锁情况。
InnoDB 会对该索引的“空隙”加锁，防止其他事务插入 id=1 的数据。这被称为 间隙锁。

3. 执行 以下语句查询存在的数据：

SELECT * FROM t WHERE id = 10 FOR UPDATE;

假设 id=10 存在。
4. 确认 加锁结果。
系统仅对 id=10 这一行的索引记录加 记录锁，不会影响前后数据的插入。

2. 非唯一索引的等值查询

当使用普通二级索引（如字段 k）进行查询时，即使只命中一行，锁的范围也会扩大。

1. 执行 以下语句：

SELECT * FROM t WHERE k = 10 FOR UPDATE;

假设存在多条 k=10 的记录，或者我们只关心其中一条。
2. 分析 加锁逻辑。
InnoDB 不仅会锁定 k=10 的索引记录，还会锁定其前后的间隙，并且会 回溯 到主键索引，对对应的主键记录也加锁。
这实际上是 Next-Key Lock（临键锁），即：记录锁 + 间隙锁。
公式表示为：
$$ Next-Key Lock = (Gap Lock) + (Record Lock) $$

3. 范围查询

无论使用主键、唯一索引还是普通索引，只要查询条件是范围（如 >、<、BETWEEN），InnoDB 都会扫描范围内的所有索引，并使用 Next-Key Lock。

1. 执行 范围更新语句：

UPDATE t SET a = a + 1 WHERE id > 10 AND id < 20;

2. 理解 锁定范围。
InnoDB 会锁定所有 (10, 20) 之间的记录，同时也会锁住“ supremum pseudo-record ”（最大值伪记录），防止其他事务在 id=20 之后插入新数据干扰当前视图。这会导致 id=20 及其之后的插入操作被阻塞。

4. 无索引查询与表锁效应

如果 WHERE 条件没有命中任何索引，InnoDB 只能进行全表扫描。

1. 执行 无索引查询：

SELECT * FROM t WHERE a = 10 FOR UPDATE;

假设字段 a 没有任何索引。
2. 后果 分析。
InnoDB 必须扫描表中的每一行来检查 a=10。在这个过程中，它会为扫描到的每一行都加上行级排他锁（X Lock）。虽然从技术角度讲是行锁，但由于锁住了所有行，效果上等同于 表锁。这将导致所有其他事务的写入操作被完全阻塞。

四、 验证与排查

在生产环境中，不要猜测锁的机制，要查看实际状态。

1. 开启锁监控

1. 执行 以下 SQL 开启 InnoDB 锁监控：

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

2. 查看锁等待信息

1. 在发生锁等待时，执行以下命令查看引擎状态：

SHOW ENGINE INNODB STATUS\G

2. 定位 输出中的 TRANSACTIONS 章节。
3. 查找 ---TRANSACTION XXXX 部分。
你会在日志中看到类似以下的字样：
RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of tabletest.ttrx id XXXX lock_mode X locks rec but not gap

• lock_mode X：表示排他锁。
• locks rec but not gap：表示仅记录锁，无间隙锁。
• locks gap before rec：表示纯间隙锁。
• Next-Key Lock：通常显示为 locks rec but not gap 与间隙的组合状态。

4. 根据 输出的具体信息，对比上述规则，判断是否是因为索引缺失导致锁范围过大，或者是因为范围查询导致了意外的间隙锁。