如何有效解决MySQL长时间运行未提交事务导致的锁表问题？

本文共计1060个文字，预计阅读时间需要5分钟。

确保在执行KILL CONNECTION命令之前，已确认`trx_state`为`RUNNING`且`trx_started`已过时。

只看 PROCESSLIST 的 STATE = 'Locked' 或 TIME > 300 容易误杀——很多慢查询刚执行完、语句已结束，INFO 为空，但事务仍开着，trx_state 却稳稳挂着 RUNNING。真正该盯的是 INNODB_TRX 表：TRX_STARTED 时间戳比当前时间早几分钟甚至几小时，且 TRX_STATE = 'RUNNING'，这种才是“静默长事务”。

查法示例：

SELECT t.TRX_ID, t.TRX_MYSQL_THREAD_ID, t.TRX_STARTED, t.TRX_ROWS_MODIFIED, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.INFO FROM information_schema.INNODB_TRX t JOIN information_schema.PROCESSLIST p ON t.TRX_MYSQL_THREAD_ID = p.ID WHERE t.TRX_STATE = 'RUNNING' AND t.TRX_STARTED < NOW() - INTERVAL 30 SECOND;

注意：TRX_ROWS_MODIFIED > 0 说明它真改过数据，不是空事务；INFO IS NULL 不代表安全，反而是高危信号。

用 KILL CONNECTION 而不是 KILL QUERY 来终结未提交事务

KILL QUERY 只中断当前语句，对已 BEGIN 未 COMMIT 的事务毫无作用——它不会回滚，锁不释放，Undo Log 继续膨胀。而未提交事务的根源就是“事务没关”，不是“语句太慢”。

必须用 KILL CONNECTION（或简写 KILL）才能触发 InnoDB 强制回滚整个事务、释放所有行锁/表锁、清空 Undo Log 链、腾出 Redo 空间。

实操要点：

• 先确认权限：你得有 SUPER 或 CONNECTION_ADMIN（MySQL 5.7+）
• 别直接 KILL 12345，先查清楚：SELECT ID, USER, HOST, DB, INFO FROM information_schema.PROCESSLIST WHERE ID = 12345;
• 避开系统线程：USER = 'system user' 或 COMMAND = 'Daemon' 的不能杀
• 批量执行要节制：高并发下连续发多个 KILL CONNECTION 可能引发短暂刷盘抖动

STATE = 'Waiting for table metadata lock' 本质是未提交事务在挡路

DDL（如 ALTER TABLE）被卡住，90% 情况不是 DDL 本身慢，而是前面有个事务没提交，正拿着 MDL 写锁，后面所有操作（包括 SELECT）全被堵在 Waiting for table metadata lock 状态。

这时候光杀 DDL 线程没用，得顺藤摸瓜找源头：

• 从 PROCESSLIST 找到 STATE 含 metadata lock 的线程，记下它的 ID
• 查 performance_schema.metadata_locks（需提前开启）或关联 INNODB_TRX 找出最早持有该表 MDL 的 TRX_MYSQL_THREAD_ID
• 那个线程大概率 TRX_STATE = 'RUNNING' 且 TRX_STARTED 很老 —— 它才是真凶

常见陷阱：ORM 框架（比如 Spring @Transactional）里做了 HTTP 调用或日志写入，事务边界被意外拉长，PROCESSLIST 看不到活跃语句，但锁一直占着。

自动清理不能只靠 wait_timeout

wait_timeout 和 interactive_timeout 只对 COMMAND = 'Sleep' 的空闲连接生效，对卡在锁等待、大事务回滚、或 TRX_STATE = 'RUNNING' 的连接完全无效。默认 8 小时等于放任自流。

更务实的做法是定时巡检 + 主动干预：

• 写个脚本每 2 分钟跑一次：SELECT CONCAT('KILL CONNECTION ', TRX_MYSQL_THREAD_ID, ';') FROM information_schema.INNODB_TRX WHERE TRX_STATE = 'RUNNING' AND TRX_STARTED < NOW() - INTERVAL 60 SECOND;
• 把生成的 KILL 语句写入临时文件，再 SOURCE 执行（避免单条 SQL 失败中断）
• 记录被杀线程的 USER、HOST、DB 和 TRX_STARTED 到日志表，用于追责和优化
• 别依赖 innodb_kill_idle_transaction：这个参数只在极旧版本存在，新版本已移除，文档都不提了

最常被忽略的一点：KILL 后线程状态变成 Killed 却迟迟不消失，大概率是磁盘满、内存不足，或者事务修改量太大正在回滚——这时不能反复 KILL，得先看 SHOW ENGINE INNODB STATUS 里的 TRANSACTIONS 部分，确认回滚进度。