InnoDB DELETE使用索引列仍出现死锁的原因排查求助

各位大佬好，我遇到一个特别困惑的InnoDB死锁问题，想请大家帮忙分析下原因：

我在应用的不同线程中执行下面的DELETE查询，已经做了保障——同一个key_other_table只会有一个DELETE操作同时执行，但死锁却发生在不同的key_other_table之间，实在搞不懂这两个操作怎么会互相阻塞。另外要说明的是，虽然查询里写了key_other_table in (?)，但实际每次只会传入一个值。

执行的DELETE语句 #

delete from my_table where (key_other_table in (?)) and some_boolean=? and some_timestamp<=?

死锁日志（来自SHOW ENGINE INNODB STATUS;） #

------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2026-03-10 08:45:52 0x7f103a5eb640
 *** (1) TRANSACTION:
 TRANSACTION 200182931, ACTIVE 0 sec fetching rows
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 15400, OS thread handle 139707667650112, query id 2231130649 999.999.99.999 some_user updating
 delete from my_table where (key_other_table in (24192412)) and some_boolean=0 and some_timestamp<='2026-03-10 08:45:52.108'
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 2654 page no 3 n bits 144 index PRIMARY of table `some_schema`.`my_table` trx id 200182931 lock_mode X locks rec but not gap waiting
 *** (2) TRANSACTION:
 TRANSACTION 200182932, ACTIVE 0 sec
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 15477, OS thread handle 139707675489856, query id 2231130658 999.999.99.999 some_user updating
 delete from my_table where (key_other_table in (24192398)) and some_boolean=0 and some_timestamp<='2026-03-10 08:45:52.108'
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 2654 page no 3 n bits 144 index PRIMARY of table `some_schema`.`my_table` trx id 200182932 lock_mode X locks rec but not gap
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 2654 page no 3 n bits 144 index PRIMARY of table `some_schema`.`my_table` trx id 200182932 lock_mode X locks rec but not gap waiting
 *** WE ROLL BACK TRANSACTION (2)

EXPLAIN查询结果 #

表结构（来自SHOW CREATE TABLE;） #

CREATE TABLE `my_table` (
   `key_my_table` int(11) NOT NULL AUTO_INCREMENT,
   `key_other_table` int(11) NOT NULL,
   `some_boolean` tinyint(1) NOT NULL DEFAULT '0',
   `some_int` int(11) NOT NULL,
   `some_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`key_my_table`),
   KEY `i_idx_key_other_table` (`key_other_table`),
   KEY `i_idx_some_boolean` (`some_boolean`),
   KEY `i_idx_some_int` (`some_int`),
   CONSTRAINT `fk_idx_other_table` FOREIGN KEY (`key_other_table`) REFERENCES `other_table` (`key_other_table`) ON UPDATE CASCADE
 ) 

MySQL版本：5.7

注：为了隐私，我修改了实际的表名和列名。

备注：内容来源于stack exchange，提问作者godmodule