技术分享 | MySQL间隙锁原理深度详解

本文为墨天轮数据库管理服务团队第150期技术分享，内容原创，作者为技术顾问陈洋，如需转载请联系小墨（VX：modb666）并注明来源。如需查看更多文章可关注【墨天轮】公众号。

一、间隙锁概述

间隙锁（Gap Lock）是InnoDB存储引擎在REPEATABLE READ（可重复读）隔离级别下为了解决幻读（Phantom Read）问题而引入的一种锁机制。它锁定的是索引记录之间的“间隙”，而不是实际存在的记录。这意味着，即使间隙中没有数据，间隙锁也能阻止其他事务在该间隙内插入新的数据，从而保证了在同一事务中多次读取相同范围的数据时，结果集保持一致。

二、幻读问题

幻读是指在同一个事务中，两次执行相同的查询语句，但第二次查询却看到了第一次查询没

1. 事务A在某个范围内执行了查询。
2. 事务B在该范围内插入了新的行并提交。
3. 事务A再次执行相同的查询，看到了事务B插入的新行，导致前后两次查询结果不一致。

在REPEATABLE READ隔离级别下，MySQL通过两种方式解决幻读问题：

• 快照读（Snapshot Read）：对于普通的SELECT语句，InnoDB通过MVCC（多版本并发控制）机制，在事务开始时生成一个Read View（一致性视图），后续的快照读都基于这个视图，因此不会看到其他事务提交的新数据。这种方式解决了普通SELECT语句的幻读。
• 当前读（Current Read）：对于SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、INSERT、UPDATE、DELETE等语句，它们需要读取最新的数据版本，因此称为当前读。在当前读情况下，MVCC无法解决幻读问题，此时就需要间隙锁来防止其他事务插入新数据。

三、间隙锁的实现原理

间隙锁是基于索引的，它锁定的是索引记录之间的空隙。当对某个范围的数据进行当前读操作时，InnoDB不仅会锁定符合条件的记录本身（记录锁），还会锁定这些记录前后的间隙，以及第一个记录之前的间隙和最后一个记录之后的间隙。这种记录锁和间隙锁的组合被称为Next-Key Lock。

3.1 Next-Key Lock

Next-Key Lock是InnoDB默认的行锁类型，它结合了记录锁（Record Lock）和间隙锁（Gap Lock）。一个Next-Key Lock会锁定一个索引记录以及该记录之前的间隙。其锁定范围是(前一个索引记录, 当前索引记录]。

例如，在一个索引包含值10、20、30的表中，Next-Key Lock可能锁定的区间包括：

• (-∞, 10]
• (10, 20]
• (20, 30]
• (30, +∞)

3.2 间隙锁的特性

• 只在REPEATABLE READ隔离级别下生效：在READ COMMITTED（读已提交）隔离级别下，没有间隙锁，因此可能会出现幻读。
• 不区分共享锁和排他锁：间隙锁的唯一目的是防止其他事务插入数据，因此它不区分共享（S）锁和排他（X）锁。任何事务持有间隙锁，都会阻止其他事务在该间隙内插入数据。
• 间隙锁之间不冲突：不同事务可以同时持有同一个间隙的间隙锁。因为间隙锁的目的是阻止插入，而不是阻止读取或修改已存在的数据。
• 与索引相关：间隙锁是加在索引上的，而不是数据行本身。如果查询没有使用索引，或者使用的索引不能有效地限制扫描范围，间隙锁可能会锁定整个表，导致并发性能下降。

四、间隙锁的加锁规则

间隙锁的加锁规则相对复杂，主要取决于查询条件、索引类型以及是否是唯一索引：

1. 等值查询与唯一索引：

• 如果查询条件是唯一索引的等值查询，并且找到了对应的记录，那么Next-Key Lock会退化为记录锁，只锁定该行，不会产生间隙锁。因为唯一索引保证了该值是唯一的，不会有新的数据插入到该位置。
• 如果查询条件是唯一索引的等值查询，但没有找到对应的记录，那么会在不存在的记录位置形成一个间隙锁，锁定该间隙，防止其他事务插入该值。

2. 等值查询与非唯一索引：

• 如果查询条件是非唯一索引的等值查询，无论是否找到记录，都会在扫描到的符合条件的记录以及其前后的间隙上加Next-Key Lock。这是因为非唯一索引可能存在多个相同的值，需要锁定一个范围来防止幻读。

