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