[笔记]MySQL 插入导致死锁

线上遇到的 MySQL 插入导致死锁,问题排查.

场景复现

MySQL 版本: 5.7.36
数据库存储引擎: InnoDB
事务隔离级别: REPEATABLE-READ

1. 创建测试表

DROP TABLE IF EXISTS `tb_task`;
CREATE TABLE `tb_task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11),
  `order_id` int(11),
  `tx` varchar(8),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_taskid_orderid` (`task_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 分别创建两个SQL会话,并开启事务

1. 在事务1 中执行插入语句1INSERT tb_task(task_id,order_id,tx)values(1,123,'tx_1');
2. 在事务2 中执行插入语句2INSERT tb_task(task_id,order_id,tx)values(1,213,'tx_2');
3. 在事务1 中执行插入语句2INSERT tb_task(task_id,order_id,tx)values(1,213,'tx_1');
4. 在事务2 中执行插入语句1INSERT tb_task(task_id,order_id,tx)values(1,123,'tx_2');
   

在执行完上面的步骤后,可以看到事务2由于发生了死锁异常而导致事务被回滚,而事务1 的插入语句1的SQL执行成功.

在执行插入语句的过程中你可能已经注意到在执行第3步插入SQL时事务1并没有立即返回而是发生了阻塞,在事务2由于死锁导致报错回滚后事务1才执行了返回。

其实我们可以根据阻塞进行推测是由于在执行第3步插入操作时事务1尝试获取由事务2持有的锁，而事务2还没有结束导致的阻塞，而事务2在尝试插入事务1已经插入的SQL时系统检测到事务1和事务2可能会出现互相持有锁的情况而发生死锁从而抛出死锁异常并回滚事务2，此时由于事务2被回滚，其持有的锁被释放，因此事务1顺利获取到了锁从阻塞状态返回。

我们此时提交事务1并查看表中数据，可以看到表中存在两条由事务1提交的数据。

我们可以使用SQL命令SHOW ENGINE InnoDB STATUS\G查看最近一次死锁产生的原因：

更详细的原因分析看参考资料吧，这里就不重复赘述了。

参考资料

MySQL:insert 加锁与死锁分析
mysql insert锁机制(博客园)
mysql insert锁机制(优快云)