问 MySQL 的 RR 隔离级别插入记录,唯一索引冲突加什么锁?

原创
09/11 17:27
阅读数 522

这篇聊聊可重复读隔离级别下,唯一索引冲突怎么加锁。

>作者:操盛春,爱可生技术专家,公众号『一树一溪』作者,专注于研究 MySQL 和 OceanBase 源码。 > >爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

> 本文基于 MySQL 8.0.32 源码,存储引擎为 InnoDB。

1. 准备工作

创建测试表:

CREATE TABLE `t4` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `i2` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入测试数据:

INSERT INTO `t4` (`id`, `i1`, `i2`) VALUES
(1, 11, 21), (2, 12, 22), (3, 13, 23),
(4, 14, 24), (5, 15, 25), (6, 16, 26);

把事务隔离级别设置为 REPEATABLE-READ(如已设置,忽略此步骤):

SET transaction_isolation = 'REPEATABLE-READ';

-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

2. 加锁情况

t4 表除了有主键索引,i1 字段上还有个唯一索引 uniq_i1,有一条 <i1 = 12> 的记录。

我们执行以下 insert 语句,再插入一条 <i1 = 12> 的记录。

begin;
insert into t4(i1, i2) values (12, 2000);

因为新插入记录和表中原有记录存在唯一索引冲突,报错如下:

(1062, "Duplicate entry '12' for key 't4.uniq_i1'")

执行以下 select 语句查询加锁情况:

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't4'
 and lock_type = 'RECORD'\G
 
***************************[ 1. row ]***************************
engine_transaction_id | 247919
object_name           | t4
index_name            | uniq_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 12, 2
***************************[ 2. row ]***************************
engine_transaction_id | 247919
object_name           | t4
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | X
lock_status           | GRANTED
lock_data             | supremum pseudo-record

从以上加锁情况可以看到,insert 语句执行过程中对 2 条记录加了行锁。

lock_data = 12,2,lock_mode = S 表示对唯一索引 uniq_i1 中 <i1 = 12, id="2"> 的记录加了共享 Next-Key 锁。

lock_data = supremum pseudo-record,lock_mode = X 表示对主键索引某个数据页中的 supermum 记录加了排他 Next-Key 锁。

3. 原理分析

3.1 主键索引

主键索引某个数据页中的 supremum 记录被加了排他 Next-Key 锁,这也太奇怪了。

supremum 记录似乎和新插入记录是八杆子打不着的关系,怎么就给加上锁了呢?

俗话说:事出反常必有妖。

现在让我们来一探究竟,看看妖从哪里来。

示例 SQL 中,我们插入了一条 <i1 = 12, i2="2000"> 的记录,没有指定 id 字段值。

id 是自增字段,插入记录时,我们没有指定 id 字段值,MySQL 会自动为我们生成。

我们可以根据表中数据推测出,新插入记录的 id 字段值为 7。

那么,我们插入的完整记录为 <id = 7, i1="12," i2="2000">

插入新记录之前,主键索引数据页中各记录的逻辑顺序如下。

+----+----+----+
| id | i1 | i2 |
+----+----+----+
|   infimum    |
| 1  | 11 | 21 |
| 2  | 12 | 22 |
| 3  | 13 | 23 |
| 4  | 14 | 24 |
| 5  | 15 | 25 |
| 6  | 16 | 26 |
|   supremum   |
+----+----+----+

insert 语句插入记录时,会先插入记录到主键索引,再插入记录到二级索引。

插入记录到唯一索引 uniq_i1 中发现存在冲突时,主键索引中已经插入记录成功。此时,主键索引数据页中各记录的逻辑顺序如下。

+----+----+------+
| id | i1 | i2   |
+----+----+------+
|    infimum     |
| 1  | 11 | 21   |
| 2  | 12 | 22   |
| 3  | 13 | 23   |
| 4  | 14 | 24   |
| 5  | 15 | 25   |
| 6  | 16 | 26   |
| 7  | 12 | 2000 |
|    supremum    |
+----+----+------+

InnoDB 插入记录到唯一索引 uniq_i1 中发现存在冲突,也就不能继续插入了,但是,主键索引中已经插入记录成功,要怎么办呢?

