当并发insert on duplicate key update遇见死锁：更新丢失

2022-03-10 02:45:28

数据库死锁问题，是一个老生常谈且很常见的问题，网上也有非常多对于各类死锁场景的解析和复现，但凡和死锁有关，无外乎不涉及数据库隔离等级、索引、以及innodb锁等相关原因。但是我这个案例我搜遍了全网也没能找到比较相似情况。于是我想尽可能的复现出这种情况，找出死锁的原因，找出可能出现的隐患。

问题的背景：

我们的数据库中的发生死锁的表是具有”多列组合构建的唯一索引“（不包含自增的主键），且数据库的隔离等级为Read Committed, 另外对于这个表来说是写入远大于读取的，由于业务的原因，经常会出现同一数据反复插入（同一数据指唯一索引值相同的数据，但其他非索引字段可能不同），所以为了简化代码，我们使用insert on duplicate key update来解决这种问题，当mysql检测到唯一键冲突时，仅更新特定（非索引）字段。但是问题就出现在大规模多worker并发插入的时候，会经常出现"Deadlock found when trying to get lock"。开始真的是百思不得其解，于是乎开始疯狂查阅mysql手册以及各类博文尝试找到问题所在。

问题的现象：

一般定位死锁原因第一步就是执行”show engine innodb status“, 查看innodb Standard monitor输出结果，这里面会有数据库最后一次的死锁记录。会记录出现死锁的两个事务，它们分别在等待什么锁，并且手里持有什么锁。mysql在检测到发生死锁的时候，会随机回滚其中的一个事务，从而解开死锁。下面的截图是发生死锁的时候innodb status截图(和业务相关的数据已脱敏，这里均用column_n和value_n表示)

Transaction1:

Transaction2:

现象阐述：从上方两个截图可以发现，死锁均发生在insert on duplicate key update语句执行的时候，并且每个insert语句均为批量插入多个数据。对于事务一，可以看到事务一在等待某个锁的获取，且这个锁是"lock_mode X locks gap before rec insert intention waiting"，直接翻译过来就是插入意向锁在等待排他gap锁的释放，也就是只有排他gap锁释放后插入意向锁才能获取到（关于这些锁的含义见下一节）。对于事务二，同样可以看到相同的一句话。并且两个事务的锁冲突均发生在”唯一索引“上。再进一步观察可以看到，事务二所持有（"Holds the Locks"下方展示的索引值）的排它锁所在的索引（锁均是加在索引上或者索引区间上的），与事务一等待获取锁的索引是一样的。进一步展示了的确，在同一个索引上出现了一个等待获取，一个已经获取的冲突现象。

问题的复现：

数据库准备：数据库中能够包含一个unique key： code

CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `code` int(11) NOT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB

初始数据：insert into test2 (code, other) values(1,1),(3,3),(5,5)

复现场景：原始的code字段为1,3, 5，现在要在中间插入code为2，3，4，5的row, 如果碰到唯一键约束则更新other字段。

Time	Session1	Session2
T1	start transaction; insert into test2(code, other) values (3, 4) on duplicate key update other = VALUES(other);
T2		start transaction; insert into test2(code, other) values (5, 6) on duplicate key update other = VALUES(other);
T3	insert into test2(code, other) values (4, 4) on duplicate key update other = VALUES(other) 现象：一直阻塞
T4		insert into test2(code, other) values (2, 2) on duplicate key update other = VALUES(other); 现象：出现死锁，事务回滚 (2,2插入失败，且code为5时，更新other字段失败，更新和插入均丢失)

死锁出现后，我们查看innodb status中的死锁记录，如下：

可以发现，复现出来的结果，和上文中的案例几乎完全一致。下面我们对此结果进行分析。

问题的分析：

