MySQL InnoDB中的锁-临键锁(Next-Key Lock)

内容目录

1. 概述

Next-Key锁称为临键锁,它是MySQL InnoDB引擎的一种锁定机制。Next-Key锁结合了记录锁和间隙锁的特性,为非唯一索引记录提供行级锁定。当SQL执行按照非唯一索引进行数据的检索时,InnoDB会给匹配到的行上加上Next-Key锁。

1.1. 锁定区间

Next-Key锁锁定的范围包括一个索引记录以及该记录前的间隙。
1.png

2. 实验

以下的实验基于MySQL 8.0.x版本。首先,我们创建一个表并插入一些记录:

CREATE TABLE `sys_user` ( 
`id` int NOT NULL AUTO_INCREMENT,
`age` int COMMENT '年龄',
PRIMARY KEY (`id`), 
KEY `idx_age` (`age`) USING BTREE COMMENT '年龄' ) ENGINE=InnoDB COMMENT='用户';

insert into sys_user (age)values (10);
insert into sys_user (age)values (11);
insert into sys_user (age)values (13);
insert into sys_user (age)values (20);

在这个表中,年龄的键值存在以下间隙区间:

  1. (负无穷大,10]
  2. (10, 11]
  3. (11, 13]
  4. (13, 20]
  5. (20, 正无穷大]

在这些间隙中,Next-Key锁会发挥作用。

2.1. 实验一:Next-Key锁的影响在锁定区间内插入

插入9,临建锁区间范围:1. (负无穷大,10]
考虑以下的事务A和事务B:
事务A:

begin;
-- 这里使用for update是使用当前读
select * from sys_user where age=10 for update;

事务B:

begin;
insert into sys_user (age) values (9);

在这个例子中,事务B会被阻塞,无法插入,直到事务A提交或者回滚。
如下图所示,事务B因为获取锁超时而被MySQL回滚:
file

2.2. 实验二:Next-Key锁的影响在锁定区间外插入

插入11,临建锁区间范围:1. (负无穷大,10],可以正常插入
考虑以下的事务A和事务B:
事务A:

begin;
-- 这里锁定的是(负无穷大,10]
select * from sys_user where age=10 for update;

事务B:

begin;
insert into sys_user (age) values (11);

在这个例子中,事务B能够正常插入,不会被阻塞,如下图所示:
file

2.3. 实验总结

Next-Key锁的工作原理是锁定给定索引记录以及该记录之前的间隙。这种锁可以防止其他事务在这个间隙中插入新的行,以此来达到防止幻读的目的。

2.3.1. 为什么实验一被临建锁锁定?

在"实验一"中,事务A执行了一个带有锁的查询,选择年龄为10的记录并对其进行"for update"的操作。在InnoDB存储引擎中,这个"for update"操作会给匹配的数据库行添加一个Next-Key锁。

Next-Key锁的功能是锁定一个记录以及前面的间隙,防止其他事务在这段间隙添加新的记录。在这个例子中,"age=10"这个记录的前面的间隙被定义为(负无穷,10]。

当事务B尝试插入年龄为9的新记录时,这个记录位于等于10的记录前面的间隙中,因此被Next-Key锁阻止了。无法插入,直到事务A完成(提交或者回滚)并释放了锁。

这就是为什么在"实验一"中,事务B会被锁定的原因。因为Next-Key锁保护了特定的记录和间隙不受干扰,防止其他事务在这些间隙中插入新的记录。

2.3.2. 为什么实验二可以正常插入?而没有被临建锁锁定?

在"实验二"中,事务A锁定了年龄为10的行,因此锁定的间隙是(负无穷大,10]。这意味着年龄介于负无穷大到10(不包含10)之间的新行不能被插入。

然而,事务B尝试插入的新行年龄为11,这个值没有落在被锁定的间隙内,所以没有被锁阻止,所以它可以正常插入。

这就是为什么在"实验二"中,尽管事务A正在进行并锁定了一些行,事务B仍然可以插入新行的原因。因为Next-Key锁只锁定指定的间隙,不在这个范围内的行插入操作不会受到影响。

3. 注意事项

为了更好地使用Next-Key锁,以下是一些建议:

  1. 尽可能让所有的查询都使用主键或者唯一索引,这样可以避免Next-Key锁,预防对记录大面积的锁。
  2. 在’REPEATABLE READ’隔离级别下,当我们根据非唯一索引来检索数据时,InnoDB会使用Next-Key锁来防止幻读问题。

4. 参考

  1. MySQL官方文档

发表评论

您的电子邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部