3. 范围查询：

• 对于范围查询（如WHERE id > 10或WHERE id BETWEEN 10 AND 20），无论是否是唯一索引，都会在扫描到的所有符合条件的记录及其前后的间隙上加Next-Key Lock。扫描会持续到第一个不满足条件的记录，并锁定该记录之前的间隙。

4. 无索引或索引失效：

• 如果查询没有使用索引，或者索引失效，那么InnoDB会进行全表扫描。在这种情况下，为了防止幻读，InnoDB会给整个表的所有索引记录都加上Next-Key Lock，这会严重影响并发性能。

五、间隙锁的示例

假设有一个products表，其中包含id（主键）、name和price字段，并且id是自增主键。

CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `products` (`id`, `name`, `price`) VALUES
(1, 'Laptop', 1200.00),
(5, 'Mouse', 25.00),
(10, 'Keyboard', 75.00);

当前products表中的id值有1, 5, 10。那么存在的间隙包括：

• (-∞, 1]
• (1, 5]
• (5, 10]
• (10, +∞)

示例1：等值查询（唯一索引，找到记录）

事务A:

BEGIN;
SELECT * FROM products WHERE id = 5 FOR UPDATE;

分析：id是主键（唯一索引），查询条件是等值查询且找到了记录。此时，Next-Key Lock会退化为记录锁，只锁定id = 5的行。其他事务可以插入id = 2或id = 7的记录，但不能修改或删除id = 5的记录。

示例2：等值查询（唯一索引，未找到记录）

事务A:

BEGIN;
SELECT * FROM products WHERE id = 3 FOR UPDATE;

分析：id是主键（唯一索引），查询条件是等值查询但未找到记录。此时，会在(1, 5]这个间隙上加间隙锁，阻止其他事务插入id为2、3、4的记录。例如，事务B尝试插入id = 2的记录会被阻塞。

示例3：范围查询

事务A:

BEGIN;
SELECT * FROM products WHERE id > 5 FOR UPDATE;

分析：查询条件是范围查询。InnoDB会扫描id = 10的记录，并锁定(5, 10]和(10, +∞)这两个间隙。这意味着，其他事务不能插入id为6、7、8、9的记录，也不能插入id大于10的记录。同时，id = 10的记录本身也会被锁定。

六、间隙锁的优缺点

优点：

• 解决幻读：在REPEATABLE READ隔离级别下，间隙锁有效地防止了幻读的发生，保证了数据的一致性。

缺点：

• 降低并发性：间隙锁锁定的不是具体的行，而是索引的范围，这可能导致不必要的锁定，从而降低了数据库的并发性能。即使没有数据，间隙也会被锁定。
• 死锁风险：间隙锁增加了死锁的风险，因为多个事务可能在不同的间隙上持有锁，并尝试获取对方持有的间隙上的锁，从而形成死锁。
• 难以理解和排查：间隙锁的加锁规则相对复杂，使得在出现性能问题或死锁时，排查和定位问题变得更加困难。

七、总结

间隙锁是MySQL InnoDB存储引擎在REPEATABLE READ隔离级别下解决幻读问题的关键机制。理解其原理、加锁规则以及优缺点对于数据库性能优化和问题排查至关重要。在实际应用中，应根据业务需求和并发量，合理选择事务隔离级别，并注意避免因间隙锁导致的性能瓶颈和死锁问题。

八、间隙锁的排查与定位

在实际的数据库运维和开发中，间隙锁可能导致性能问题甚至死锁。因此，了解如何排查和定位间隙锁是至关重要的。

8.1 识别间隙锁导致的性能问题

• 慢查询日志

  ：检查MySQL的慢查询日志，特别是那些执行时间长、涉及范围查询且隔离级别为REPEATABLE READ的SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE语句。这些语句很可能触发了间隙锁。

• SHOW PROCESSLIST：通过SHOW PROCESSLIST命令可以查看当前正在执行的SQL语句。如果发现有大量事务长时间处于Locked或Waiting for table metadata lock状态，并且涉及的SQL语句是范围查询，则可能与间隙锁有关。
• information_schema数据库：information_schema数据库提供了许多关于MySQL服务器状态的信息。以下几个表对于排查锁问题非常有用：
• information_schema.INNODB_TRX：显示当前所有正在运行的InnoDB事务的信息，包括事务ID、事务状态、锁等待情况等。
• information_schema.INNODB_LOCKS：显示当前被锁定的资源以及持有锁的事务信息。可以查看锁的类型（如RECORD、GAP、AUTO_INC等）和锁定的索引。
• information_schema.INNODB_LOCK_WAITS：显示当前存在的锁等待关系，可以帮助识别死锁或长时间的锁等待。

