MySQL / 技术文章

MySQL ALTER TABLE 卡住如何定位

ALTER TABLE 在发布窗口里“卡住”时,最危险的不是等待本身,而是不知道它是在等元数据锁、已经进入耗时执行,还是正在把新的业务连接继续拖进队列。下面这套排查路径面向后端工程师、SRE 与 DBA,目标是在保留现场证据的前提下定位根阻塞会话,判断应该终止 DDL 还是阻塞者,并确认业务连接恢复。

先判断:DDL 在等待,还是正在执行

先不要把“运行时间长”直接等同于“DDL 很慢”。第一步只回答两个问题:ALTER TABLE 对应的连接 ID 是多少,它当前的 State 是什么。

sql
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> 替换为本次变更对象。

sql
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。

sql
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 活跃事务对应起来:

sql
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 是否进入了可观测的执行阶段,以及完成量是否在变化。

sql
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 必须继续,而且已经确认阻塞者是可中断的异常长事务:再终止阻塞连接。

sql
KILL QUERY <DDL_PID>;

如果只是终止阻塞会话当前正在执行的语句,事务本身可能仍然存在,MDL 也可能继续持有,因此不要把 KILL QUERY 当成“释放事务锁”的保证。对于确认可以中断的根阻塞连接,通常需要结束整个连接:

sql
KILL CONNECTION <BLOCKING_PID>;

执行前至少确认连接归属、事务年龄、已修改行数、是否承担关键业务,以及回滚可能带来的 IO 压力。大事务被终止后,回滚本身也可能持续一段时间,所以“已经 KILL”不等于“锁立即释放”。

如果现场已经出现连接池耗尽或请求雪崩,先暂停继续发起同类 DDL、关闭发布侧自动重试,并控制对目标表的非必要流量。目标是先阻止等待链继续增长,再处理根阻塞者,而不是同时对多个未知会话做破坏性操作。

验证恢复,并给下一次发布加上快速失败边界

处置后重新检查目标表的 MDL 等待链,确认不再有同一个 ALTER 或业务请求继续排队。

sql
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。超时时间应由发布窗口和业务容忍度决定,不要把下面的示例值直接当成统一标准。

sql
SET SESSION lock_wait_timeout = 30;

这类快速失败只能限制“等锁等多久”,不能让一个本来需要表重建的 DDL 变快。真正的发布判断仍然是:先确认算法和锁级别,再给 DDL 设置等待边界;一旦异常,按“识别等待态 → 找等待者与阻塞者 → 核对长事务 → 决定终止对象 → 验证等待链消失”的顺序处理。

目录与阅读设置

记录一个想法

MySQL ALTER TABLE 卡住如何定位

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通