如何处理MySQL由于表结构变更（ALGORITHM=INPLACE）导致的锁表？

ALGORITHM=INPLACE本身不锁表，但需在准备和提交阶段获取排他元数据锁（X MDL），若遇长事务持有S MDL则阻塞，导致后续DML全部挂起；真正卡住DDL的是未释放S MDL的长事务或Sleep连接，而非DDL自身。

ALGORITHM=INPLACE 本身不锁表，但会卡在元数据锁（MDL）上——真正拖住 DDL 的不是它自己，而是其他事务没释放的 S MDL。只要有一个长 SELECT 或 Sleep 连接挂着这张表，ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 就会堵在“Waiting for table metadata lock”状态，连带把后续所有 DML 拦死。

为什么 ALGORITHM=INPLACE 还会卡住？

很多人以为加了 ALGORITHM=INPLACE 就全程无锁，其实它只跳过数据行拷贝，但两个关键节点仍需排他元数据锁（X MDL）：

• 初始准备阶段：校验结构、分配资源，必须抢到 X MDL
• 最终提交阶段：原子更新数据字典（比如 mysql.table），必须再抢一次 X MDL

只要此时有任意活跃事务（哪怕只是 SELECT * FROM t 没提交）持有该表的 S MDL，X MDL 请求就会排队；而新来的 DML 又因拿不到 S MDL 被堵在后面——一个卡点引发雪崩。

快速定位谁在持锁

别盯着 SHOW FULL PROCESSLIST 里那些 “Waiting for table metadata lock” 的线程，那是下游受害者。要直接揪出源头：

• 查长时间运行事务：SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY duration_sec DESC LIMIT 5，重点关注 duration_sec > 60 且 trx_state = 'RUNNING' 的
• 查休眠老连接：SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST WHERE COMMAND = 'Sleep' AND TIME > 300，TIME > 300 是高危信号
• 交叉验证等待关系：SELECT * FROM information_schema.innodb_lock_waits（注意它只反映当前活跃等待，不存历史）

KILL 前必须确认两件事

盲目 KILL 可能导致应用层事务异常或主从延迟跳变：

• 核对 USER 和 HOST，比对服务注册中心或部署清单，确认是不是你可控的服务连接
• 看 INFO 字段内容：如果是空或简单 SELECT，KILL 风险低；如果是已执行部分逻辑的 UPDATE 或 INSERT，KILL 会回滚全部变更，得评估业务影响
• 更稳妥做法是先试 KILL QUERY <thread_id>（只中断当前语句，不杀连接），观察是否释放 MDL；若无效再 KILL <thread_id>

怎么写 ALTER 才真正避开锁表风险？

显式指定参数不是可选，而是控制行为的底线：

• 加二级索引的标准写法：ALTER TABLE users ADD INDEX idx_email (email) ALGORITHM=INPLACE, LOCK=NONE;
• 如果报错 ALGORITHM=INPLACE is not supported，说明操作不兼容（比如改了主键、加了全文索引），得换方案
• 如果报错 LOCK=NONE is not supported，说明当前操作允许并发但需短暂锁元数据，可降级为 LOCK=SHARED（读可用，写暂停），比直接锁表好
• 永远不要省略 ALGORITHM=INPLACE——默认可能 fallback 到 COPY，等于主动选择停服

真正难处理的不是 DDL 本身，而是那些没人管的长事务和 Sleep 连接；它们像暗桩，平时不显眼，一到 DDL 就顶住整个表。线上执行前，务必先扫一遍 information_schema.innodb_trx 和 PROCESSLIST，而不是等 Waiting for table metadata lock 出现才去救火。