带索引列WHERE查询的MySQL InnoDB死锁问题排查

核心原因1：InnoDB的Next-Key Lock（间隙锁+行锁）机制 #

InnoDB在默认的REPEATABLE READ隔离级别下，会启用Next-Key Lock来避免幻读。事务1执行的UPDATE语句同时用到status_id=5和container_id=1020065两个条件，但这两个字段是单独的单列索引，没有联合索引，导致InnoDB无法精准定位到唯一的行范围。它会对status_id=5索引对应的整个范围（包括行与行之间的间隙）加锁，这就出现了实际匹配2行但锁定11行的情况——额外的锁来自于间隙锁，而这些锁的范围恰好覆盖了事务2要更新的id=2160566对应的行所在的索引区间。

核心原因2：单列索引导致锁范围扩大 #

事务1的UPDATE条件涉及两个单列索引，MySQL优化器选择了shipment_detail_child_status（status_id索引）来定位数据，之后需要回表验证container_id是否符合条件。在这个过程中，InnoDB会先对status_id=5索引下的所有行加锁，再过滤不符合container_id条件的行，这就导致原本不匹配的行也被锁定，锁的范围被无端扩大，包含了事务2操作的行。

死锁形成的具体过程 #

1. 事务1先通过status_id索引锁定了一批行（包含事务2目标行的索引位置），随后尝试获取这些行的主键索引锁以完成更新。
2. 事务2先通过主键索引锁定了id=2160566的行，随后更新status_id时需要获取shipment_detail_child_status索引对应的锁。
3. 双方互相持有对方需要的锁资源，最终形成死锁。

可行的解决方案 #

• 创建(status_id, container_id)联合索引：让MySQL能通过联合索引直接定位到满足两个条件的行，无需回表过滤，大幅缩小锁的范围。
• 调整事务执行顺序：避免两个事务交叉锁定不同索引的资源。
• 调整隔离级别（谨慎使用）：若业务可以接受幻读，可将隔离级别改为READ COMMITTED，此时InnoDB仅使用行锁，不会触发间隙锁。

内容的提问来源于stack exchange，提问作者Sheryar Khan