讲讲insert on duplicate key update 的死锁坑

1.背景

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

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

2.问题分析

数据库代码如下：

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语句如下：

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;查看最近一次死锁日志：

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

一般的死锁日志都是由两个事务导致的，所以会给予一定的迷惑性，其实大部分的死锁都是由两个以上的事务导致的，这次其实也不例外,这其实是mysql的一个bug，https://bugs.mysql.com/bug.php?id=52020，

有兴趣的可以看一下。

对着bug中的描述，在本地开始复现一下这个场景：

时间线 | session1 | session2 | session3

---|--- |---|---|---

1 | begin; | | insert into xx

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

3 |1 row in affected; |     INSERT INTO order_extrainfo (orderId, extraInfo, appProductId, hostAppProductId) VALUES (158360184,'', 0, 0) ON DUPLICATE KEY UPDATE extraInfo = '';| begin;

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

5 | commit;|

6 | | 1 row in affected;|Deadlock found when trying to get lock; try restarting transaction

注意：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命令查看

加锁情况如上图所示，这里要说明的是 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 = '';

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

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

2.2 为什么会有gap锁

gap锁是RR隔离级别下用来解决幻读的一个手段，一般出现在delete中，为什么会出现在这里呢？在 https://bugs.mysql.com/bug.php?id=50413 这个bug中可以看见：

"Concurrent "INSERT …ON DUPLICATE KEY UPDATE" statements run on a table

with multiple unique indexes would sometimes cause events to be written to

the binary log incorrectly"

”

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

3.如何解决

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

• 使用mysql5.6版本，可以看见这个是在5.7中引入的，5.6中不会出现这个情况

• 使用RC级别，RC隔离级别下不会有gap锁

  -- 不要使用 insert on duplicate key update，使用普通的insert。我们最后使用的就是这个方法，因为ON DUPLICATE KEY UPDATE 这个在代码中的确是没有必要

• 在数据库表中只建立主键，不建立其他唯一索引。

• 先insert 再捕获异常，然后进行更新

• 使用insert ignore，然后判断update rows 是否是1，然后再决定是否更新。

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