MySQL / 技术文章

MySQL Online DDL 为什么仍会阻塞

Online DDL 能让主要执行阶段与 DML 并发,但不等于整个 ALTER TABLE 无锁。面向已经会执行 MySQL DDL 的后端工程师和 DBA,这里只解决一个问题:为什么声明为在线、甚至允许并发写入的变更仍会把业务卡住;读完后,你应能把阻塞定位到 DDL 的起始、执行或收尾阶段,并找到真正持有元数据锁的会话。

在线指的是并发能力,不是无锁

理解 Online DDL,先把三个经常混在一起的概念拆开:ALGORITHM 描述数据和索引如何被修改,LOCK 描述 ALTER 主执行阶段允许多大程度的并发访问,而 Metadata Lock(MDL)负责保证“正在使用旧表定义的会话”和“准备发布新表定义的 DDL”不会看到互相矛盾的元数据。

因此,ALGORITHM=INPLACE 或 LOCK=NONE 的正确含义不是“整个语句不拿锁”,而是“该操作支持在主要执行阶段继续进行相应的并发读写”。MySQL 8.4 对 INPLACE 的说明也明确指出,操作期间仍可能短暂取得排他的元数据锁;LOCK=NONE 只是在操作支持时允许并发读写。:contentReference[oaicite:0]{index=0}

可以把它记成一个判断式:

Online DDL = 尽量缩短排他窗口 + 允许主体阶段并发 DML,而不是 Online DDL = LOCK FREE。

一次 ALTER 的阻塞窗口在哪里

从锁行为看,一次在线 ALTER 更适合被理解为“短排他窗口—长在线阶段—短排他窗口”,而不是从开始到结束都保持同一种锁状态。

text
提交 ALTER
   |
   v
[准备阶段] ---- 需要短暂取得较强/排他的 MDL,检查并准备元数据
   |
   v
[执行阶段] ---- 按算法执行;若能力允许,业务 DML 可以继续
   |
   v
[最终阶段] ---- 必须取得排他 MDL,更新并发布新的表定义
   |
   v
DDL 完成

MySQL 8.4 的 Online DDL 限制说明指出,in-place 在线 DDL 在执行期间可能短暂需要排他 MDL,并且在最终更新表定义时一定需要排他 MDL;如果此时仍有事务持有该表的 MDL,DDL 就必须等待。InnoDB 的原子 DDL 也会经历 Prepare、Perform、Commit 等阶段,最终需要提交数据字典事务。:contentReference[oaicite:1]{index=1}

这解释了一个常见现象:ALTER 已经运行了很久,CPU、IO 和进度都看起来正常,却在“最后一下”突然停住。此时真正卡住的可能不是索引构建或表重建,而是新表定义发布前的 MDL 获取。

长事务为什么能卡住在线 DDL

MDL 的关键不是“SQL 执行了多久”,而是“事务什么时候结束”。普通 DML 访问表时也会取得元数据锁;在显式事务中,这些锁通常一直持有到 COMMIT 或 ROLLBACK。因此,一个早已执行完 SELECT、当前处于空闲状态的连接,仍可能阻止 DDL。MySQL 文档明确说明:事务使用过某张表后,相关 MDL 会延迟到事务结束才释放。:contentReference[oaicite:2]{index=2}

可以用三个会话复现这个机制。先准备实验表:

sql
CREATE DATABASE IF NOT EXISTS ddl_lab;
USE ddl_lab;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

INSERT INTO orders VALUES
  (1, 101, NOW()),
  (2, 102, NOW());

会话 A 打开事务并访问表,但不提交:

sql
USE ddl_lab;
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;

此时会话 A 的 SELECT 已经返回,但事务仍然打开。会话 B 执行一个支持在线创建索引的 ALTER:

sql
ALTER TABLE ddl_lab.orders
  ADD INDEX idx_customer_id (customer_id),
  ALGORITHM=INPLACE,
  LOCK=NONE;

预期现象是会话 B 可能停在等待元数据锁,而不是立即进入索引构建。此时在会话 A 执行:

sql
COMMIT;

如果没有其他阻塞者,ALTER 随后即可继续。这个实验最重要的结论是:阻塞 DDL 的会话不需要正在写数据,甚至不需要正在执行 SQL;只要事务没有结束且仍持有冲突的 MDL,就足够了。

等待中的 DDL 为什么会放大业务阻塞

真正危险的并不只是“ALTER 自己等一会儿”。当 DDL 为取得排他 MDL 进入等待队列后,后续访问同一对象的请求也可能因为 MDL 的调度优先级而排队,于是一个原本只影响发布脚本的问题,会扩散成应用请求堆积。

