MySQL表结构变更INPLACE算法导致锁表怎么办

首先明确一个核心观点：ALGORITHM=INPLACE 本身确实不会锁表，但许多开发者在线上执行 ALTER TABLE 时，误以为添加该参数就能高枕无忧，结果仍然导致业务阻塞。问题的根源并非 DDL 本身，而是它被元数据锁（MDL）卡住——更准确地说，是被尚未完成的其他长事务拖累，这些事务持有了共享 MDL。

只要该表上存在一个长时间运行的 SELECT 查询，或一个进入休眠状态的 Sleep 连接，它们仍然持有表的共享元数据锁（S MDL），那么你的 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 就会立即受阻，状态变为“Waiting for table metadata lock”。更糟糕的是，后续的所有 DML 操作也会因为无法获取 S MDL 而被依次阻塞。一个阻塞点，引发连锁雪崩效应。

为什么 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”的线程，那些是下游受害者。我们要直接找出源头：

• 定位运行超过60秒的事务：执行以下 SQL，重点关注持续时间超过 60 秒且状态为 RUNNING 的事务，它们极有可能是元凶。

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;

• 查找休眠超过300秒的连接：这些连接是高风险因素。

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 
FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Sleep' AND TIME > 300;

• 交叉验证等待关系：SELECT * FROM information_schema.innodb_lock_waits 可以告诉你当前谁在等谁，尽管不包含历史数据，但对于现场救火场景已经足够。

执行 KILL 之前，务必确认这两点

随意选择一个线程执行 KILL 风险很高，可能导致应用层事务异常报错，甚至引发主从延迟跳变。动手前必须完成以下两步功课：

• 核对身份：查看 USER 和 HOST，去服务注册中心或部署清单中比对，确认该连接属于你可控的服务，避免误杀后台核心任务。
• 评估当前操作：留意 INFO 字段。如果内容为空或只是一个简单的 SELECT，则 KILL 风险很低；但如果是一个已执行一半的 UPDATE 或 INSERT，执行 KILL 将导致全部变更回滚，必须评估影响范围。
• 更稳妥的做法是先尝试“软中断”：使用 KILL QUERY ，该命令只中断当前正在执行的语句，不杀死连接本身。观察 MDL 是否释放；若无效，再执行 KILL 。

如何编写 ALTER 才能避免锁表风险？

显式指定 ALGORITHM 和 LOCK 参数并非最佳实践，而是你控制底层行为的生存底线：

• 标准操作：例如添加二级索引，应这样编写，切勿省略。

ALTER TABLE users ADD INDEX idx_email (email) ALGORITHM=INPLACE, LOCK=NONE;

• 不兼容时的处理：如果报错提示 ALGORITHM=INPLACE is not supported，说明该操作（如修改主键、添加全文索引）不支持原地算法，需改用 pt-osc、gh-ost 等方案。
• 降级方案：如果报错提示 LOCK=NONE is not supported，说明操作允许使用 INPLACE 但需要短暂锁住元数据。此时可降级为 LOCK=SHARED——只读可用，写入暂停，这比全表锁要好得多。
• 切勿省略 ALGORITHM=INPLACE：MySQL 默认的 DDL 算法可能是 COPY，不指定就可能回落到复制算法，相当于主动选择停服。

因此，真正让你头疼的从来不是 DDL 本身，而是那些无人管理的长事务和休眠连接。它们就像埋在线上的暗桩，平时毫无征兆，一旦你要执行 DDL，就跳出来死死顶住整张表。下次线上操作前，记得先去 information_schema.innodb_trx 和 PROCESSLIST 中扫一圈，别等到看到“Waiting for table metadata lock”才慌慌张张去救火，那时已经晚了。