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 更适合被理解为“短排他窗口—长在线阶段—短排他窗口”,而不是从开始到结束都保持同一种锁状态。
提交 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}
可以用三个会话复现这个机制。先准备实验表:
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 打开事务并访问表,但不提交:
USE ddl_lab;
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;此时会话 A 的 SELECT 已经返回,但事务仍然打开。会话 B 执行一个支持在线创建索引的 ALTER:
ALTER TABLE ddl_lab.orders
ADD INDEX idx_customer_id (customer_id),
ALGORITHM=INPLACE,
LOCK=NONE;预期现象是会话 B 可能停在等待元数据锁,而不是立即进入索引构建。此时在会话 A 执行:
COMMIT;如果没有其他阻塞者,ALTER 随后即可继续。这个实验最重要的结论是:阻塞 DDL 的会话不需要正在写数据,甚至不需要正在执行 SQL;只要事务没有结束且仍持有冲突的 MDL,就足够了。
等待中的 DDL 为什么会放大业务阻塞
真正危险的并不只是“ALTER 自己等一会儿”。当 DDL 为取得排他 MDL 进入等待队列后,后续访问同一对象的请求也可能因为 MDL 的调度优先级而排队,于是一个原本只影响发布脚本的问题,会扩散成应用请求堆积。
典型链路是:
长事务持有兼容业务访问的 MDL → ALTER 请求排他 MDL 并等待 → 新来的业务 SQL 也开始等待 → 连接池占满 → 请求超时
MySQL 的 MDL 调度会优先处理更高优先级的锁请求,写类请求通常高于读类请求;因此,排队中的 DDL 可能成为后续请求前方的一道“闸门”。:contentReference[oaicite:3]{index=3}
在前面的实验中,让会话 B 保持等待,再从会话 C 执行:
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}
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:
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 线程。