那必须要把主键索引恢复原样,也就是要删除刚刚插入到主键索引的记录。

删除记录时,InnoDB 发现这条记录没有被显式加锁,并且记录的 DB_TRX_ID 字段值对应的事务还没有提交,说明这条记录上存在隐式锁。

因为要删除这条记录,为了防止其它事务读写这条记录,InnoDB 会把记录上的隐式锁转换为显式锁。

前面介绍隐式锁转换时,我们知道隐式锁会转换为排他普通记录锁,也就是 X,REC_NOT_GAP

隐式锁转换为显式锁之后,接下来就要准备删除这条记录了。

此时,InnoDB 又发现了一个问题:这条记录上已经有了锁,如果删除记录,上面的锁就无依无靠了。

因为行锁必须依附于某条记录,现在,要删除这条记录,只能让它的下一记录来继承这条记录的锁。

下一条记录有点傲娇,并不会照单全收。它只会继承这条记录的锁模式,不继承精确模式。也就是说,它只继承 X,不继承 REC_NOT_GAP。

继承锁模式(X)之后,再加上自己的精确模式(GAP),它要加的锁是这样的:X,GAP

确定要加的锁之后,就开始走加锁流程了。

加锁流程里,InnoDB 发现加锁的是 supremum 记录,这就命中了一个规则:所有 supremum 记录,不管原来要加什么锁,统一变成 Next-Key 锁。

我们查询加锁情况,看到 supremum 记录对应的 lock_mode 就是 X。

好了,到这里,主键索引 supremum 记录加排他 Next-Key 锁的逻辑就搞清楚了。

3.1 唯一索引

前面介绍插入记录导致主键索引冲突的加锁情况时,我们介绍过,insert 语句插入记录到索引(包括主键索引、二级索引)之前,需要先找到插入记录的目标位置。

示例 SQL 插入到唯一索引 uniq_i1 中的记录为 <i1 = 12, id="7">。

找到插入记录的目标位置是 <i1 = 12, id="2"> 这条记录之后,此时,InnoDB 也就发现了表中已经存在 <i1 = 12> 的记录。

因为 i1 字段上有唯一索引,自然不允许再插入一条 <i1 = 12> 的记录了。

根据 insert 语句的执行结果来看,此时应该报错: Duplicate entry xxx。

然而,这只是我们的美好愿望。对 InnoDB 来说,路要一步一步走,不能直接报错,例行检查工作还是要做的。

首先要做的检查工作,就是看看新插入记录中,是否有哪个唯一索引字段值为 NULL。

为什么要做这样的检查呢?

因为对于用户普通表(使用 create table 语句创建的表),NULL 和 NULL 被认为不相等。

也就是说,插入到用户普通表中唯一索引的多条记录,如果唯一索引的任何一个字段值为 NULL,这些记录都可以插入。

现在,我们继续回到例行检查工作中。

如果新插入记录中,唯一索引的任何一个字段值为 NULL,InnoDB 就认为新插入记录和表中已有记录不冲突,检查工作到此结束。

否则,继续进行下一项检查工作,就是看看表中已发现的 <i1 = 12, id="2"> 这条冲突记录是否为有效记录。

如果已发现的冲突记录被标记删除了,只是还没有被清理,说明它不有效记录,新记录可以插入。

如果已发现的冲突记录是有效记录,新记录就不能插入了,要报错。

为了防止其它事务更新或者删除这条记录、或者往这条记录前面的间隙里插入记录,开始例行检查工作之前,InnoDB 会对这条记录加共享 Next-Key 锁。

这就是示例 SQL 执行过程中对 <i1 = 12, id="2"> 的记录加共享 Next-Key 锁的原因。

4. 总结

没有需要总结的内容。

更多技术文章,请访问:https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。

✨ Github:https://github.com/actiontech/sqle

📚 文档:https://actiontech.github.io/sqle-docs/

💻 官网:https://opensource.actionsky.com/sqle/

👥 微信群:请添加小助手加入 ActionOpenSource

🔗 商业支持:https://www.actionsky.com/sqle</i1></i1></i1></i1></i1></i1></id></i1></i1></i1></i1>

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
0 收藏
0
分享
返回顶部
顶部