MySQL DDL 操作与 MDL 锁阻塞分析

在 MySQL 中执行数据定义语言(DDL)操作（如 ALTER TABLE）时，会获取元数据锁(MDL, Metadata Lock)，这可能导致正常查询被阻塞。下面对此过程进行详细分析。

MDL 锁的基本原理

元数据锁(MDL)用于保护数据库对象的结构，确保在结构更改时不会有并发操作破坏数据一致性。

DDL 操作阻塞查询的过程

1. 正常状态下的查询

在正常情况下，查询会获取共享MDL锁（S锁），多个查询可以同时获取共享锁并行执行。

sequenceDiagram
    participant A as 会话A(查询)
    participant MDL as MDL锁系统
    participant Table as 表
    
    A->>MDL: 请求共享MDL锁(S锁)
    MDL->>A: 授予共享锁
    A->>Table: 执行SELECT查询
    A->>MDL: 释放共享锁

2. DDL 操作请求排他锁

当执行 ALTER TABLE 等DDL操作时，会请求排他MDL锁（X锁）。此时：

• 新的DDL操作需要等待当前所有查询释放S锁
• 新的查询请求会被阻塞，无法获取S锁

sequenceDiagram
    participant A as 会话A(查询)
    participant B as 会话B(DDL操作)
    participant C as 会话C(新查询)
    participant MDL as MDL锁系统
    participant Table as 表
    
    A->>MDL: 获取共享MDL锁(S锁)
    MDL->>A: 授予共享锁
    Note over A,Table: 会话A正在执行长时间查询...
    
    B->>MDL: 请求排他MDL锁(X锁)
    Note over B,MDL: 会话B被阻塞(等待A释放S锁)
    
    C->>MDL: 请求共享MDL锁(S锁)
    Note over C,MDL: 会话C被阻塞(等待B的X锁请求处理完)
    
    Note over A,C: 此时形成了锁等待队列
    
    A->>MDL: 释放共享锁
    MDL->>B: 授予排他锁
    B->>Table: 执行ALTER TABLE
    B->>MDL: 释放排他锁
    MDL->>C: 授予共享锁
    C->>Table: 执行SELECT查询
    C->>MDL: 释放共享锁

3. MDL 锁阻塞的级联效应

级联阻塞效应：

graph TD
    A[会话A: 持有共享MDL锁<br>长时间查询] --> B[会话B: 等待排他MDL锁<br>ALTER TABLE]
    B --> C[会话C: 等待共享MDL锁<br>SELECT查询]
    C --> D[会话D: 等待共享MDL锁<br>SELECT查询]
    D --> E[会话E: 等待共享MDL锁<br>SELECT查询]
    
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#bbf,stroke:#333,stroke-width:2px
    style C fill:#dfd,stroke:#333,stroke-width:2px
    style D fill:#dfd,stroke:#333,stroke-width:2px
    style E fill:#dfd,stroke:#333,stroke-width:2px

1. 会话 A：长时间运行的查询持有S锁
2. 会话 B：DDL操作等待X锁
3. 会话 C、D、E：新查询请求S锁被阻塞在DDL后面
4. 更多查询被阻塞，形成队列

实际案例分析

实验准备

首先创建一个测试表：

CREATE DATABASE mdl_test;
USE mdl_test;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一些测试数据
INSERT INTO users (name, email) VALUES 
('User 1', 'user1@example.com'),
('User 2', 'user2@example.com'),
('User 3', 'user3@example.com'),
('User 4', 'user4@example.com'),
('User 5', 'user5@example.com');

实验步骤

终端1（会话A）：开启显式事务并执行查询，但不提交

USE mdl_test;

-- 开启事务
START TRANSACTION;

-- 执行一个查询，此时会获取MDL共享锁
SELECT * FROM users;

-- 重要：此时不要提交事务，保持事务开启状态

终端2（会话B）：尝试执行DDL操作

USE mdl_test;

-- 尝试执行ALTER TABLE操作，此操作需要获取MDL排他锁
-- 由于会话A持有MDL共享锁，此操作会被阻塞
ALTER TABLE users ADD COLUMN address VARCHAR(200);

-- 此命令将会挂起等待

终端3（会话C）：尝试执行普通查询

USE mdl_test;

-- 在会话B被阻塞后，尝试执行普通查询
-- 此查询也需要获取MDL共享锁，但因为会话B正在等待排他锁，所以会被阻塞
SELECT * FROM users WHERE id = 1;

-- 此命令将会挂起等待

终端4（会话D）：查看当前的锁状态