8.2 使用SHOW ENGINE INNODB STATUS排查

SHOW ENGINE INNODB STATUS命令是排查InnoDB存储引擎问题（包括锁问题）的强大工具。它会输出大量关于InnoDB内部状态的信息，其中LATEST DETECTED DEADLOCK和TRANSACTIONS部分对于分析间隙锁导致的死锁和锁等待尤为重要。

输出解读要点：

• LATEST DETECTED DEADLOCK：如果发生了死锁，这一部分会详细记录最近一次死锁的信息，包括死锁涉及的事务、它们尝试获取的锁、持有的锁以及等待的资源。通过分析这里的信息，可以明确是哪些事务在哪些间隙上发生了死锁。
• RECORD LOCKS：表示记录锁。
• GAP LOCKS：表示间隙锁。
• NEXT-KEY LOCKS：表示临键锁（记录锁+间隙锁）。
• TRANSACTIONS：这一部分列出了所有活跃的事务，包括它们的事务ID、状态、执行的SQL语句、持有的锁以及等待的锁。通过查看LOCK WAIT状态的事务，可以找到正在等待锁的事务，并进一步分析其等待的原因。
• 查找LOCK WAIT状态的事务。
• 查看LOCKED TABLES和WAITING FOR THIS LOCK TO BE GRANTED部分，了解事务正在等待的锁类型和资源。
• 结合SQL语句，判断是否是间隙锁导致的等待。

示例：

SHOW ENGINE INNODB STATUS\G

执行上述命令后，会得到一个详细的报告。你需要仔细阅读其中的LATEST DETECTED DEADLOCK和TRANSACTIONS部分。

8.3 模拟和复现间隙锁

为了更好地理解和排查间隙锁，可以在测试环境中模拟和复现间隙锁的场景。这通常涉及：

1. 设置数据库隔离级别为REPEATABLE READ。
2. 创建包含索引的测试表。
3. 开启多个事务，在不同的事务中执行会触发间隙锁的SQL语句（如范围查询的FOR UPDATE语句），并尝试在间隙中插入数据，观察事务的阻塞和死锁情况。

通过模拟，可以加深对间隙锁行为的理解，并验证排查方法是否有效。

8.4 避免间隙锁导致的性能问题

• 降低隔离级别：如果业务允许，可以将事务隔离级别从REPEATABLE READ降至READ COMMITTED。在READ COMMITTED隔离级别下，InnoDB不会使用间隙锁，从而避免了幻读和间隙锁带来的性能问题。但需要注意的是，这可能会引入其他并发问题，需要根据业务场景权衡。
• 优化SQL语句
• 尽量使用等值查询，避免不必要的范围查询。
• 确保查询条件能够命中索引，避免全表扫描。全表扫描会导致整个表被间隙锁锁定，严重影响并发。
• 对于范围查询，尽量缩小查询范围，减少间隙锁锁定的范围。
• 避免不必要的FOR UPDATE或LOCK IN SHARE MODE：只有在确实需要对查询结果进行更新或需要保证数据一致性时，才使用这些语句。
• 拆分大事务：将长时间运行的大事务拆分为多个小事务，减少事务持有锁的时间，从而降低间隙锁冲突的概率。
• 使用乐观锁：对于某些业务场景，可以考虑使用乐观锁（通过版本号或时间戳）来替代悲观锁，减少数据库层面的锁竞争。
• 调整索引：合理设计索引，确保查询能够高效地利用索引，减少不必要的全表扫描或索引扫描。

通过上述方法，可以有效地排查、定位和避免MySQL间隙锁带来的性能问题和死锁风险。

九、间隙锁死锁案例分析与解决方案

间隙锁虽然解决了幻读问题，但它引入了死锁的风险。当两个或多个事务在获取间隙锁时形成循环等待，就会发生死锁。以下是一个典型的间隙锁死锁案例及其解决方案。

9.1 案例场景：并发插入导致的间隙锁死锁

