本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
事务与并发（ACID / 隔离级别 / 锁 / MVCC） · 并发问题排查

RR 隔离级别下，UPDATE / DELETE 操作会加什么锁？示意图分析。

What locks are acquired by UPDATE / DELETE operations under the RR isolation level? A diagrammatic analysis.

答案语言

考察要点

这道题面试官想看你是否深入理解 InnoDB 在默认隔离级别（Repeatable Read）下的加锁机制。核心是考察你对 Record Lock（行锁）、Gap Lock（间隙锁）和 Next-Key Lock（临键锁） 的掌握程度，以及能否清晰地分析出在不同查询条件（唯一索引、普通索引、无索引）下，UPDATE/DELETE 语句会施加哪种锁、锁定多大的范围。这直接关系到数据库的并发性能和死锁排查能力。

核心概念

在 MySQL 的 RR 隔离级别下，UPDATE 和 DELETE 属于当前读（Current Read），它们会读取并锁定数据库中最新的、已提交的数据版本，以防止其他事务并发修改。这与普通 SELECT 使用的快照读（Snapshot Read）（基于 MVCC）形成对比。其加锁行为主要依赖以下三种锁：

  1. Record Lock (行锁):

    • 定义: 这是最基本的锁，直接锁定单条索引记录。
    • 作用: 防止其他事务对该行进行 UPDATE 或 DELETE。
    • 示例: UPDATE users SET age = 30 WHERE id = 1; (假设 id 是主键)，会给 id=1 的主键索引记录加上一个 Record Lock。

  2. Gap Lock (间隙锁):

    • 定义: 锁定一个开区间，即两个索引记录之间的间隙，或者第一个索引记录之前的间隙，或最后一个索引记录之后的间隙。它不锁定任何实际存在的记录。
    • 作用: 防止其他事务在这个间隙中 INSERT 新的记录，这是解决幻读问题的关键。
    • 示意图: 假设 users 表 age 字段有索引，且存在值为 20 和 30 的记录。
      text
       1... (age < 20) | record(age=20) | (20 < age < 30) | record(age=30) | (age > 30) ...
       2              ^                ^                 ^                ^
       3              |-- Gap Lock --|   |-- Gap Lock --|   |-- Gap Lock --|

  3. Next-Key Lock (临键锁):

    • 定义: Record Lock 和 Gap Lock 的结合体，锁定一个左开右闭的区间。即，它既锁定一个索引记录本身，也锁定该记录之前的那个间隙。
    • 公式: Next-Key Lock = Record Lock + Gap Lock (on the gap before the record)
    • 作用: InnoDB 在 RR 级别下，进行范围扫描和更新时的默认加锁方式。
    • 示意图: 锁定 age=30 的 Next-Key Lock，实际锁定了 (20, 30] 这个区间。
      text
       1... (age < 20) | record(age=20) | (20 < age < 30) | record(age=30) | (age > 30) ...
       2                               ^-----------------^
       3                               |-- Next-Key Lock --|

不同场景下的加锁分析

为了方便演示，我们创建一张示例表：

sql
 1CREATE TABLE `users` (
 2  `id` int(11) NOT NULL AUTO_INCREMENT,
 3  `name` varchar(255) DEFAULT NULL,
 4  `age` int(11) DEFAULT NULL,
 5  PRIMARY KEY (`id`),
 6  KEY `idx_age` (`age`)
 7) ENGINE=InnoDB;
 8

 9INSERT INTO `users` (`id`, `name`, `age`) VALUES (1, 'Alice', 10), (5, 'Bob', 20), (10, 'Charlie', 30), (15, 'David', 40);

age 索引上的记录分布为 10, 20, 30, 40。

场景一：WHERE 条件命中唯一索引（主键或唯一键），且为等值查询

  • SQL 示例: UPDATE users SET name = 'Alice_new' WHERE id = 5;
  • 加锁行为:
    • InnoDB 会将 Next-Key Lock 退化为 Record Lock。
    • 原因: 因为索引是唯一的，查询能精确命中一行，不需要保护任何间隙来防止幻读（不可能有另一个 id=5 的行被插入）。
    • 锁定范围: 仅锁定 id=5 这一行记录。这是最高效的加锁方式。

