MySQL 在 RC 隔离级别插入记录，唯一索引冲突加什么锁？

对比上一篇，这篇聊聊【读已提交】隔离级别下，唯一索引冲突怎么加锁。

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

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

目录 [TOC]

正文

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); 

把事务隔离级别设置为 READ-COMMITTED（如已设置，忽略此步骤）：

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

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 | 247925 object_name | t4 index_name | uniq_i1 lock_type | RECORD lock_mode | S lock_status | GRANTED lock_data | 12, 2 

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

和可重复读隔离级别不一样，读已提交隔离级别没有对 supremum 记录加排他 Next-Key 锁。

3. 原理分析

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

MySQL 会自动生成 id 字段值，根据表中数据可以推导出，新插入记录的 id 字段值为 7。

那么，我们插入的完整记录为 <id = 7, i1="12," i2="2000">，插入到唯一索引 uniq_i1 中的记录为 <i1 = 12, id="7">。

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

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

和可重复读隔离级别一样，InnoDB 发现表中已经存在 <i1 = 12> 的记录之后，并不会直接报 Duplicate entry xxx 错误，也需要进一步检查。

首先，会检查要插入到唯一索引中的记录，是否有哪个字段值为 NULL。

因为对于用户普通表，NULL 值和 NULL 值被认为不相等。

如果要插入的记录中存在值为 NULL 的字段，虽然从存储内容上来说，发现了同样的记录，但是也会被认为是不同的记录。这种情况下，新记录可以继续插入到唯一索引中。

也就是说，对于唯一索引 uniq_i1，可以插入任意条 <i1 = null> 的记录。

对于示例 SQL，因为 i1 字段值为 12，从这项检查来看，和表中 <i1 = 12, id="2"> 的记录冲突。

但是，InnoDB 还要再做最后一次尝试，看看表中 <i1 = 12, id="2"> 的记录是否已经被标记删除，只是还没有被清理。

如果表中 <i1 = 12, id="2"> 的记录已经被标记删除，新记录就可以继续插入到唯一索引 uniq_i1 中，否则，新记录不能插入，需要报错。

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

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

到这里就结束了吗？

当然不能就这么结束。

虽然读已提交隔离级别下，没有对主键索引中的 supremum 记录加锁，但是我们也不能把主键索引忘了。

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

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

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

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

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

当 InnoDB 准备开始转换时，发现当前事务的隔离级别为读已提交，后面的转换步骤就不再进行了，转换操作就此终止。

刚刚插入到主键索引的记录上，隐式锁没有被转换为显式锁，删除这条记录时，它的下一条记录（supremum 记录）也就不需要继承这条记录上的锁了。

所以，和可重复读隔离级别不一样，读已提交隔离级别没有对 supremum 记录加排他 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></i1></i1></i1></i1></id></i1></i1></i1></i1>