假设我们有一个orders表，其中包含id（主键）、order_no（唯一索引）和amount字段。为了简化，我们只关注id和order_no。

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(255) UNIQUE,
  `amount` decimal(10,2),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `orders` (`id`, `order_no`, `amount`) VALUES
(1, 'A001', 100.00),
(5, 'A005', 200.00),
(10, 'A010', 300.00);

当前orders表中的order_no值有’A001’, ‘A005’, ‘A010’。假设现在有两个事务（事务A和事务B）几乎同时尝试插入order_no在’A001’和’A005’之间的记录，例如’A003’和’A004’。

事务A:

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 步骤1: 事务A尝试插入 'A003'
INSERT INTO orders (order_no, amount) VALUES ('A003', 150.00);

事务B:

-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 步骤1: 事务B尝试插入 'A004'
INSERT INTO orders (order_no, amount) VALUES ('A004', 180.00);

死锁发生过程：

1. 事务A执行INSERT ('A003', ...)：

• 为了插入’A003’，InnoDB需要检查order_no唯一索引中(A001, A005)这个间隙。事务A会在这个间隙上加一个意向插入锁（Insert Intention Lock），这是一种特殊的间隙锁，表示事务A打算在这个间隙中插入一条记录。同时，为了保证唯一性，它可能还需要对A001和A005这两个记录加S锁或X锁（具体取决于索引类型和操作）。

2. 事务B执行INSERT ('A004', ...)：

• 几乎同时，事务B也尝试插入’A004’。它也需要检查order_no唯一索引中(A001, A005)这个间隙。事务B也会在这个间隙上加一个意向插入锁。

3. 冲突与死锁：

• 虽然意向插入锁之间通常不会直接冲突，但当两个事务都试图在同一个间隙内插入数据时，它们可能会尝试获取间隙内的其他锁（例如，为了检查唯一性而对相邻记录加的锁），或者在内部对间隙进行更细粒度的锁定。在这种情况下，如果事务A持有了间隙(A001, A005)的一部分锁，并等待事务B持有的另一部分锁；同时事务B持有了间隙(A001, A005)的另一部分锁，并等待事务A持有的锁，就会形成循环等待，导致死锁。
• MySQL的死锁检测机制会发现这个循环，并选择其中一个事务作为“牺牲品”（通常是修改行数较少的事务），回滚该事务，从而解除死锁。被回滚的事务会收到ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction错误。

9.2 解决方案

针对这种因间隙锁导致的死锁，可以采取以下几种策略：

1. 降低事务隔离级别：将事务隔离级别从REPEATABLE READ降至READ COMMITTED。在READ COMMITTED级别下，INSERT操作通常只在插入的行上加行锁，而不会加间隙锁，从而避免了这类死锁。但需要注意的是，READ COMMITTED隔离级别下可能出现幻读（对于快照读），需要根据业务场景权衡。
2. 优化SQL语句和索引：

• 避免在非唯一索引上进行范围查询的FOR UPDATE或LOCK IN SHARE MODE：如果业务允许，尽量避免在非唯一索引上使用FOR UPDATE或LOCK IN SHARE MODE进行范围查询，因为这会更容易触发间隙锁。
• 合理设计唯一索引：如果order_no是唯一的，并且业务逻辑允许，可以考虑在插入前先进行一次SELECT ... FOR UPDATE来预先锁定范围，但这会降低并发性。

3. 应用程序层面处理死锁：在应用程序代码中捕获死锁异常（错误码1213），并实现事务重试机制。当发生死锁时，回滚当前事务，并等待一小段时间后重新尝试执行事务。这是处理死锁的常见且有效的方法。
4. 使用自增主键作为插入依据：如果order_no不是严格递增的，或者其生成逻辑复杂，可以考虑让id（自增主键）作为主要的插入依据，而order_no作为普通唯一索引。在某些情况下，这可以减少间隙锁的冲突。
5. 批量插入：如果需要插入大量数据，可以考虑使用批量插入（INSERT INTO ... VALUES (...), (...);）而不是单条插入。批量插入可以减少事务的数量和锁的竞争。
6. 调整业务逻辑：重新审视业务逻辑，看是否可以调整操作顺序或数据模型，以减少并发事务对相同间隙的竞争。

---

墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。
墨天轮数据库服务官网：https://www.modb.pro/service