场景二：WHERE 条件命中普通索引，且为等值查询

  • SQL 示例: UPDATE users SET name = 'Bob_new' WHERE age = 20;
  • 加锁行为:
    • 首先，在 idx_age 索引上，找到 age=20 的记录。由于 age 不是唯一的，可能存在多个 age=20 的记录。InnoDB 需要防止新的 age=20 的记录被插入。
    • 它会对 age=20 的索引记录加上 Next-Key Lock，锁定 (10, 20] 这个区间。
    • 为了防止其他事务在 age=20 之后插入新的 age=20 的记录，扫描会继续向后查找，直到下一个不等于 20 的值（即 30）。然后在这个间隙 (20, 30) 上加一个 Gap Lock。
    • 然后，对于每个找到的 age=20 的记录，通过主键索引回表，给对应的主键记录（id=5）加上 Record Lock。
  • 锁定范围: idx_age 上的 (10, 30) 区间被锁定，以及 id=5 的主键记录被锁定。

场景三：WHERE 条件命中普通索引，且为范围查询

  • SQL 示例: DELETE FROM users WHERE age >= 20 AND age < 35;
  • 加锁行为:
    • InnoDB 会在 idx_age 索引上进行范围扫描。
    • 它会锁定所有扫描到的索引记录。
    • 扫描从 age=20 开始，给它加上 Next-Key Lock，锁住 (10, 20]。
    • 继续扫描到 age=30，给它加上 Next-Key Lock，锁住 (20, 30]。
    • 继续扫描到 age=40，这是第一个不满足 age < 35 的记录，扫描停止。但为了保护范围的右边界，需要给 age=40 的记录也加上 Next-Key Lock，锁住 (30, 40]。
    • 同时，所有匹配记录（id=5, id=10）对应的主键索引记录会被加上 Record Lock。
  • 锁定范围: 在 idx_age 上，锁定了 (10, 40] 这个大区间。

场景四：WHERE 条件无索引

  • SQL 示例: UPDATE users SET age = 99 WHERE name = 'Bob';
  • 加锁行为:
    • 由于 name 字段没有索引，MySQL 别无选择，只能进行全表扫描。
    • 为了防止在扫描过程中出现幻读（比如另一个事务插入了满足条件的行），InnoDB 会对聚簇索引（主键索引）上的每一条记录都施加 Next-Key Lock。
    • 这相当于锁定了整张表，以及所有记录之间的间隙。
  • 锁定范围: 全表锁定。任何其他事务都无法对该表进行任何 INSERT、UPDATE、DELETE 操作。这是性能灾难，必须在生产中极力避免。

常见低分回答

  1. “UPDATE/DELETE 就是加行锁”: 这个回答过于笼统，没有区分不同索引和查询条件下的差异，忽略了 Gap Lock 和 Next-Key Lock 的存在，这是 RR 级别并发控制的核心。
  2. “只有 SELECT ... FOR UPDATE 才加锁，UPDATE 和 DELETE 不加”: 这是对当前读的根本性误解。UPDATE 和 DELETE 本身就是一种锁定读操作，它们必须加锁以保证数据一致性。
  3. 混淆 RC 和 RR 的加锁行为: 回答说“RR 级别下加的是行锁”，实际上这是 RC 级别的主要行为（RC 默认禁用 Gap Lock，只在特定情况如外键约束检查或唯一性检查时使用）。RR 的核心就是 Next-Key Lock。
  4. 不知道无索引查询会锁全表: 这是一个非常严重的知识盲点。候选人如果不知道这一点，说明其对数据库性能和并发风险的理解不足。
  5. “Gap Lock 是为了解决不可重复读”: 这是概念性错误。Gap Lock 主要为了解决幻读问题（Phantom Read），而不可重复读（Non-Repeatable Read）是由 MVCC 或 Record Lock 解决的。