典型链路是:

长事务持有兼容业务访问的 MDL → ALTER 请求排他 MDL 并等待 → 新来的业务 SQL 也开始等待 → 连接池占满 → 请求超时

MySQL 的 MDL 调度会优先处理更高优先级的锁请求,写类请求通常高于读类请求;因此,排队中的 DDL 可能成为后续请求前方的一道“闸门”。:contentReference[oaicite:3]{index=3}

在前面的实验中,让会话 B 保持等待,再从会话 C 执行:

sql
SELECT COUNT(*) FROM ddl_lab.orders;

如果该请求也进入等待,你看到的就不是“DDL 慢”,而是典型的 MDL 队列放大。此时盲目增加应用连接数通常不会解决问题,反而可能制造更多等待者;应该先找出最前面的持锁者和等待中的 DDL。

LOCK=NONE 和 INSTANT 也有边界

LOCK=NONE 最容易被误读成“无锁”。它实际表达的是:如果这个 ALTER 操作支持,就要求在主体执行阶段允许并发读写;如果做不到,语句直接报错,而不是自动满足一个不存在的“全程无锁”承诺。MySQL 8.4 的 ALTER TABLE 文档就是按并发能力定义 LOCK=NONE。:contentReference[oaicite:4]{index=4}

ALGORITHM=INSTANT 也不是 MDL 的例外。INSTANT 不需要重写表数据,通常极快,但仍要修改数据字典并发布新的表定义,因此可能短暂请求排他 MDL;如果前面正好有长事务,它依旧可以等待。MySQL 8.4 文档明确指出,INSTANT 执行期间可能短暂取得排他元数据锁。:contentReference[oaicite:5]{index=5}

所以评估“会不会阻塞”时,不要只问这两个问题:

  • 是否 INPLACE 或 INSTANT
  • 是否支持 LOCK=NONE

还要再问两个更接近事故本质的问题:执行前是否存在长事务,以及 DDL 最终切换元数据时能否迅速取得排他 MDL。

用锁链而不是进度判断故障点

遇到 Waiting for table metadata lock,第一目标不是猜 ALTER 还要多久,而是把“等待者—阻塞者—对象”三者连起来。MySQL 8.4 的 sys.schema_table_lock_waits 直接给出等待 MDL 的会话及其阻塞者,适合先做快速定位。:contentReference[oaicite:6]{index=6}

sql
SELECT
  object_schema,
  object_name,
  waiting_pid,
  waiting_query,
  waiting_query_secs,
  blocking_pid,
  blocking_account,
  blocking_lock_type,
  sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = 'ddl_lab'
  AND object_name = 'orders';

预期结果会把正在等待的 ALTER 与阻塞它的连接关联起来。不要看到 sql_kill_blocking_connection 就直接执行;先确认阻塞会话是否属于关键业务事务、是否可以正常提交或回滚,以及终止连接会造成什么业务后果。

如果还需要查看当前授予和等待中的 MDL,可以直接检查 Performance Schema:

sql
SELECT
  ml.OBJECT_SCHEMA,
  ml.OBJECT_NAME,
  ml.LOCK_TYPE,
  ml.LOCK_DURATION,
  ml.LOCK_STATUS,
  t.PROCESSLIST_ID,
  t.PROCESSLIST_USER,
  t.PROCESSLIST_TIME,
  t.PROCESSLIST_STATE,
  t.PROCESSLIST_INFO
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 = 'ddl_lab'
  AND ml.OBJECT_NAME = 'orders'
ORDER BY ml.LOCK_STATUS, t.PROCESSLIST_TIME DESC;

判断时优先看三件事:是否存在长时间未结束的事务,ALTER 是在开始阶段还是最终发布阶段等待,以及等待中的 DDL 后面是否已经堆起业务请求。存在外键时还要扩大对象范围,因为 MySQL 会按需要把 MDL 扩展到外键相关表。:contentReference[oaicite:7]{index=7}

上线治理上的核心动作也由此变得清晰:DDL 前先清理异常长事务;发布会话设置合理的元数据锁等待上限,避免 ALTER 无限排队;执行中同时观察 DDL 进度和 MDL 队列;一旦形成“长事务 → DDL → 业务请求”的等待链,优先解除链头,而不是只盯着最显眼的 ALTER 线程。

目录与阅读设置

记录一个想法

MySQL Online DDL 为什么仍会阻塞

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

登录哪都通