mysql insert duplicate key update 死锁分析

背景

数据入库这块有离线和实时两套入库系统，写同一个db的同一批mysql表，两边用的都是insert into table on duplicate key update这种方式。实时一直运行，离线5分钟更新一次，当两套系统同时运行时出现了死锁问题，频率还挺高。事务的隔离级别是read committed 读提交。

原因分析&解决方案

这里面分两种情况，一种是带主键的insert duplicate key update，一种是没有主键带唯一索引的insert duplicate key update。

1、带主键的insert duplicate key update

实时入库的batch大小是1w，离线入库的batch大小也是1w，为了提高入库效率 ，两边都开启了事务。当两边同一批插入的数据中包含了相同的数据，且顺序不一致，此时会出现死锁。比如说实时任务当前插入的数据包含id为1，2的数据，离线任务当前插入的数据包含2，1。此时实时任务获取了id为1数据的锁，等2的数据锁，离线任务获取了id为2的数据锁，等id为1数据锁，产生了死锁。

这种情况比较简单，处理方式有两种：

1、减少batch大小，减少了同一批数据中包含相同数据的概率，也就减少了死锁发生的概率

2、入库前对sql按照id排序，即使出现相同数据，只要保证顺序相同就不会出现死锁

2、带唯一索引的insert duplicate key update

https://developer.aliyun.com/article/727076 这篇文章讲的很清楚，这里我大致聊一下。 insert相关的有四种锁。

• record锁，也就是锁一条记录，这时其他事务读写不了当前加锁的记录。
• gap锁，锁一个区间。比如说db中有1，3，5，那么对应的区间就有(-∞,1),(1,3),(3,5),(5,+∞)，该锁可以锁住上面四个区间的任何一个或者多个。
• next-key锁，锁一条记录和小于该记录最近的一个gap，本质上就是record锁加上gap锁。比如说db中有1，3，5，那么对应的区间就有(-∞,1],(1,3],(3,5],(5,+∞]，该锁可以锁住上面四个区间的任何一个或者多个。
• Insert Intention锁（插入意向锁）：这个锁是在数据插入之前会加此锁。它是一种轻量的Gap锁，同时也是意向排他锁的一种。它的存在使得多个事务在写入不同数据到统一索引间隙的时候，不会发生锁等待。

插入唯一索引时判断索引是否存在。如果不存在，会在数据所属的gap添加Insert Intention锁。由于意向锁是轻量gap锁，这时即使有同gap的其他数据插入，两者之间不会互相影响。

如果插入的索引已经存在，会在索引值位置插入next-key锁。next-key锁包含record锁和gap锁，gap锁会锁一个范围，锁住期间，其他事务不能向该范围插入数据。如果此时两个事务各自获取到了一个next-key锁，此时都需要向对方获取到的next-key锁范围插入数据，就会出现死锁。

举例： code字段有唯一索引。初始数据：insert into test2 (code, other) values(1,1),(3,3),(5,5) 事务1: insert into test2(code,other)values(3,3) on duplicate key update other=values(other); 由于code为3的数据已经存在，所以会在(1,3]这个范围加next-key锁。 事务2: insert into test2(code,other)values(5,5) on duplicate key update other=values(other); 由于code为5的数据已经存在，所以会在(3,5]这个范围加next-key锁。 如果此时事务1插入一条code为4的数据，那么此时会等待事务2 (3,5]的next-key锁释放； 如果同时事务2插入一条code为2的数据，那么此时会等待事务1 (1,3]的next-key锁释放； 形成死锁。

解决方案： 1、减少batch的大小，单个事务获取到的next-key锁的范围就会变少，减少死锁的概率。 2、重试。 3、插入数据时添加主键。如果插入数据时带上主键，那么就不会产生next-key锁，会退化到第一种情况（带主键的insert duplicate key update）。