工程实践与优化

  • 为高频更新/删除的 WHERE 条件建立索引: 这是最重要的优化。确保 UPDATE/DELETE 语句的 WHERE 子句中的列都有合适的索引，可以极大地缩小锁的范围，从全表锁降级为 Next-Key Lock 甚至 Record Lock，从而提高并发性能。
  • 使用 EXPLAIN 分析执行计划: 在上线任何重要的 UPDATE/DELETE 语句前，使用 EXPLAIN 检查其执行计划。重点关注 type 列是否为 ALL（全表扫描），key 列是否使用了预期索引。
  • 缩小事务范围，尽快提交: 大事务会长时间持有锁，严重阻塞其他会话。应将业务逻辑拆分，让事务尽可能简短，操作完成后立即 COMMIT 或 ROLLBACK 释放锁。
  • 使用唯一索引进行点更新: 如果业务逻辑允许，尽可能使用主键或唯一键作为 UPDATE/DELETE 的条件。这能让锁退化为 Record Lock，实现最大程度的并发。
  • 死锁排查: 当发生死锁时，使用 SHOW ENGINE INNODB STATUS 命令查看 LATEST DETECTED DEADLOCK 部分。它会详细列出死锁涉及的两个事务、它们持有的锁、正在等待的锁以及发生冲突的 SQL 语句，是定位死锁根源的最有效工具。

常见追问与延伸题目

  1. 追问：为什么 RR 级别需要 Gap Lock 和 Next-Key Lock，而 RC 级别基本不需要？

    • 回答要点: 这是由隔离级别的定义决定的。RR（可重复读）的契约是在一个事务内，多次执行相同的范围查询，结果集应该完全一致，不能多出“幻影”行。为了阻止其他事务在两次查询之间插入新的、符合范围条件的行，就必须用 Gap Lock 锁住这些可能被插入的“间隙”。而 RC（读已提交）只保证能读到已提交的数据，它允许幻读和不可重复读的发生，因此不需要 Gap Lock 来保护间隙，默认只对实际操作的行加 Record Lock，并发性更好。

  2. 追问：如果我的业务场景可以容忍幻读，但对并发写入性能要求极高，应该怎么做？

    • 回答要点:
      1. 调整隔离级别: 最直接的方法是将数据库或当前会话的隔离级别设置为 READ COMMITTED。SET GLOBAL transaction_isolation = 'READ-COMMITTED'; 或 SET SESSION transaction_isolation = 'READ-COMMITTED';。这会禁用 Gap Lock，大大减少锁冲突，提升并发写入（尤其是 INSERT）的性能。
      2. 检查 Binlog 格式: 在此之前，必须确认 MySQL 的 binlog_format 设置为 ROW 或 MIXED。如果是 STATEMENT 格式，在 RC 级别下主从复制可能出现数据不一致问题。幸运的是，MySQL 5.7.7+ 默认就是 ROW 格式。
      3. 评估业务影响: 确认业务逻辑上确实能接受幻读带来的影响，避免出现意料之外的数据不一致。

  3. 追问：UPDATE users SET name = 'New' WHERE id = 9;，假设表中没有 id=9 的记录，这条 SQL 会加什么锁？

    • 回答要点: 这是一个经典的边界情况测试。即使记录不存在，InnoDB 仍然需要加锁来防止这条记录被插入，以维护 RR 隔离级别。
      1. 扫描定位: 数据库会扫描主键索引，定位到 id=9 应该插入的位置。根据我们的示例数据（1, 5, 10, 15），id=9 位于 id=5 和 id=10 之间。
      2. 加间隙锁: InnoDB 会在这个位置加上一个 Gap Lock，锁定的范围是 (5, 10)。
      3. 效果: 这个 Gap Lock 会阻塞任何其他事务执行 INSERT INTO users (id, ...) VALUES (9, ...) 或任何 id 在 (5, 10) 区间内的插入操作，直到当前事务提交或回滚。这完美地防止了幻读。
← Previous
SELECT FOR UPDATE 在不同隔离级别下的行为差异？
Next →
InnoDB 的整体架构图？Buffer Pool / Change Buffer / Log Buffer / Data Dictionary 的作用？