MySQL执行DELETE语句挂起报锁等待超时问题排查与解决

• 未提交的空闲长事务持有目标行排他锁是最核心的原因。你之前通过show engine innodb status\G没查到锁，是因为这个命令只会返回执行瞬间的锁等待链路，等锁超时报错后，等待锁的会话已经自动回滚语句退出等待，锁链路销毁，事后自然查不到记录。这类持锁事务基本都是操作遗留的Sleep状态连接：比如之前手动删记录时客户端关闭了自动提交，事务执行到一半没commit/rollback就切走窗口、连接异常断开但没被MySQL回收，并不是你判断的“无其他会话访问”，自己之前操作遗留的连接持锁是最容易被忽略的场景。
• 不存在索引问题。从EXPLAIN结果看，语句已经走主键扫描，预估扫描行数1行，属于最优执行计划，不会出现全表扫描锁大量行的问题，你之前给aisle字段加索引对这个语句没有任何优化作用，解决不了锁问题。
• 特殊情况是InnoDB Purge线程阻塞：如果存在长时间运行的老事务，Purge线程无法清理已经标记为删除的旧版本行记录，目标行残留半清理状态的隐式锁，也会导致DELETE操作持续等待。你提到“删前后行后就能删目标行”，本质是删其他行的等待过程中老事务提交/被回滚、或者Purge线程刚好推进到了目标行所在的页，不是删前后行本身有什么特殊作用。

重新开一个独立的MySQL会话，在执行DELETE语句之前先运行以下命令，不要等报错后再查：

-- 查询所有运行超过10秒的活跃事务，重点看trx_query为NULL的空闲事务
SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_rows_locked, trx_query 
FROM information_schema.INNODB_TRX 
WHERE trx_started < DATE_SUB(NOW(), INTERVAL 10 SECOND);

-- 查询当前实时的锁等待链路
SELECT * FROM sys.innodb_lock_waits;

如果查询结果里有访问目标表的空闲事务，那个就是锁的持有者。
额外可以查Purge状态确认是不是清理阻塞：

SHOW GLOBAL STATUS LIKE 'Innodb_purge_pending';

如果返回值大于0，说明Purge线程确实在等待清理旧版本。

1. 清理异常持锁连接：从上面INNODB_TRX查询结果里拿到持锁事务对应的trx_mysql_thread_id，执行KILL 线程ID;杀掉异常连接，之后直接执行原DELETE语句即可正常完成。
2. 没有查到持锁事务但还是阻塞的话，用主键精确匹配加锁跳过残留锁，强制删除：
   先确认目标行唯一：

   SELECT filetime FROM database.table 
   WHERE filename='roger.doc' 
     AND aisle='whatever' 
     AND filetime='2022-07-09 18:37:13.645050' 
   LIMIT 1;
   

   确认只返回1条目标记录后，直接按主键删除，加LIMIT 1避免误删：

   DELETE FROM database.table 
   WHERE filetime='2022-07-09 18:37:13.645050' 
   LIMIT 1;
   

3. 如果是Purge阻塞导致的问题，临时调整参数推进Purge进度：

   -- 当前会话锁等待时间临时调到2分钟
   SET SESSION innodb_lock_wait_timeout = 120;
   -- 临时加大Purge批量清理的大小
   SET GLOBAL innodb_purge_batch_size = 500;
   

   等待1-2分钟让Purge线程清理完残留的旧版本记录，再执行DELETE即可。
4. 日常操作规避：操作InnoDB表时不要长期保持自动提交关闭的空闲连接，执行完DML语句及时commit/rollback，避免遗留长事务持锁阻塞其他操作。

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