在T1时刻Session1执行完insert操作后，由于插入的code=2已经存在于表中，发生了唯一键冲突，所以触发了duplicate-checking，导致在(1,3]这个区间加上了next-key lock。这里，我为了进一步证明确实只有(1,3]这个区间加了锁。在T1时刻执行完后，验证插入code=0/4/6的数据可以在Session2中执行成功。同时这个时候Session2中可以修改code=1的数据，如update test2 set other=0 where code=1可以执行成功（当然你不能update test2 set code=2 where code=1,因为这个操作是在向(1,3]的间隙内插入了数据，违反了gap锁的要求）。同时我们可以证明这时code=3肯定是被排他锁锁住的，由于当出现唯一键冲突时，就会执行on duplicate key update，更新other字段，所以code=3一定在更新结束后处于排它锁锁定状态（补充说明：可以证明如果是共享锁的话，session2在T2时刻执行insert into test2(code, other) values (3, 33)语句的话，一定会立刻包duplicate error而不会阻塞。但是事实上如果Session2在T2时刻执行这句sql，会一直阻塞，进一步说明code=3加的是排它锁。另外需要注意的是，其实我目前只能非常确定code = 3是有排它锁，但是(1,3)上面，到底是S gap lock 还是X gap lock无法确定，不过无论是S还是X,不影响后续的解释。）
在T2 完成时，同理也会在（3，5]这个区间上X next-key lock (在上面的截图中也可以看到插入code=5后，正在插入code=2的时候，写着HOLD the lock hex 80000005)
当T1和T2执行完成之后，我们可以看到(1,3] 和（3,5]分别被Session1和Session2锁定，T3时候，Session1尝试插入code=4, 由于在插入前会加插入意向锁，（对于插入意向锁的锁的范围，我目前尚无法确认在3~5的区间内加锁的时候，左右临界的开合问题）但是很明显，插入意向锁一定和(3,5]区间的next-key lock有重合，所以会出现在Session1执行T3的时候，语句被阻塞了，它在等待Session2释放(3,5]这个区间的X next_key lock 。可以参考下图——一个非常详细的锁兼容矩阵，理解阻塞原因（兼容矩阵图链接）。
同理，在T4时刻Session2执行插入语句的时候，由于（1,3]被阻塞了，但是插入的时候又要请求1~3这个区间的插入意向锁，等待Session1释放X next-key lock。于是乎死锁发生，Session2被回滚。

至此：死锁的现象可以顺利的解释通。（当然，这里还有一个疑惑不是很明白，当出现唯一冲突的时候为什么要加Next-Key Lock。有知道原因的小伙伴可以告诉我）

问题的拓展：

如果将insert on duplicate key update换成insert ignore语句，是否可以避免死锁的发生呢？答案是：否定的。其实原理都是一样的。如果我们将上述复现中的insert on duplicate key update换成insert ignore，同样会在T4时刻出现死锁。
同样，update和insert on duplicate key update组合也可以构造出死锁的出现。数据库中表结构不变，数据初始化为(1,1,1),(3,3,3),(5,5,5) 分别对应id, code,other, id是pk.

Time	Session1	Session2
T1	start transaction; update test2 set other=1 where id=3;
T2		start transaction; insert into test2(code, other) values (5, 55) on duplicate key update other = VALUES(other);
T3	update test2 set other=1 where id=5; 现象：一直阻塞
T4		insert into test2(code, other) values (3, 33) on duplicate key update other = VALUES(other); 现象：出现死锁，回滚 (2,2插入失败)

总结：

说了这么多，死锁的原因找到了，解决的办法其实比较简单。

将批量insert on duplicate key update,拆分成多个语句。保证一次事务中不要插入过多值，将多个数据，变成多个sql，执行插入。可以有效的减少死锁命中的发生。
重试：死锁不可怕，当出现死锁发生时，多执行重试操作可以有效保证插入成功，更新不丢失。

参考文章：

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
https://dba.stackexchange.com/questions/237549/gap-locking-in-read-committed-isolation-level-in-mysql
https://www.cnblogs.com/crazylqy/p/7773492.html
https://www.jianshu.com/p/dca007208a58
https://my.oschina.net/actiontechoss/blog/3068976
https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html
https://www.cnblogs.com/zhoujinyi/p/3435982.html
http://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html

码农公寓

当并发insert on duplicate key update遇见死锁：更新丢失

问题的背景：

问题的现象：

相关概念：

问题的复现：

问题的分析：

问题的拓展：

总结：

码农公寓

问题的背景：

问题的现象：

相关概念：

问题的复现：

问题的分析：

问题的拓展：

总结：

相关文章