MySQL中，遇到唯一键冲突时（与表中原有数据冲突）会如何加锁

aaajjjghjgh

已于 2024-08-08 21:45:05 修改

阅读量694

点赞数 8

文章标签： mysql 数据库

于 2024-08-08 21:33:18 首次发布

本文链接：https://blog.csdn.net/aaajjjghjgh/article/details/141024396

版权

文章目录

一、情况分类
二、实验结果
三、总结

背景描述：本人在小林coding的”图解MySQL”-“锁篇”中发现了一个奇怪的描述，如下所示：
在这里插入图片描述

MySQL死锁了，怎么办？

在事务状态下，插入新记录时，如果碰到了唯一键冲突（与表中原有的数据冲突），添加S型锁（共享锁）可以理解，毕竟数据冲突了，新插入记录和老记录至少有一个是有问题的，对这条老记录添加共享锁可以防止查询这条老数据时被其他事务更改。对于主键索引重复，添加记录锁即可，但对于唯一二级索引重复，这里有两个问题：1、为什么要区别于主键冲突，添加next-key锁（临键锁）；2、此时是否也会给某些主键索引加锁。

关于这两个问题，网上讨论的不多，也没有找到非常合理的解释，所以本人决定针对唯一键冲突的一些情况进行实验，并对结果进行记录与讨论。

实验配置：MySQL8.4，存储引擎InnoDB，隔离级别Repeatable Read（可重复读）。
下文中的S型锁指共享锁，X型锁指独占锁。

一、情况分类

本次实验主要对以下几种情况进行分析：
在这里插入图片描述
实验所用表采用如下设置：

CREATE TABLE `test1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` int  NOT NULL,
`age` int,
PRIMARY KEY (`id`),
UNIQUE KEY `index_name` (`name`) 
) ENGINE=InnoDB AUTO_INCREMENT=6;

初始数据：
在这里插入图片描述

二、实验结果

1、主键索引冲突

使用表test1进行实验：
在这里插入图片描述

使用如下命令查看锁的情况：

select * from performance_schema.data_locks\G;

在这里插入图片描述
可以看到，在事务中主键冲突时，会对冲突的主键加一个S型记录锁，对这张表加一个X型意向锁。

对冲突的主键加S型记录锁可以防止该记录被更改，那为什么不加X型锁呢？个人猜测其中有这样的逻辑：主键冲突，那么这条记录大概率有问题，其他事务也有很大可能对这条记录进行insert，众所周知，S型锁与S型锁不冲突，这样别的事务也会知道这条记录有问题，但如果加的是X型锁，那么别的事务insert时会被阻塞，短时间内就看不到冲突的报错，不利于问题反馈。

另外，为什么会给这张表加一个X型意向锁呢？因为按正常来讲，给记录加S型锁，应该给表也加S型的意向锁。
在这里插入图片描述
这么做最直观的现象就是后续对这张表加S型锁和X型锁时会被阻塞，个人推测：这张表中有冲突的数据问题，因为不能确定冲突的老记录以及其记录是否正确，所以在表级别最好不要进行读、写操作。

对主键非自动递增的表也做了相同的实验，发现insert时产生了主值索引冲突也会加一样的锁。

2、唯一二级索引冲突

使用表test1进行实验：
在这里插入图片描述
使用如下命令查看锁的情况：

select * from performance_schema.data_locks\G;

在这里插入图片描述
这里首先明确，对于next-key锁，LOCK_DATA显示的是区间右边的值，其区间左边的值是对应索引排序后前面的那个值。上图中的7，8指的是二级索引name为7，主值id为8；supremum pseudo-record是一条伪记录，相当于代表最大值正无穷。正无穷那里之所以是闭区间是为了迎合next-key锁左开右闭的区间。

可以看到，在事务中唯一二级索引冲突时，会对冲突的唯一二级索引加一个S型next-key锁，对主键值索引加一个(当前最大主键值，+∞]的X型next-key锁，对这张表加一个X型意向锁。

