MySQL / 技术文章
MySQL ALTER TABLE 卡住如何定位
ALTER TABLE 在发布窗口里“卡住”时,最危险的不是等待本身,而是不知道它是在等元数据锁、已经进入耗时执行,还是正在把新的业务连接继续拖进队列。下面这套排查路径面向后端工程师、SRE 与 DBA,目标是在保留现场证据的前提下定位根阻塞会话,判断应该终止 DDL 还是阻塞者,并确认业务连接恢复。
先判断:DDL 在等待,还是正在执行
先不要把“运行时间长”直接等同于“DDL 很慢”。第一步只回答两个问题:ALTER TABLE 对应的连接 ID 是多少,它当前的 State 是什么。
SHOW FULL PROCESSLIST;在结果中找到 ALTER TABLE 会话。如果 State 明确出现 Waiting for table metadata lock,优先按 MDL 阻塞处理;如果没有这个等待状态,则继续检查它是否正在执行复制、重建、排序索引等阶段。与此同时观察是否有大量业务连接开始在同一张表附近等待,因为一个排队中的 DDL 可能让故障从“单条变更卡住”扩大成“请求连接堆积”。
不要只看 Time。一个持续 600 秒的 ALTER TABLE 可能是执行了 600 秒,也可能是 590 秒都在等锁;这两种情况的处置对象完全不同。
用 sys 视图直接找出谁在挡住 ALTER TABLE
MySQL 8.4 的 sys.schema_table_lock_waits 已经把元数据锁等待者和阻塞者关联起来,适合故障现场先快速查看阻塞链。把 <DB> 和 <TABLE> 替换为本次变更对象。
SELECT
object_schema,
object_name,
waiting_pid,
waiting_account,
waiting_lock_type,
waiting_query_secs,
waiting_query,
blocking_pid,
blocking_account,
blocking_lock_type,
blocking_lock_duration,
sql_kill_blocking_query,
sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = '<DB>'
AND object_name = '<TABLE>'\G先认清三个角色:waiting_pid 是正在等锁的连接,通常就是 ALTER TABLE;blocking_pid 是当前持有不兼容 MDL 的连接;同一个等待者可能对应多个阻塞者,因此不要看到第一行就立即执行 KILL。sql_kill_blocking_* 只是便于定位的候选命令,不等于可以无条件执行。
如果这里已经明确显示 ALTER TABLE 正在等待,而某个业务连接持有阻塞锁,排查重点就从 DDL 算法切换为“为什么这个业务连接的事务还没有结束”。
sys 信息不够时,下钻到 metadata_locks 和长事务
当 sys 视图不可用、字段被裁剪,或者需要确认同一张表上还有哪些已授予和待授予的 MDL,可以直接查询 Performance Schema。
SELECT
ml.OBJECT_SCHEMA,
ml.OBJECT_NAME,
ml.LOCK_TYPE,
ml.LOCK_DURATION,
ml.LOCK_STATUS,
t.PROCESSLIST_ID AS pid,
t.PROCESSLIST_USER AS user_name,
t.PROCESSLIST_HOST AS host_name,
t.PROCESSLIST_STATE AS state,
LEFT(t.PROCESSLIST_INFO, 200) AS sql_text
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
AND ml.OBJECT_SCHEMA = '<DB>'
AND ml.OBJECT_NAME = '<TABLE>'
ORDER BY FIELD(ml.LOCK_STATUS, 'PENDING', 'GRANTED'),
t.PROCESSLIST_TIME DESC;PENDING 是尚未拿到锁的请求,GRANTED 是已经持有的锁。接着把可疑的 blocking_pid 与 InnoDB 活跃事务对应起来:
SELECT
trx_mysql_thread_id AS pid,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
trx_state,
trx_rows_locked,
trx_rows_modified,
LEFT(trx_query, 200) AS trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;重点找“事务开始时间很早、连接看起来已经空闲、但事务仍未提交”的会话。SHOW PROCESSLIST 中的 Sleep 不能单独证明它无害:如果它仍处于未结束事务中,之前访问目标表取得的 MDL 仍可能保留到事务结束。反过来,也不要因为事务很老就直接杀掉;先确认它属于哪个应用、是否在关键写路径、回滚规模有多大。
没有 MDL 等待时,确认 ALTER 是否真的在推进
如果 ALTER TABLE 会话没有等待元数据锁,就不要继续沿着“找 blocker”这条路排查。此时需要确认 DDL 是否进入了可观测的执行阶段,以及完成量是否在变化。
SELECT
t.PROCESSLIST_ID AS pid,
es.EVENT_NAME,
es.WORK_COMPLETED,
es.WORK_ESTIMATED,
ROUND(
100 * es.WORK_COMPLETED / NULLIF(es.WORK_ESTIMATED, 0),
2
) AS progress_pct
FROM performance_schema.events_stages_current AS es
JOIN performance_schema.threads AS t
ON t.THREAD_ID = es.THREAD_ID
WHERE t.PROCESSLIST_ID = <DDL_PID>;连续采样时,如果 WORK_COMPLETED 持续增长,说明它不是“锁死”,而是在做实际工作;此时应把注意力转向磁盘 IO、临时空间、CPU、redo/binlog 压力和复制延迟。查询结果为空也不能直接判定 DDL 卡死,因为相关 stage instrument 可能没有启用,仍要结合 SHOW FULL PROCESSLIST、主机资源和磁盘空间一起判断。
还有一种容易误判的情况:DDL 主体工作已经接近完成,却在切换表定义时等待独占元数据锁。这时它看起来“已经跑了很久才突然卡住”,根因仍然可能是新的长事务或持续访问该表的会话。
止血时先决定杀谁,不要看到阻塞就 KILL
故障处置通常有两个方向。第一种是业务已经明显受影响,而本次结构变更可以放弃:优先终止等待中的 ALTER TABLE,让排队的独占 MDL 请求消失,再观察新业务请求是否恢复。第二种是 DDL 必须继续,而且已经确认阻塞者是可中断的异常长事务:再终止阻塞连接。
KILL QUERY <DDL_PID>;如果只是终止阻塞会话当前正在执行的语句,事务本身可能仍然存在,MDL 也可能继续持有,因此不要把 KILL QUERY 当成“释放事务锁”的保证。对于确认可以中断的根阻塞连接,通常需要结束整个连接:
KILL CONNECTION <BLOCKING_PID>;执行前至少确认连接归属、事务年龄、已修改行数、是否承担关键业务,以及回滚可能带来的 IO 压力。大事务被终止后,回滚本身也可能持续一段时间,所以“已经 KILL”不等于“锁立即释放”。
如果现场已经出现连接池耗尽或请求雪崩,先暂停继续发起同类 DDL、关闭发布侧自动重试,并控制对目标表的非必要流量。目标是先阻止等待链继续增长,再处理根阻塞者,而不是同时对多个未知会话做破坏性操作。
验证恢复,并给下一次发布加上快速失败边界
处置后重新检查目标表的 MDL 等待链,确认不再有同一个 ALTER 或业务请求继续排队。
SELECT
object_schema,
object_name,
waiting_pid,
blocking_pid,
waiting_query_secs
FROM sys.schema_table_lock_waits
WHERE object_schema = '<DB>'
AND object_name = '<TABLE>';预期结果不是“DDL 立刻完成”,而是等待链符合你的处置目标:如果已经取消变更,应看不到该 ALTER 的等待记录,业务连接数和延迟开始回落;如果保留变更继续执行,应确认原来的 blocking_pid 消失,并观察 DDL 进入执行阶段或继续推进。
为了避免下一次发布把问题拖到连接堆积之后才暴露,可以在执行 DDL 的专用会话里先设置合理的 MDL 等待上限,再发起 ALTER TABLE。超时时间应由发布窗口和业务容忍度决定,不要把下面的示例值直接当成统一标准。
SET SESSION lock_wait_timeout = 30;这类快速失败只能限制“等锁等多久”,不能让一个本来需要表重建的 DDL 变快。真正的发布判断仍然是:先确认算法和锁级别,再给 DDL 设置等待边界;一旦异常,按“识别等待态 → 找等待者与阻塞者 → 核对长事务 → 决定终止对象 → 验证等待链消失”的顺序处理。