对比上一篇,这篇聊聊【读已提交】隔离级别下,唯一索引冲突怎么加锁。
>作者:操盛春,爱可生技术专家,公众号『一树一溪』作者,专注于研究 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>