一、问题复现

表结构：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alias` int(11) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_idx_alias` (`alias`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

事务隔离级别为MySQL默认的RR（可重复读）

数据库版本：5.7.20

插入数据：

insert into test(alias,age) values(1,1),(3,3),(5,5),(7,7);

死锁前提条件：多个并发同时执行insert into on duplicate update xxx 判断唯一键是否存在，存在更新数据

二、锁机制分析

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

首先打开：

set GLOBAL innodb_status_output_locks=ON;
set GLOBAL innodb_status_output=ON;

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

首先执行第一个SQL，输入show engine innodb status命令查看。

在执行执行第二个SQL，发现其插入意向锁正在被gap锁阻塞。

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

三、为什么会有gap锁

gap锁是RR隔离级别下用来解决幻读的一个手段，一般出现在delete中，为什么会出现在这里呢？这其实是一个BUG，在 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错误，也就是会导致主从复制不一致，具体的一些测试可以去链接中查看。

三、关于gap锁介绍

间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁。根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

1. 防止间隙内有新数据被插入。
2. 防止已存在的数据，更新成间隙内的数

示例数据：

+----+------+
| id | n    |
+----+------+
| 1  | 1    |
| 3  | 102  |
| 5  | 105  |
| 7  | 107  |
| 9  | 109  |
+----+------+

如果事务A开启一个事务，执行select * from test where n = 105 for update会对 n 在 (102,105), (105, 107)的数据上锁。

四、解决方案

1、升级版本

由于是一个BUG，因此升级MySQL版本到5.7.35及以上版本即可解决，比如MySQL 8以上版本也没类似问题。

2、就是要用5.7版本

• 使用RC级别，RC隔离级别下不会有gap锁
• 在数据库表中只建立主键，不建立其他唯一索引。
• 不使用 insert on duplicate key update，使用普通的insert。根据业务场景判断ON DUPLICATE KEY UPDATE 是否有必要
  • 先insert 再捕获异常，然后进行更新
  • 使用insert ignore，然后判断 affected rows 是否是1，然后再决定是否更新。