主键冲突，失败的insert变成共享锁，导致死锁，经典案例随时复现

欣辰守护者

已于 2023-11-11 14:59:17 修改

阅读量3.3k

点赞数 4

文章标签： mysql 数据库

于 2023-06-09 23:02:53 首次发布

本文链接：https://blog.csdn.net/songjiweiliu/article/details/131136171

版权

当INSERT语句在MySQL中遇到唯一键冲突时，会将排他锁转换为共享锁以防止死锁，保证数据的一致性和隔离性。文章通过实例展示了这一过程，并提供了查看锁信息及死锁日志的方法，进一步解释了死锁的原理和如何利用共享锁制造死锁。

摘要由CSDN通过智能技术生成

INSERT语句变成共享锁原因

在MySQL中，当我们使用INSERT语句向表中插入数据时，如果插入的数据与已有的数据主键或唯一索引发生冲突，就会产生唯一键冲突错误（Duplicate Key Error）。此时，MySQL会将该操作转换为一个SELECT语句，并且将产生的排他锁（Exclusive Lock）转换为共享锁（Shared Lock）。

这种转换的原因是为了保证数据的隔离性和一致性。具体来说，当我们使用INSERT语句插入数据时，MySQL会根据数据表的唯一索引来创建一条排他锁，这样可以确保其他事务不能同时修改该数据。但是，当我们插入的数据与已有的数据发生冲突时，我们需要回滚当前事务并进行重试。为了避免出现死锁等问题，MySQL会将排他锁转换为共享锁，这样其他事务就可以读取该数据了，而不会被阻塞或死锁。

需要注意的是，该操作只是在发生唯一键冲突时才会执行。如果没有冲突，INSERT操作仍然会持有排他锁，以保证数据的完整性和一致性。

总之，当INSERT语句产生唯一键冲突时，MySQL会将产生的排他锁转换为共享锁，以避免出现死锁等问题，保证数据的隔离性和一致性。

原本是一次生产死锁，本次极简演示，尽可能通俗易懂。

注意隔离级别是rc，读已提交。可通过下列sql设置当前会话隔离级别为rc。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

本次排查用到mysql8.0 sql 工具

1.发生死锁时，查看最后一次死锁的日志。

show engine innodb status;

查出来结果，直接复制到文本中，搜索DEADLOCK，即可找到死锁的日志。

2.分析sql时，查看当前系统中所有锁的信息

SELECT ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE, LOCK_MODE,LOCK_STATUS,LOCK_DATA 
FROM performance_schema.data_locks;

建表语句

CREATE TABLE `course_2` (
  `cid` bigint NOT NULL AUTO_INCREMENT,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1666491309009006594 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

提前插入一条数据。cid=1。

INSERT INTO coursedb.course_2 (cid, cname, user_id, cstatus) 
VALUES(1, 'java', 1001, '1');

insert本是排他锁，现在我们证明下插入主键冲突会降级为共享锁。证明直接执行两个cid一致insert，就会发现后执行的insert被阻塞即可证明。

操作1，证明 insert 插入主键冲突会降级为共享锁

事务1 依次执行

begin;
INSERT INTO course_2 (cid, cname, user_id, cstatus) 
VALUES(1, 'java', 1001, '1');

此时报错

此时再开个窗口执行

SELECT ENGINE,ENGINE_TRANSACTION_ID,THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE, LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

可以看到insert由于主键冲突变成了共享锁。而且第一个记录，意向写锁（IX锁），更能证明，本来是排它锁（X），然后失败变共享锁（S）了。

为了达到更清晰的认识，可以继续走下面步骤

事务2 依次执行

begin;
select * from course_2  where cid=1 lock in share mode ;
select * from course_2  where cid=1 for update;

当执行到共享锁，能查到cid=1的数据，执行到for update会被事务1，阻塞。

因为共享锁可以正常执行，排它锁阻塞，所以判定事务1获取的是共享锁。

然后事务1 执行

commit;

提交事务后，事务2 for update能正常查询。

注意最后事务2也commit下。释放排他锁。

事务2 执行

commit;

操作2，利用共享锁制造死锁

事务1 依次执行

begin;
INSERT INTO course_2 (cid, cname, user_id, cstatus) 
VALUES(1, 'java', 1001, '1');

此时制造了锁住cid=1记录的共享锁。

事务2 一次执行

begin;
select * from course_2  where cid=1 for update;

事务2获取排它锁，因为事务1持有共享锁，阻塞中。

事务1 执行

select * from course_2  where cid=1 for update;

事务1 持有共享锁，尝试获取排他锁。

此时看事务2，报死锁。

死锁产生了。

这是可以用下面sql查看死锁日志。

show engine innodb status;

死锁原理

当事务1获取到共享锁。这时，事务2执行"select * from course_2 where cid=1 for update"语句来获取该行的排他锁，就会被阻塞，因为该行已经被事务1持有了共享锁。而当事务2持有该行的排他锁时，如果事务1再执行"select * from course_2 where cid=1 for update"语句来获取该行的排他锁，也会被阻塞，因为该行已经被事务2持有了排他锁。
这样，事务1和事务2就相互等待对方释放所持有的锁，形成了死锁。如果不采取相应的措施来解除死锁，这两个事务就会一直被阻塞，无法继续执行，从而导致系统性能下降和数据不一致等问题的发生。不过我的数据库开启了死锁检测。所以直接回滚了事务2.

推荐锁的基础知识

MySQL(十三)：小一万字+14张图读懂锁机制_星河之码的博客-CSDN博客