mysql update 死锁讲讲insert on duplicate key update 的死锁坑- 鲸跃云

1.背景

最近有一些活动，于是会对系统做一些平时量比较小的路径做一些打压，这不打压还好，这一打压就出现了奇怪的问题，居然有一段陈年老代码出现了死锁的问题，日志如下：

mysql update 死锁_mysql update 死锁_mysql update 死锁

看见了日志之后，就踏上了死锁的排查之路。当然如果你对锁不是很熟悉的话你可以先看我的这两篇文章看一下数据库锁的基础知识: 为什么开发人员必须要了解数据库锁：和记一次神器的mysql死锁排查

2.问题分析

数据库代码如下：

代码语言：javascript

复制

CREATE TABLE `order_extrainfo` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `orderId` int(11) NOT NULL,  `extraInfo` text NOT NULL,  `appProductId` int(11) NOT NULL DEFAULT '0',  `hostAppProductId` int(11) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),  UNIQUE KEY `orderId` (`orderId`)) ENGINE=InnoDB AUTO_INCREMENT=17835265 DEFAULT CHARSET=utf8mb4;

出现问题的sql语句如下：

代码语言：javascript

复制

INSERT INTO `order_extrainfo` (orderId, " +                            "extraInfo, appProductId, hostAppProductId) VALUES (?,?, ?, ?) ON " +                            "DUPLICATE KEY UPDATE extraInfo = ?, appProductId = ?, " +                            "hostAppProductId = ?

之前没有遇到过insert出死锁的情况，所以当时觉得是on dpulicate key update导致的。为了找到当时的死锁现场，输入：SHOW ENGINE INNODB STATUS;查看最近一次死锁日志：

mysql update 死锁_mysql update 死锁_mysql update 死锁

事务1和事务2明显都在等待gap锁的释放，应该是互相持有gap锁，都在等待对方导致。

一般的死锁日志都是由两个事务导致的，所以会给予一定的迷惑性，其实大部分的死锁都是由两个以上的事务导致的，这次其实也不例外,这其实是mysql的一个bug，，有兴趣的可以看一下。

注意：session1,2,3 具体每个orderId 是依次递增的

session3 出现了死锁。

session1,2,3 的这个执行顺序在我们的高并发的时候是很容易出现的，所以才会大量出现死锁报错。

2.1 锁分析

这里我们来具体分析一下到底加了什么锁，我们知道insert插入操作的时候会加 X锁和插入意向锁，这里我们看看 insert into on duplicate key加什么锁,

这个是在我本地电脑进行测试，首先打开：

set GLOBAL innodb_status_output_locks=ON;

set GLOBAL innodb_status_output=ON;

mysql的锁统计，这个线上不推荐打开打开的话日志会记录得比较多。

首先执行第一个sql：

INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360183,'', 0, 0) ON DUPLICATE KEY UPDATE extraInfo = '';

输入show engine innodb status命令查看

mysql update 死锁_mysql update 死锁_mysql update 死锁

加锁情况如上图所示，这里要说明的是 insert intention 在这里是隐式锁，这里加的锁实际上就是x + GAP(负无穷到正无穷的gap锁) + insert intention 三个锁

这里我们在执行执行第二个sql，

INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360184,'', 0, 0) ON DUPLICATE KEY UPDATE extraInfo = '';

mysql update 死锁_mysql update 死锁_mysql update 死锁

发现其插入意向锁正在被gap锁阻塞。

同样的如果我们执行第三个sql，插入意向锁也会被第一个事务gap锁阻塞，如果第一个事务的gap锁提交，他们首先又会先获取gap锁(这里从锁的信息判断，被阻塞的时候是没有gap锁)，其次再获取插入意向锁，就导致了session2,session3两个形成循环链路，最终导致死锁。

mysql update 死锁_mysql update 死锁_mysql update 死锁

2.2 为什么会有gap锁

gap锁是RR隔离级别下用来解决幻读的一个手段，一般出现在delete中，为什么会出现在这里呢？在这个bug中可以看见：

“Concurrent “INSERT …ON DUPLICATE KEY UPDATE” statements run on a tablewith multiple unique indexes would sometimes cause events to be written tothe binary log incorrectly””

当我们并发的用INSERT …ON DUPLICATE KEY UPDATE的时候，如果我们有多个唯一索引，那么有可能会导致binlog错误，也就是会导致主从复制不一致，具体的一些测试可以去链接中查看

3.如何解决

如果遇到这个问题怎么办呢？我们有下面的一些方法来解决这个问题：

如果大家觉得这篇文章对你有帮助，你的关注和转发是对我最大的支持，O(∩_∩)O:

原创文章，作者：筱凯，如若转载，请注明出处：https://www.jingyueyun.com/ask/834.html

mysql update 死锁 讲讲insert on duplicate key update 的死锁坑

相关推荐

发表回复

mysql update 死锁讲讲insert on duplicate key update 的死锁坑