MySQL / 系统课时
观察元数据锁与长事务
观察元数据锁与长事务
Online DDL 是否“在线”,首先取决于能否拿到需要的元数据锁。一个业务会话即使只做了普通查询,只要事务迟迟不结束,也可能让 ALTER TABLE 长时间停在 MDL 等待。本节用三个会话复现阻塞,并通过 Performance Schema 找到等待者、持有者和事务持续时间。
学习目标
- 从
metadata_locks中识别GRANTED与PENDING的表级 MDL - 用
events_transactions_current估算当前事务已经持续的时间 - 建立执行 DDL 前“先找长事务,再确认等待链”的排查顺序
准备可复现对象
以下步骤在隔离的 MySQL 8.4 实例中执行,建议使用具备 Performance Schema 读取和配置权限的管理账号。先确认版本与 Performance Schema 已启用。
SELECT VERSION() AS version, @@performance_schema AS performance_schema;预期输出
version performance_schema
8.4.<VALUE> 1事务事件的采集可能未全部开启。为保证后面的持续时间查询有数据,显式启用事务 instrument 和当前事务 consumer。
UPDATE performance_schema.setup_instruments
SET ENABLED='YES', TIMED='YES'
WHERE NAME='transaction';
UPDATE performance_schema.setup_consumers
SET ENABLED='YES'
WHERE NAME='events_transactions_current';
SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME='events_transactions_current';预期结果:两个 UPDATE 都应成功;若原本已经启用,受影响行数可以为 0。最后一条查询应返回:
NAME ENABLED
events_transactions_current YES再创建一个专门用于 MDL 实验的小表。这里刻意不用大表,因为本节要观察的是“DDL 尚未开始执行就被 MDL 卡住”,与表数据量无关。
DROP DATABASE IF EXISTS ddl_mdl_lab;
CREATE DATABASE ddl_mdl_lab;
CREATE TABLE ddl_mdl_lab.mdl_demo (
id BIGINT UNSIGNED PRIMARY KEY,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT INTO ddl_mdl_lab.mdl_demo VALUES
(1, 'NEW', '2026-01-01 10:00:00'),
(2, 'PAID', '2026-01-01 10:01:00'),
(3, 'PAID', '2026-01-01 10:02:00');
SELECT COUNT(*) AS rows_in_demo FROM ddl_mdl_lab.mdl_demo;预期输出
rows_in_demo
3复现 DDL 被长事务阻塞
打开三个独立 MySQL 会话。会话 A 开启事务并读取目标表,但故意不提交。MySQL 会把该事务对表元数据的锁保持到事务结束。
先在会话 A 执行以下语句,并保持事务不提交。
SET autocommit=0;
START TRANSACTION;
SELECT CONNECTION_ID() AS blocker_connection;
SELECT * FROM ddl_mdl_lab.mdl_demo WHERE id=1;预期输出
Query OK, 0 rows affected
Query OK, 0 rows affected
blocker_connection
<ID>
id status created_at
1 NEW 2026-01-01 10:00:00保持会话 A 不动。然后在会话 B 发起一个本身可以使用 ALGORITHM=INSTANT 的加列操作。
随后在会话 B 发起 DDL。
SELECT CONNECTION_ID() AS ddl_connection;
ALTER TABLE ddl_mdl_lab.mdl_demo
ADD COLUMN note VARCHAR(64) NULL,
ALGORITHM=INSTANT;预期结果:第一条查询立即返回会话 B 的 <ID>,随后 ALTER TABLE 保持等待,不会立刻出现 Query OK。这说明“算法执行很快”和“能立即获得 MDL”是两个不同问题。

从 Performance Schema 找等待链
在会话 C 先查看目标表当前的 MDL。LOCK_STATUS='PENDING' 表示请求尚未获得;GRANTED 表示锁已经持有。DDL 可能同时持有其他已授予的元数据锁,因此不要只数行数,要先找 PENDING。
SELECT
t.PROCESSLIST_ID AS connection_id,
ml.LOCK_TYPE,
ml.LOCK_DURATION,
ml.LOCK_STATUS
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE='TABLE'
AND ml.OBJECT_SCHEMA='ddl_mdl_lab'
AND ml.OBJECT_NAME='mdl_demo'
ORDER BY ml.LOCK_STATUS, connection_id, ml.LOCK_TYPE;预期输出
connection_id LOCK_TYPE LOCK_DURATION LOCK_STATUS
<ID> <LOCK_TYPE> TRANSACTION GRANTED
<ID> EXCLUSIVE <VALUE> PENDING实际可能出现额外的 GRANTED 行,因为同一个 DDL 会话可能先取得兼容的元数据锁,再等待更强的锁。排查重点是确认目标表上存在 PENDING,并把它的 OWNER_THREAD_ID 映射到连接。
接着把同一对象上的待授予锁与已授予锁关联起来。对等待 EXCLUSIVE 的 DDL 来说,同一表上其他会话尚未释放的已授予锁就是优先检查的阻塞来源。
SELECT
wt.PROCESSLIST_ID AS waiting_connection,
bt.PROCESSLIST_ID AS blocking_connection,
wml.LOCK_TYPE AS waiting_lock,
bml.LOCK_TYPE AS blocking_lock
FROM performance_schema.metadata_locks AS wml
JOIN performance_schema.metadata_locks AS bml
ON bml.OBJECT_TYPE = wml.OBJECT_TYPE
AND bml.OBJECT_SCHEMA <=> wml.OBJECT_SCHEMA
AND bml.OBJECT_NAME <=> wml.OBJECT_NAME
AND bml.LOCK_STATUS = 'GRANTED'
AND bml.OWNER_THREAD_ID <> wml.OWNER_THREAD_ID
JOIN performance_schema.threads AS wt
ON wt.THREAD_ID = wml.OWNER_THREAD_ID
JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = bml.OWNER_THREAD_ID
WHERE wml.LOCK_STATUS='PENDING'
AND wml.OBJECT_TYPE='TABLE'
AND wml.OBJECT_SCHEMA='ddl_mdl_lab'
AND wml.OBJECT_NAME='mdl_demo';预期输出
waiting_connection blocking_connection waiting_lock blocking_lock
<ID> <ID> EXCLUSIVE <LOCK_TYPE>这条查询是针对单表 DDL 的排查视角:当目标锁是 EXCLUSIVE 时,同一对象上的其他会话持锁都值得核查。复杂场景还可能涉及多个对象或更多兼容性规则,因此生产排障时应结合 SQL、事务和对象范围确认真正的业务阻塞者。
判断事务是否已经过长
找到 blocking_connection 后,不要立刻 KILL。先确认它是否处于活动事务,以及已经持续多久。Performance Schema 的计时单位是皮秒,因此除以 1000000000000 可换算为秒。
SELECT
t.PROCESSLIST_ID AS connection_id,
ROUND(et.TIMER_WAIT / 1000000000000, 3) AS trx_seconds,
et.STATE,
et.AUTOCOMMIT,
et.ACCESS_MODE
FROM performance_schema.events_transactions_current AS et
JOIN performance_schema.threads AS t
ON t.THREAD_ID = et.THREAD_ID
WHERE et.END_EVENT_ID IS NULL
AND et.STATE='ACTIVE'
ORDER BY et.TIMER_WAIT DESC;预期输出
connection_id trx_seconds STATE AUTOCOMMIT ACCESS_MODE
<ID> <VALUE> ACTIVE NO READ WRITEtrx_seconds 会持续增长。即使会话当前显示为 Sleep,只要事务没有提交,它仍可能继续持有事务周期的 MDL。DDL 前置检查因此不能只看“当前有没有慢 SQL”,还要看“有没有迟迟未结束的事务”。
释放阻塞并验证恢复
回到会话 A 提交事务。该事务持有的 MDL 会在事务结束时释放,会话 B 随后获得所需锁并继续执行。
COMMIT;
SET autocommit=1;预期输出
Query OK, 0 rows affected
Query OK, 0 rows affected此时会话 B 应返回成功结果。
Query OK, 0 rows affected最后在会话 C 验证目标表已经没有 PENDING 的 MDL,并确认新列存在。
SELECT COUNT(*) AS pending_mdl
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_SCHEMA='ddl_mdl_lab'
AND OBJECT_NAME='mdl_demo'
AND LOCK_STATUS='PENDING';
SHOW COLUMNS FROM ddl_mdl_lab.mdl_demo LIKE 'note';预期输出
pending_mdl
0
Field Type Null Key Default Extra
note varchar(64) YES NULL执行线上 DDL 前,最实用的顺序是先筛活动时间异常的事务,再检查目标对象是否已有 MDL 等待,最后把 PENDING 请求映射到具体阻塞连接。这样可以在真正执行表结构变更前发现“算法本身在线,但入口被长事务堵住”的风险。