-- 查看当前正在执行的查询
SHOW PROCESSLIST;

-- 查看元数据锁情况（MySQL 8.0+）
SELECT * FROM performance_schema.metadata_locks;

-- 查看锁等待情况
SELECT * FROM sys.schema_table_lock_waits;

终端1（会话A）：提交事务释放锁

-- 回到终端1，提交事务，释放MDL共享锁
COMMIT;

如何诊断MDL锁阻塞

可以通过以下命令查看正在等待的锁：

-- 查看当前正在执行的进程
SHOW PROCESSLIST;

-- 查看元数据锁情况
SELECT * FROM performance_schema.metadata_locks;

-- 查看阻塞关系
SELECT 
  b.thread_id AS '被阻塞线程',
  b.processlist_id AS '被阻塞会话ID',
  b.processlist_info AS '被阻塞查询',
  c.thread_id AS '阻塞线程',
  c.processlist_id AS '阻塞会话ID',
  c.processlist_info AS '阻塞查询'
FROM 
  performance_schema.metadata_locks a
  JOIN performance_schema.threads b ON a.owner_thread_id = b.thread_id
  JOIN performance_schema.metadata_locks m ON m.object_name = a.object_name
  JOIN performance_schema.threads c ON m.owner_thread_id = c.thread_id
WHERE 
  a.lock_status = 'PENDING';

-- 查看等待事件
SELECT * FROM sys.schema_table_lock_waits;

-- 终止长时间运行的查询
KILL [阻塞会话ID];

-- 查看当前阻塞的MDL锁
SELECT 
    b.id AS blocked_id,
    b.user AS blocked_user,
    b.host AS blocked_host,
    b.command AS blocked_command,
    b.time AS blocked_time,
    b.state AS blocked_state,
    b.info AS blocked_query,
    p.id AS blocking_id,
    p.user AS blocking_user,
    p.host AS blocking_host,
    p.command AS blocking_command,
    p.time AS blocking_time,
    p.state AS blocking_state,
    p.info AS blocking_query
FROM 
    information_schema.processlist b
JOIN 
    information_schema.processlist p
WHERE 
    b.state = 'Waiting for table metadata lock' 
    AND p.state <> 'Waiting for table metadata lock'
    AND p.info IS NOT NULL
    AND p.info NOT LIKE '%information_schema.processlist%';

-- 更详细地查看MDL锁信息（MySQL 8.0+）
SELECT 
    p.id, 
    p.user, 
    p.host, 
    p.db, 
    p.command, 
    p.time, 
    p.state, 
    p.info, 
    m.THREAD_ID, 
    m.OBJECT_NAME, 
    m.LOCK_TYPE, 
    m.LOCK_DURATION, 
    m.LOCK_STATUS 
FROM 
    performance_schema.metadata_locks m 
JOIN 
    information_schema.processlist p ON m.THREAD_ID = p.id 
WHERE 
    m.OBJECT_SCHEMA = 'mdl_test' 
    AND m.OBJECT_NAME = 'users';
    
-- 查找长时间运行的事务
SELECT 
    trx_id, 
    trx_state, 
    trx_started, 
    trx_mysql_thread_id, 
    trx_query, 
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds 
FROM 
    information_schema.innodb_trx 
WHERE 
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10 
ORDER BY 
    duration_seconds DESC;
    
-- 如果情况紧急且无法详细分析，可以使用以下命令找出并杀死所有持有MDL锁的长时间运行的事务
-- 查找所有相关的阻塞进程
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist 
WHERE db = 'mdl_test' 
  AND time > 10  -- 设置合理的时间阈值
  AND state != 'Waiting for table metadata lock';
  
  
-- 查看当前活跃的事务
SELECT * FROM information_schema.INNODB_TRX;

Performance Schema 和 metadata_locks

如果无法查看 metadata_locks 可能是还没有配置。

检查并启用 Performance Schema

首先，确认 Performance Schema 是否已启用：

SHOW VARIABLES LIKE 'performance_schema';

如果返回值是 OFF，您需要在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中添加以下行，然后重启 MySQL：

performance_schema=ON

检查并启用 metadata_locks 的监控

即使 Performance Schema 已启用，metadata_locks 表也可能没有被设置为收集数据。检查是否启用：

SELECT * FROM performance_schema.setup_instruments 
WHERE NAME = 'wait/lock/metadata/sql/mdl';

如果 ENABLED 或 TIMED 列为 'NO'，请启用它：

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME = 'wait/lock/metadata/sql/mdl';

还需要确保 metadata_locks 表被启用：

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'global_instrumentation';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%locks%';