本文共 3016 字，大约阅读时间需要 10 分钟。

MySQL 加锁机制与死锁分析

加锁规则概述

在 MySQL 中，加锁是管理并发访问的核心机制。为了确保数据一致性，InnoDB 引擎采用了行锁机制，但具体的加锁规则较为复杂，涉及到原则、优化和一些特殊情况（bug）。以下是需要掌握的关键点：

1. 加锁原则

• 原则1：加锁的基本单位是 next-key lock。这是一个前开后闭的区间，表示从某个记录开始，到下一个记录结束的范围。
• 原则2：只有在查找过程中访问到的对象才会加锁。这意味着锁只会在索引上的记录上申请，而不会锁整个表。

2. 加锁优化

• 优化1：在索引上的唯一性查询（如 WHERE c = 5）时，唯一索引会退化为行锁。这是因为唯一索引的查找范围很小，只会锁定对应的行。
• 优化2：在索引上的等值查询（如 WHERE c IN (5, 20, 10)），如果查找过程中遇到不满足条件的记录，则会锁定一个间隙锁（gap lock）。间隙锁的作用是防止插入操作破坏索引的结构。

3. 一个重要的 bug

• 在唯一索引上执行范围查询（如 WHERE c BETWEEN 5 AND 10）时，系统会继续锁定不满足条件的第一个记录。这意味着即使 c=5 满足条件，c=10 不满足，系统仍然会锁定 c=5 这一行，导致性能问题。

不等号条件中的等值查询

在实际项目中，WHERE 条件中经常使用不等号（如 id > 9 AND id < 12）。这时候，系统如何处理等值查询呢？让我们通过一个具体的例子来分析：

示例分析

BEGIN;SELECT * FROM t WHERE id > 9 AND id < 12 ORDER BY id DESC FOR UPDATE;

加锁范围分析

• 主键索引：表 t 的主键是 id，一个 next-key lock 是前开后闭的区间。
• 执行过程：
  • 首先确定 id < 12 的最大值（id=11），然后向左遍历索引树。
  • 找到 id=10 时，发现 id=10 满足条件，但 id=15 不满足条件，因此锁定 (10, 15) 这个间隙。
  • 继续向左遍历，找到 id=5 满足条件，锁定 (0, 5]。
  • 最后，找到 id=5 满足条件，锁定 (5, 10]。

因此，最终加锁范围为 (0, 5]、(5, 10] 和 (10, 15)，id=15 未被加锁。

关键点

• 间隙锁的作用：防止插入操作破坏索引结构。
• 加锁顺序：锁定是按遍历顺序依次加锁的，而不是一次性加锁。

等值查询的过程

在 WHERE 条件中使用 IN 语句时，系统如何处理等值查询呢？我们来看下面的例子：

BEGIN;SELECT id FROM t WHERE c IN (5, 20, 10) LOCK IN SHARE MODE;

Explain 结果分析

• 索引选择：系统选择了非唯一索引 c，说明 c 索引非常有助于减少查询时间。
• 执行方式：IN 语句内的值是通过逐个查找的方式加锁的。

加锁过程

加锁顺序

• 插入语句：如果没有 ORDER BY，加锁顺序是 c=5 → c=10 → c=20。
• 带 ORDER BY 的语句：加锁顺序会变为 c=20 → c=10 → c=5。

死锁案例

假设有两个并发的 SELECT 语句：

SELECT id FROM t WHERE c IN (5, 20, 10) LOCK IN SHARE MODE;

SELECT id FROM t WHERE c IN (5, 20, 10) ORDER BY c DESC FOR UPDATE;

死锁原因

• 资源冲突：两条语句都试图加锁 c=5、c=10 和 c=20。
• 加锁顺序不同：导致其中一条语句等待锁，另一条语句持有锁，无法继续执行。

如何查看死锁？

执行 SHOW ENGINE InnoDB STATUS 可以查看最新的死锁信息。例如：

死锁信息示例

• 事务1：等待锁 (c=10, id=10)。
• 事务2：持有锁 (c=5, id=5) 和 (c=20, id=20)，等待锁 (c=5, id=5)。

如何避免死锁？

• 加锁顺序统一：确保同一组锁在所有事务中按相同顺序加锁。
• 减少锁的竞争：尽量减少并发访问相同资源的语句。

锁等待案例

在实际项目中，锁等待是常见的性能问题。例如，当一个 DELETE 操作修改了索引结构后，其他事务可能因为索引的变化而无法继续。

示例分析

假设表 t 的数据为：

id | c | d0 | 0 | 05 | 5 | 510 | 10 | 1015 | 15 | 1520 | 20 | 2025 | 25 | 25

步骤 1：DELETE 操作

DELETE FROM t WHERE c = 10;

• 加锁范围：c=10 的记录锁。
• 修改后的索引结构：删除 id=10 后，索引树的结构发生了变化，原来的间隙 (10, 15) 被锁定，但 id=10 已经被删除。

步骤 2：尝试 INSERT 操作

INSERT INTO t VALUES (10, 5, 5);

• 加锁范围：尝试在 c=5 的间隙 (5, 10) 中插入数据。
• 锁等待：因为 c=10 已经被删除，系统无法找到对应的记录，导致锁等待。

如何解决？

• 添加唯一约束：可以在 c 字段上添加唯一约束，防止重复值的插入。
• 优化索引：定期检查和优化索引，确保索引结构的有效性。

update 操作的加锁示例

在 UPDATE 操作中，加锁范围的选择同样需要仔细分析。例如，当 c 字段被修改时，系统可能会锁定多个间隙。

示例分析

假设表 t 的数据为：

id | c | d0 | 0 | 05 | 5 | 510 | 10 | 1015 | 15 | 1520 | 20 | 2025 | 25 | 25

步骤 1：UPDATE 操作

UPDATE t SET c = 1 WHERE c = 5;

• 加锁范围：c=5 的记录锁。
• 修改后的数据：c=5 被更新为 c=1，c=5 的记录被删除，c=1 的记录被插入。

步骤 2：再次 UPDATE 操作

UPDATE t SET c = 5 WHERE c = 1;

• 加锁范围：尝试在 c=1 的间隙 (1, 10) 中插入 c=5。
• 锁等待：由于 c=10 已经被删除，系统无法找到对应的记录，导致锁等待。

如何解决？

• 优化查询：避免在已删除的记录上进行更新操作。
• 检查索引：定期检查索引的完整性，确保索引结构的正确性。

小结

通过以上分析，可以看到加锁机制在实际项目中的重要性。掌握加锁规则、理解死锁原因以及学会如何通过 SHOW ENGINE InnoDB STATUS 查看锁等待信息，是优化数据库性能的关键技能。

如果你对加锁机制和死锁分析感兴趣，可以深入学习 InnoDB 的存储引擎原理，了解更多关于锁的实现细节和优化技巧。

转载地址：http://lzcy.baihongyu.com/