Skip to content

InnoDB 加锁规则

一、 引言：为何只改一行，却锁了这么多？

在 MySQL 的 InnoDB 存储引擎和可重复读 (Repeatable Read, RR) 隔离级别下，为了解决幻读问题，引入了间隙锁 (Gap Lock) 和 Next-Key Lock。这导致即使是针对单行的操作，实际加锁的范围也可能远超预期，影响并发性能并可能引发死锁。理解其加锁规则至关重要。

(注：以下规则基于 MySQL 5.7.24 及 8.0.13 之前的版本，默认隔离级别为可重复读)

二、 InnoDB 加锁核心规则 (RR 级别)

包含 两个原则、两个优化、一个 Bug：

  1. 原则 1：加锁基本单位是 Next-Key Lock

    • Next-Key Lock 是前开后闭区间，由其覆盖范围内的行锁 + 该行之前的间隙锁组成。
    • 例如，索引上有值 5, 10, 15，则 Next-Key Lock 可能包括 (..., 5], (5, 10], (10, 15], (15, +supremum]。

  2. 原则 2：查找过程中访问到的对象才会加锁

    • 只有在索引扫描过程中实际“访问”到的记录或间隙才会被加锁。
    • 对于覆盖索引查询 (SELECT 字段都在索引中)，如果不需要回表查主键索引，则主键索引上通常不会加锁 (但 FOR UPDATE 例外)。

  3. 优化 1：唯一索引等值查询 -> 行锁优化

    • 在唯一索引（包括主键索引）上进行等值查询，若记录存在，Next-Key Lock 会退化为行锁，只锁定该行本身。

  4. 优化 2：等值查询向右遍历 -> 间隙锁优化

    • 在索引上进行等值查询，当向右遍历（查找下一个值）且最后一个访问到的值不满足等值条件时，该值对应的 Next-Key Lock 会退化为间隙锁（只锁间隙，不锁该行）。

  5. Bug 1：唯一索引范围查询 -> 额外锁定

    • 在唯一索引上进行范围查询时，InnoDB 会扫描到第一个不满足范围条件的记录为止，并对这个记录所在的 Next-Key Lock 也进行加锁。这通常是不必要的，会锁住额外的范围。

三、 案例解析精要

  • 案例一 (等值查不存在记录): WHERE id=7 (id 为主键)。加 (5, 10] Next-Key Lock，根据优化 2 退化为 (5, 10) 间隙锁。阻塞 id=8 的插入，不阻塞 id=10 的更新。
  • 案例二 (非唯一索引等值查): WHERE c=5 LOCK IN SHARE MODE (c 为普通索引，使用覆盖索引)。
    • 加 (0, 5] Next-Key Lock。
    • 向右遍历到 c=10，加 (5, 10] Next-Key Lock。
    • 根据优化 2，c=10 不满足条件，(5, 10] 退化为 (5, 10) 间隙锁。
    • 锁范围: 索引 c 上的 (0, 5] Next-Key Lock 和 (5, 10) 间隙锁。
    • 关键: 由于是覆盖索引且 LOCK IN SHARE MODE，主键索引上无锁，其他事务可更新 id=5 或 id=10 的非 c 字段。但插入 c=7 会被间隙锁阻塞。
    • 注意: 若是 FOR UPDATE，即使是覆盖索引，也会给主键索引上满足条件的行加行锁。
  • 案例三 (主键范围查): WHERE id>=10 AND id<11 (等价于 id=10)。
    • 定位 id=10：本应加 (5, 10] Next-Key Lock，根据优化 1 退化为 id=10 的行锁。
    • 向右范围查找：找到 id=15 停止，加 (10, 15] Next-Key Lock。
    • 锁范围: 主键索引上的 id=10 行锁 + (10, 15] Next-Key Lock。
  • 案例四 (非唯一索引范围查): WHERE c>=10 AND c<11。
    • 定位 c=10：加 (5, 10] Next-Key Lock (非唯一索引无优化 1)。
    • 向右范围查找：找到 c=15 停止，加 (10, 15] Next-Key Lock。
    • 锁范围: 索引 c 上的 (5, 10] 和 (10, 15] 两个 Next-Key Lock。
  • 案例五 (唯一索引范围查 Bug): WHERE id>=10 AND id<20。
    • 预期：锁 id=10 行锁 + (10, 15] Next-Key Lock + (15, 20) 间隙锁 (因 id=20 不满足 <20)。
    • 实际 (Bug)：扫描到第一个不满足条件 id=20，并对其加锁。
    • 锁范围: id=10 行锁 + (10, 15] Next-Key Lock + (15, 20] Next-Key Lock (额外锁定了 id=20 这一行)。
  • 案例六/七 (DELETE 与 LIMIT): DELETE FROM t WHERE c=10 (假设有 (c=10,id=10) 和 (c=10,id=30) 两行)。
    • 无 LIMIT：加 (c=5,id=5) -> (c=10,id=10] Next-Key Lock，继续扫描到 (c=15,id=15)，根据优化 2，加 (c=10,id=10) -> (c=15,id=15) 间隙锁。
    • 有 LIMIT 2：扫描到 (c=10,id=30) 后满足 2 条记录，停止扫描。只加 (c=5,id=5) -> (c=10,id=30] 的 Next-Key Lock 范围。LIMIT 可以有效减少加锁范围。
  • 案例八 (死锁): 揭示 Next-Key Lock 的两阶段获取：先获取间隙锁，再获取行锁。两个事务可能互相持有对方需要的行锁前置的间隙锁，然后请求行锁时发生死锁。

四、 读提交 (Read Committed, RC) 隔离级别的差异

  • 无间隙锁 (通常): RC 级别下基本不使用间隙锁（外键约束等少数情况除外）。
  • 锁范围更小： 主要使用行锁。
  • 锁时间更短： 对于不满足查询条件的行，其行锁在语句执行完成后即释放，无需等到事务提交（称为 semi-consistent read）。
  • 优点： 并发度更高，死锁概率降低。
  • 缺点： 存在不可重复读和幻读。必须配合 binlog_format=ROW 才能保证主备数据一致性。

五、 总结与建议

  1. 理解代价： RR 级别通过 Next-Key Lock 解决幻读，但牺牲了并发性，增加了死锁风险。
  2. 分析工具： 深入理解加锁规则有助于分析和解决锁等待、死锁问题。
  3. 实践指导：
    • 在 DELETE 或 UPDATE 时尽量使用 LIMIT 明确条数，减少不必要的锁范围。
    • 理解覆盖索引在 LOCK IN SHARE MODE 和 FOR UPDATE 下加锁行为的差异。
    • 警惕唯一索引范围查询的 Bug 可能锁住更多行。
  4. 隔离级别选择：
    • 如果业务能接受不可重复读和幻读，且需要高并发，RC + ROW Binlog 是常用选择。
    • 如果业务强依赖可重复读的保证，则必须使用 RR，并仔细设计 SQL 以管理锁带来的影响。