MySQL 行级锁原理详解：Record Lock、Gap Lock 与 Next-Key Lock

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁 + 记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

select … for update 有啥用？我不加 for update 不行吗？

可以解决'快照读'在特定场景下的不足

在 MySQL 的默认隔离级别'可重复读'下，普通的 SELECT 语句是'快照读'。它基于 MVCC 读取一个历史快照，不会加锁。这虽然保证了高并发下的读取性能，但在某些业务场景下会产生问题。

经典场景：库存扣减

假设商品 A 的库存为 1。

1. 事务 A 查询库存：SELECT stock FROM products WHERE id = 1;（返回 stock=1）
2. 事务 B 也查询库存：SELECT stock FROM products WHERE id = 1;（返回 stock=1）
3. 事务 A 下单，执行：UPDATE products SET stock = stock - 1 WHERE id = 1并提交。
4. 此时库存已为 0。
5. 事务 B 也执行：UPDATE products SET stock = stock - 1 WHERE id = 1并提交。

最终结果是库存变成了 -1，这就是典型的'超卖'问题。

使用 SELECT … FOR UPDATE 解决：

1. 事务 A 查询并锁定库存：SELECT stock FROM products WHERE id = 1 FOR UPDATE;（对 id=1 的记录加排他锁）
2. 事务 B 也尝试执行 SELECT … FOR UPDATE 查询同一件商品，但会被阻塞，直到事务 A 释放锁。
3. 事务 A 完成下单和更新操作，提交事务，释放锁。
4. 事务 B 获得锁，执行查询，此时它读到的 stock 已经是事务 A 更新后的 0，因此可以阻止后续的扣减操作。

MySQL 是怎么加行级锁的？

加锁的对象是索引，加锁的基本单位是 next-key lock。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下，next-key lock 就会退化成记录锁或间隙锁。

这次会以下面这个表结构来进行实验说明：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

其中，id 是主键索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。

表中的有这些行记录：

我本篇文章的「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。

然后，很多同学误以为如果是二级索引的「唯一索引」，加锁也是只加在二级索引项上。

其实这是不对的，所以这里特此说明下，如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」。

在文章的「非唯一索引」的案例中，我就是用二级索引作为例子，在后面的章节我有说明，对二级索引进行锁定读查询的时候，因为存在两个索引（二级索引和主键索引），所以两个索引都会加锁。

唯一索引（主键索引）等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;

接下来，如果有其他事务，对 id 为 1 的记录进行更新或者删除操作的话，这些操作都会被阻塞，因为更新或者删除操作也会对记录加 X 型的记录锁，而 X 锁和 X 锁之间是互斥关系。

• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

BEGIN;
SELECT * FROM user WHERE id = 2 FOR UPDATE;

此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。

唯一索引（主键索引）范围查询

实验一：针对「大于」的范围查询的情况。

假设事务 A 执行了这条范围查询语句：

BEGIN;
SELECT * FROM user WHERE id > 15 FOR UPDATE;

• 在 id = 20 这条记录的主键索引上，加了范围为 (15, 20] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录。
• 在特殊记录（supremum pseudo-record）的主键索引上，加了范围为 (20, +∞] 的 next-key 锁，意味着其他事务无法插入 id 值大于 20 的这一些新记录。

实验二：针对「大于等于」的范围查询的情况。

假设事务 A 执行了这条范围查询语句：

BEGIN;
SELECT * FROM user WHERE id >= 15 FOR UPDATE;

• 在 id = 15 这条记录的主键索引上，加了记录锁，范围是 id = 15 这一行记录；意味着其他事务无法更新或者删除 id = 15 的这一条记录；
• 在 id = 20 这条记录的主键索引上，加了 next-key 锁，范围是 (15, 20] 。意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录。
• 在特殊记录（supremum pseudo-record）的主键索引上，加了 next-key 锁，范围是 (20, +∞] 。意味着其他事务无法插入 id 值大于 20 的这一些新记录。

实验三：针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况。

假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 6）并不存在于表中。

BEGIN;
SELECT * FROM user WHERE id < 6 FOR UPDATE;

• 在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录。
• 在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录。
• 在 id = 10 这条记录的主键索引上，加了范围为 (5, 10) 的间隙锁，意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录。

读者提问：请教一个问题 在看 mysql 加行级锁中 select * from user where id<6 for update; 为什么 id(5,10) 要加间隙锁呢？不加 也不会发生幻读吧？

回答：第三个间隙锁是加在 id=10 索引上的，这个例子不太好说明，如果是 id<7，id=10 索引不加间隙锁的话，中途插入一个 6，就发生幻读了

实验四：针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况。

假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 5）存在于表中。

BEGIN;
SELECT * FROM user WHERE id <= 5 FOR UPDATE;

• 在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录。
• 在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录。

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

先说结论：

• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

1、记录不存在的情况

假设事务 A 对非唯一索引（age）进行了等值查询，且表中不存在 age = 25 的记录。

BEGIN;
SELECT * FROM user WHERE age = 25 FOR UPDATE;

2、记录存在的情况

假设事务 A 对非唯一索引（age）进行了等值查询，且表中存在 age = 22 的记录。

BEGIN;
SELECT * FROM user WHERE age = 22 FOR UPDATE;

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，都是加 next-key 锁。

就带大家简单分析一下，事务 A 的这条范围查询语句：

BEGIN;
SELECT * FROM user WHERE age >= 22 FOR UPDATE;

在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？

因为 age 字段是非唯一索引，不具有唯一性，所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不相同了，出现了幻读现象。

读者问题：这里的查询条件如果是 age <= 22，验证了一下确实都是临键锁，不过感觉 age=39 那里加一个间隙锁就可以避免幻读了，为什么 age=39 也要加临键锁额外的把 39 也锁住了。

回答：这里属于能优化，但是 mysql 没有做这方面的优化，具体原因未知，官方文档也没解释，大概率是作者忘记了😂

没有加索引的查询

当前读查询语句没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是当前读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表

问题：为什么 select…for update/ update / delete 这些语句的查询语句没有走索引就会对每一条记录的索引上加 next-key 锁（锁全表）呢？

答案：是为了防止在事务执行期间，其他事务修改或删除某一条记录，或者在其前后插入新记录，从而出现了不可重复读和幻读的问题。

死锁

死锁的发生

我建了一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB;

然后，先 t_order 表里现在已经有了 6 条记录：

假设这时有两事务，一个事务要插入订单 1007，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录，过程如下：

对于 A 事务而言，因为表中最大记录是 1006，所以会对 (1006, +∞] 加 next-key 锁

对于 B 事务而言，也是会对 (1006, +∞] 加 next-key 锁

两个事务在插入的时候都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

当发生超时后，就出现下面这个提示：

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。