为什么这里不像主键值索引冲突那样给唯一二级索引加一个S型记录锁呢？同时，为了保险起见，还可以给相应的主键值8也加一个S型记录锁。MySQL之所以没有这么做，而是选择对冲突的唯一二级索引值加一个S型next-key锁，肯定是有相应的考量。我查阅了许多网站，也询问了ChatGPT，都没有一个很好的解释。
首先，我们要明白S型记录锁和S型next-key锁的区别，很显然，next-key锁多了一段前一个索引（排序后）到目标索引的区间。那么问题就变成了为什么在唯一二级索引冲突时要对这段区间也加锁？或者为什么在主键冲突时不对这段区间加锁？
对于唯一二级索引，我们思考一下在日常业务中一般会代表什么。时间？排名？身份id？抑或是一些其他不容易有冲突值的属性。此时你脑海中浮现出的大部分属性是不是有一个前值优先的特性，比如时间、排名等等，这些属性在排序后一般是排在前面的具有更强烈的重要性。例如，排名在前面的人会享受更多的福利，时间排在前面的人拥有优先权等等。所以，对前面的区间加锁相对来说可以对前值提供一个保护，后续查找错误时也可以优先核对前面的区间。这时可能有人要问：既然如此，为什么不对前面的两个、三个甚至更多区间都加锁呢，那不是更保险吗？要知道，加锁这一操作虽然可以用来规避风险，但也会影响其他事务的进行，我们进行加锁操作的宗旨就是在规避风险的同时尽可能不影响其他事务。这时又有另一个问题，为什么不对后面的区间加锁呢？原因很简单，首先，排在前面的记录有较大概率比后面的记录重要，其次，如果这个出错的索引是当前最大值，那么对后面区间加锁相当于对这个值到正无穷区间加锁，所有在此范围内的记录在后续插入时都会被阻塞。
综合取舍后，MySQL给出的结果就是只对当前记录和前一段区间进行加锁。那为什么在主键冲突时不对这段区间加锁呢？因为主键不像唯一二级索引那么容易具有前值优先的特性，试想一下，对于身份id这样的属性，是不是更容易作为主键呢？或者在一些业务中，干脆不用实际属性作为主键，而是用自动递增的INT值，这样的话，更没必要对主键的区间加锁。
以上分析均属个人推测，因为暂时想不到一个“不得不”这么做的理由，所以只能从定性的角度来分析，如果你有耐心看完上面这几段文字，欢迎在评论区交流讨论。

话说回来，MySQL除了对冲突值的唯一二级索引加一个S型next-key锁，还对主键值索引加了一个(当前最大主键值，+∞]的X型next-key锁，这又是为什么呢？
对于这个问题，要注意到此时的主键是设置为自动递增的（AUTO_INCREMENT），也就是说插入时会自动给这条记录分配当前最大主键值+1的id作为主键，而这条记录因为唯一二级索引冲突而报错了。虽然主键值是合法的，但作为一条有问题的数据，主键值也应当被锁起来，因为可能后续核实清楚冲突问题后，该事务还会给这个主键值加一个不冲突的唯一二级索引，所以对于主键值也应该进行加锁，注意此时这个主键实际不存在（因为这条数据插入失败了），为了预留这个主键值，需要给含有这个值的区间加锁，又因为只能给存在的主键值加锁，所以在当前情况下，实际加锁的主值区间为(当前最大主键值，+∞]。在此基础上合理推测，唯一二级索引冲突导致插入失败时，如果主键的值不冲突，也就是这个主键实际不存在表记录中，那么此时给主键加锁的情况只有两种，间隙锁和next-key锁，且next-key锁只会出现在主键值大于记录中最大主键值的情况，其余情况均是间隙锁。众所周知，间隙锁的X型和S型实际区别不大，因为间隙之间一般不会有实际存在的数据，否则加锁时会对这个实际数据加记录锁，也就是说一般不存在读数据的需求，而X型和S型都不允许写数据。因此加X型锁并没有太大问题。
为了验证上述推测，我指定插入主键值小于当前记录中的最小主值，唯一二级索引仍然冲突，那么给主键值加的锁应该是(-∞,当前最小主值)的X型间隙锁。实验如下，跟推测的结果一致：
在这里插入图片描述
对于表级X型意向锁，在主键索引冲突中已进行了推测，不再赘述。

对主键非自动递增的表也做了相同的实验，发现insert时产生了唯一二级索引冲突也会加一样的锁，相当于指定主键插入值的情况。

3、主键索引和唯一二级索引同时冲突

依据MySQL的索引检查规则，在insert的记录同时包含主键索引和唯一二级索引时，优先检查主键索引，这时候发现主键索引冲突了，会直接返回主键的重复错误，所以只会对主键值加一个S型的记录锁，对这张表加一个X型意向锁。

使用表test1进行实验：
在这里插入图片描述
使用如下命令查看锁的情况：

select * from performance_schema.data_locks\G;

在这里插入图片描述
显然，结果与主键索引冲突的情况一样。

三、总结

在事务中对某张表插入一条记录时，如果与表中原有数据产生唯一键冲突，可能会有如下三种情况：

1、主键索引冲突：主键冲突时，会对冲突的主键加一个S型记录锁，对这张表加一个X型意向锁。
2、唯一二级索引冲突：唯一二级索引冲突时，会对冲突的唯一二级索引加一个S型next-key锁，对主键值索引加一个X型间隙锁或next-key锁，对这张表加一个X型意向锁。
3、主键索引和唯一二级索引同时冲突：同时冲突时，对主键值加一个S型的记录锁，对这张表加一个X型意向锁，与主键索引冲突时的情况一样。

其实还有非索引的唯一列冲突导致的插入失败，实验后发现加锁逻辑与本文的分析一致：优先检查主键，其次检查唯一二级索引，最后检查非索引的唯一列。在这三个步骤的依次进行过程中如果发现冲突就不会进行后面的检查，主键或唯一二级索引存在冲突的加锁情况文中已经描述，如果主键或唯一二级索引均不存在冲突，而非索引的唯一列存在冲突，则会对这个非索引的唯一列的这个冲突值加S型next-key锁，对主键和唯一二级索引加X型的间隙锁或next-key锁，对这张表加一个X型意向锁，与前文中的唯一二级索引冲突的情况类似。