MySQL多个二级索引并发更新触发死锁

MySQL多个二级索引并发更新触发死锁

删除实例失败，报错db死锁。

事务一：

持有 i-ybsxzr5spk1234567890 锁，去获取 bi-ybrurprssf1234567890 锁；

事务二：

持有 bi-ybrurprssf1234567890 锁，去获取 i-ybsxzr5spk1234567890 锁；

事务一获取 i-ybsxzr5spk1234567890 的锁很好理解，但是这两个事务还要获取 bi-ybrurprssf1234567890 这个锁，看似和这两个update没有关系。

原因是上述两个update语句在执行时，用到了mysql的索引合并。

什么是索引合并（ blog.csdn.net/w10140747...， dev.mysql.com/doc/refma...）：

索引合并引发死锁（ modb.pro/db/58619）：

所以第一个事务先根据group_id索引，已经锁住primary id，然后再根据test_id索引，锁定primary id； 第二个事务先根据test_id索引，已经锁住primary id，然后再根据group_id索引，去锁primary id； 所以这样并发更新就可能出现死索引。 MySQL官方也已经确认了此bug： bugs.mysql.com/bug.php?...

再看出问题的两个update语句，where中有两个条件：instance_id 和 deleted_at，并且表结构中有对应两列单独的索引。

解释为什么两个update还要锁 bi-ybrurprssf1234567890，就是因为索引合并中根据 deleted_at 筛选到了 bi-ybrurprssf1234567890 这条记录，并尝试去给这个主键加锁。

表结构复现数据集

gitee.com/chen_zhuohan/...

代码如何避免

总的来说，当你的sql有这种情况，那mysql很有可能用索引合并，就会存在死锁风险：

sql用到了索引合并并非是一个最优的方案，反而证明你的sql需要为索引合并的列建立组合索引。一方面避免上述死锁问题，另一方面索引合并也会有性能损耗，不如直接用组合索引。

2. 使用主键更新列；

更新时where中有主键时，innodb会自动选用PRIMARY索引，而非索引合并。

3. （不推荐）where中的单列索引条件筛选出的结果数很少，比如唯一索引；

4. （不推荐）关闭优化器的index merge优化。

参考文档

MySQL 案例：Update 死锁详解

一次 MySQL 线上死锁分析实战

Reference:2024-09-04