如何应对MySQL在大表ALTER TABLE操作中引发的生产长事务锁死故障

本质是MDL写锁等待长事务释放读锁所致；需分三步：一、用SHOW PROCESSLIST和INNODB_TRX定位并KILL超时事务；二、区分MDL阻塞、锁超时、死锁三类场景；三、通过错峰操作、INSTANT校验、pt-osc工具及开发规范长效预防。

执行大表 ALTER TABLE 时引发长事务锁死，本质是元数据锁（MDL）与活跃事务冲突所致——不是“表被锁住”，而是 ALTER 在等所有读写事务释放 MDL 读锁，而长事务迟迟不提交，导致后续所有请求排队阻塞。解决需分三步：快速止血、精准定位、长效预防。

一、立即止血：快速识别并终止阻塞源头

别先杀 ALTER 进程，它通常不是元凶。优先查谁在“占着茅坑不拉屎”：

• 运行 SHOW PROCESSLIST;，找状态为 Waiting for table metadata lock 的线程，记下其 ID（第一列）和对应 SQL
• 再执行：
  SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id, SUBSTRING_INDEX(trx_query,' ', 4) AS short_query FROM information_schema.INNODB_TRX WHERE trx_started < DATE_SUB(NOW(), INTERVAL 60 SECOND) ORDER BY trx_started;
  重点看 trx_started 时间远早于当前时间的事务（如超 1 分钟），它们极可能是未提交的长事务
• 确认无业务影响后，用 KILL <thread_id>（不是 KILL QUERY）终止该线程——只有 KILL 才能真正回滚事务、释放 MDL 锁

二、精准定位：区分三类典型阻塞场景

同一句 Waiting for table metadata lock，背后原因不同，处理策略也不同：

• MDL 写锁被长事务阻塞：最常见。例如一个 SELECT * FROM t FOR UPDATE 开了事务但没提交，ALTER 就永远卡住。查 INNODB_TRX 中老事务即可定位
• DDL 自身耗时过长触发锁等待超时：如对千万级表加非空字段且未设默认值，MySQL 会全表更新填充 NULL，持有行锁+MDL 锁数分钟。此时错误日志报 Error 1205 或超时，需改用带默认值或分步操作
• 高并发下 DDL 与写入形成死锁：如 ALTER 和多个 UPDATE 同时争抢表级资源。MySQL 会主动报 Error 1213 并回滚其中一个，但业务已受损。需结合 SHOW ENGINE INNODB STATUS 查 LATEST DETECTED DEADLOCK 段分析

三、长效预防：从设计到执行的实操要点

靠“事后 kill”只能救急，以下措施才能根治：

• 禁止在业务高峰期执行 DDL：尤其避免对核心大表操作。可结合业务低峰期窗口（如凌晨 2–4 点）并提前通知
• 用 ALGORITHM=INSTANT 前严格校验条件：仅支持末尾加列、删非索引列、改列名/注释；表格式必须为 DYNAMIC 或 COMPRESSED；MySQL 版本 ≥ 8.0.29（非 2026 年发布版）
• 大表变更必走在线工具：如 pt-online-schema-change 或 gh-ost，它们通过影子表+触发器实现无锁变更，适合生产环境
• 约束开发规范：禁止应用层开启事务后长时间休眠；所有事务必须有明确超时（如 innodb_lock_wait_timeout=10）；关键业务表定期审核长事务风险

不复杂但容易忽略：真正的瓶颈往往不在 DDL 本身，而在那个忘了 COMMIT 的开发测试脚本，或监控里一直没告警的慢查询事务。