MySQL / 系统课时

观察元数据锁与长事务

观察元数据锁与长事务

Online DDL 是否“在线”,首先取决于能否拿到需要的元数据锁。一个业务会话即使只做了普通查询,只要事务迟迟不结束,也可能让 ALTER TABLE 长时间停在 MDL 等待。本节用三个会话复现阻塞,并通过 Performance Schema 找到等待者、持有者和事务持续时间。

学习目标

  • 从 metadata_locks 中识别 GRANTED 与 PENDING 的表级 MDL
  • 用 events_transactions_current 估算当前事务已经持续的时间
  • 建立执行 DDL 前“先找长事务,再确认等待链”的排查顺序

准备可复现对象

以下步骤在隔离的 MySQL 8.4 实例中执行,建议使用具备 Performance Schema 读取和配置权限的管理账号。先确认版本与 Performance Schema 已启用。

sql
SELECT VERSION() AS version, @@performance_schema AS performance_schema;

预期输出

text
version      performance_schema
8.4.<VALUE>  1

事务事件的采集可能未全部开启。为保证后面的持续时间查询有数据,显式启用事务 instrument 和当前事务 consumer。

sql
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。最后一条查询应返回:

text
NAME                         ENABLED
events_transactions_current  YES

再创建一个专门用于 MDL 实验的小表。这里刻意不用大表,因为本节要观察的是“DDL 尚未开始执行就被 MDL 卡住”,与表数据量无关。

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

预期输出

text
rows_in_demo
3

复现 DDL 被长事务阻塞

打开三个独立 MySQL 会话。会话 A 开启事务并读取目标表,但故意不提交。MySQL 会把该事务对表元数据的锁保持到事务结束。

先在会话 A 执行以下语句,并保持事务不提交。

sql
SET autocommit=0;
START TRANSACTION;
SELECT CONNECTION_ID() AS blocker_connection;
SELECT * FROM ddl_mdl_lab.mdl_demo WHERE id=1;

预期输出

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

sql
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”是两个不同问题。

长事务持有 MDL 导致 DDL 等待的关系

从 Performance Schema 找等待链

在会话 C 先查看目标表当前的 MDL。LOCK_STATUS='PENDING' 表示请求尚未获得;GRANTED 表示锁已经持有。DDL 可能同时持有其他已授予的元数据锁,因此不要只数行数,要先找 PENDING。

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

预期输出

text
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 来说,同一表上其他会话尚未释放的已授予锁就是优先检查的阻塞来源。

sql
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';

预期输出

text
waiting_connection  blocking_connection  waiting_lock  blocking_lock
<ID>                <ID>                 EXCLUSIVE     <LOCK_TYPE>

这条查询是针对单表 DDL 的排查视角:当目标锁是 EXCLUSIVE 时,同一对象上的其他会话持锁都值得核查。复杂场景还可能涉及多个对象或更多兼容性规则,因此生产排障时应结合 SQL、事务和对象范围确认真正的业务阻塞者。

判断事务是否已经过长

找到 blocking_connection 后,不要立刻 KILL。先确认它是否处于活动事务,以及已经持续多久。Performance Schema 的计时单位是皮秒,因此除以 1000000000000 可换算为秒。

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

预期输出

text
connection_id  trx_seconds  STATE   AUTOCOMMIT  ACCESS_MODE
<ID>           <VALUE>      ACTIVE  NO          READ WRITE

trx_seconds 会持续增长。即使会话当前显示为 Sleep,只要事务没有提交,它仍可能继续持有事务周期的 MDL。DDL 前置检查因此不能只看“当前有没有慢 SQL”,还要看“有没有迟迟未结束的事务”。

释放阻塞并验证恢复

回到会话 A 提交事务。该事务持有的 MDL 会在事务结束时释放,会话 B 随后获得所需锁并继续执行。

sql
COMMIT;
SET autocommit=1;

预期输出

text
Query OK, 0 rows affected
Query OK, 0 rows affected

此时会话 B 应返回成功结果。

text
Query OK, 0 rows affected

最后在会话 C 验证目标表已经没有 PENDING 的 MDL,并确认新列存在。

sql
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';

预期输出

text
pending_mdl
0

Field  Type         Null  Key  Default  Extra
note   varchar(64)  YES        NULL

执行线上 DDL 前,最实用的顺序是先筛活动时间异常的事务,再检查目标对象是否已有 MDL 等待,最后把 PENDING 请求映射到具体阻塞连接。这样可以在真正执行表结构变更前发现“算法本身在线,但入口被长事务堵住”的风险。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

观察元数据锁与长事务

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

登录哪都通