Skip to content

用动态的观点看加锁

一、复习InnoDB加锁规则

  1. 两个原则：
    • 原则1： 加锁的基本单位是 next-key lock（前开后闭区间，如 (a, b]）。
    • 原则2： 查找过程中访问到的对象才会加锁。
  2. 两个优化：
    • 优化1： 索引上的等值查询，给唯一索引加锁时，next-key lock 退化为行锁。
    • 优化2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock 退化为间隙锁。

  3. 一个bug：

    • 唯一索引上的范围查询会访问到不满足条件的第一个值为止（并可能对其加锁）。

  4. 实验表结构： sql CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; INSERT INTO t VALUES(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

二、不等号条件里的“等值查询”与加锁分析 (动态视角)

  1. 问题场景： sql BEGIN; SELECT * FROM t WHERE id > 9 AND id < 12 ORDER BY id DESC FOR UPDATE;
  2. 加锁范围分析： 主键索引上的 (0,5], (5,10], 和 (10,15)（间隙锁）。id=15 未加行锁。
  3. “等值查询”的来源：
    • ORDER BY id DESC 要求优化器先找到“第一个 id < 12 的值”。
    • 引擎内部通过索引树搜索定位记录时，本质上是尝试找到 id=12 这个值（即使最终没找到）。这个定位过程使用的是“等值查询”的方法。
    • 在本例中，定位到 id=12 时，发现它不存在，实际定位到的是 (10,15) 这个间隙。由于 id=15 不满足 id<12，根据优化2，next-key lock (10,15] 退化为间隙锁 (10,15)。
    • 之后向左遍历：
      • 扫描到 id=10，满足条件，加 next-key lock (5,10]。
      • 扫描到 id=5，不满足 id>9，但这是向左遍历访问到的最后一个满足 id<12 的记录的上一个记录，根据原则1（基本单位是next-key lock），其对应的区间 (0,5] 也会被考虑。在这个例子中，因为 id=5 不满足 id>9，所以 id=5 本身不会被锁定。但如果查询是 id < 12，则 (0,5] 会被锁。 (此处原文例子简化了，实际向左遍历到第一个不满足id > 9的id=5时，由于id=10是满足条件的，所以(5,10]会被锁。) 更正理解：因为是id>9 AND id<12，向左遍历时，id=10满足，锁(5,10]。再向左id=5不满足id>9，停止。
      • 核心： 加锁是动态的，在语句执行过程中发生。

三、IN 子句的等值查询与死锁分析

  1. 问题场景1 (Share Mode)： sql BEGIN; SELECT id FROM t WHERE c IN (5, 20, 10) LOCK IN SHARE MODE;
  2. Explain结果： 使用索引 c，rows=3，说明三个值都是通过B+树搜索定位。
  3. 加锁过程 (动态)：
    • 查找 c=5：
      • 锁住 (0,5] (next-key lock)。
      • c 非唯一索引，向右遍历确认无更多 c=5，找到 c=10。
      • c=10 不满足 c=5，根据优化2，next-key lock (5,10] 退化为间隙锁 (5,10)。
    • 查找 c=10：
      • 锁住 (5,10] (next-key lock)。
      • 向右遍历找到 c=15。
      • c=15 不满足 c=10，根据优化2，next-key lock (10,15] 退化为间隙锁 (10,15)。
    • 查找 c=20：
      • 锁住 (15,20] (next-key lock)。
      • 向右遍历找到 c=25。
      • c=25 不满足 c=20，根据优化2，next-key lock (20,25] 退化为间隙锁 (20,25)。
    • 加锁顺序： 记录锁 c=5 -> c=10 -> c=20。
  4. 问题场景2 (For Update with DESC order)： sql SELECT id FROM t WHERE c IN (5, 20, 10) ORDER BY c DESC FOR UPDATE;
  5. 加锁过程 (动态)：
    • 由于 ORDER BY c DESC，B+树搜索和加锁顺序相反。
    • 加锁顺序： 记录锁 c=20 -> c=10 -> c=5。
  6. 死锁产生：
    • 场景1和场景2并发执行时：
      • 事务1 (LOCK IN SHARE MODE) 持有 c=5 的S锁，等待 c=10 的S锁。
      • 事务2 (FOR UPDATE) 持有 c=20 的X锁和 c=10 的X锁，等待 c=5 的X锁。
    • 形成死锁。

四、如何分析死锁 (SHOW ENGINE INNODB STATUS)

  1. 输出结构：
    • LATEST DETECTED DEADLOCK 区域。
    • (1) TRANSACTION：第一个事务信息。
    • (2) TRANSACTION：第二个事务信息。
    • WE ROLL BACK TRANSACTION (X)：回滚的事务。
  2. 事务信息解读：
    • WAITING FOR THIS LOCK TO BE GRANTED：当前事务等待的锁。
      • index c of table test.t：锁在哪个表的哪个索引上。
      • lock mode S waiting：要加S锁，正在等待。
      • Record lock：记录锁。
      • n_fields X：记录的列数。
      • 0: len Y; hex ZZZZ; asc ...：列的定义和值 (十六进制和可打印字符)。
    • HOLDS THE LOCK(S)：当前事务持有的锁。
  3. 死锁推导： 根据两个事务分别持有的锁和等待的锁，分析循环等待关系。
  4. 死锁处理结论：
    • 避免死锁： 对同一组资源，尽量按相同顺序访问。
    • 回滚策略： InnoDB通常选择回滚持有锁较少或回滚成本较小的事务。

五、如何分析锁等待 (SHOW ENGINE INNODB STATUS)

  1. 问题场景：间隙变化导致插入阻塞
    • Session A: SELECT * FROM t WHERE c=5 FOR UPDATE; (锁住索引c上的 (0,5] 和间隙 (5,10))
    • Session B: DELETE FROM t WHERE id=10; (成功，因为id=10的记录锁在主键上，c=5的锁不影响)
    • Session B: INSERT INTO t VALUES(10,10,10); (阻塞)
  2. SHOW ENGINE INNODB STATUS 分析 (TRANSACTIONS 节)：
    • index PRIMARY of table test.t：锁等待发生在主键索引上。
    • lock_mode X locks gap before rec insert intention waiting：
      • insert intention: 插入意向锁 (可理解为插入动作本身)。
      • gap before rec: 这是一个间隙锁。
    • 记录信息 (n_fields 5, 0: len 4; hex 0000000f; asc ;; 等)：
      • 显示该间隙锁是定义在 id=15 这条记录之前的。
      • 由于 id=10 已被删除，原 (5,10) 和 (10,15) 两个间隙合并为 (5,15)。
      • Session B 尝试插入 id=10，落入 (5,15) 间隙，被 Session A 持有的作用于此间隙的锁阻塞。
  3. 重要结论：间隙的定义
    • 间隙是由“这个间隙右边的那个记录”定义的。
    • 当记录被删除或插入时，间隙会相应地合并或分裂，影响已加锁的范围。

六、UPDATE 语句的加锁分析 (动态视角)

  1. 问题场景：
    • Session A: SELECT * FROM t WHERE c > 5 FOR UPDATE;
      • 加锁范围：索引 c 上的 (5,10], (10,15], (15,20], (20,25], (25,supremum]。 (注意：c>5 查到的第一个是 c=10，所以不锁 (0,5])。
    • Session B: UPDATE t SET c=1 WHERE id=5; (将 (id=5,c=5) 改为 (id=5,c=1))
      • 可理解为两步：
        1. 删除 (id=5,c=5) 记录 (对应索引c上的记录)。
        2. 插入 (id=5,c=1) 记录 (对应索引c上的记录)。
      • 影响： Session A 在索引 c 上的加锁范围中，原来的 (0,5] 和 (5,10) 的部分会因 c=5 记录的“移动”而调整。原先由 c=5 定义的间隙 (0,5) 和由 c=10 定义的间隙 (5,10)。当 c=5 变为 c=1，间隙变为 (supinf,1) 和 (1,10)。
    • Session B: UPDATE t SET c=5 WHERE c=1; (尝试将 (id=5,c=1) 改回 (id=5,c=5))
      • 可理解为两步：
        1. 删除 (id=5,c=1)。
        2. 插入 (id=5,c=5)。
      • 阻塞点： 第二步插入 (id=5,c=5) 时，试图在 (1,10) 这个间隙（该间隙被 Session A 的锁覆盖）中插入数据，因此被阻塞。

七、核心小结与实践建议

  1. 动态分析： 分析加锁范围时，必须结合SQL语句的执行逻辑和顺序。
  2. 执行计划理解： 能够通过 EXPLAIN 结果脑补SQL执行流程，是理解索引和锁的关键。
  3. SHOW ENGINE INNODB STATUS： 重要的诊断工具，用于分析事务状态、锁信息和死锁现场。

八、延伸思考：文末问题预告

问题： 一个空表有间隙吗？这个间隙是由谁定义的？如何验证？ * 思考方向： * 空表是否有记录来定义“右边界”？ * MySQL如何处理空表的边界情况？(supremum/infimum伪记录) * 尝试在空表上执行会产生间隙锁的语句，并观察加锁情况。

九、上期问题回顾：业务监控处理经验

  • 服务状态监控： 外部系统检测服务是否存活 (如health check接口)。
  • 服务质量监控： 接口响应时间、成功率、错误率等指标。
  • 心跳机制： 服务主动上报健康状态。
  • 监控分层： 基础监控 (CPU, Mem, IO), 服务监控 (进程, QPS), 业务监控 (核心业务指标)。