生产运维脚本引发的 MDL 锁故障排查之旅

爱可生开源社区

发布于 2025-05-21 15:03:41

15600

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：何文超，分享 MySQL 和 OceanBase 相关技术博文。个人博客【CSDN | 雅俗数据库】

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1300 字，预计阅读需要 4 分钟。

1. 故障背景

在生产环境中，DBA 经常需要执行 DDL 变更操作。在此过程中，无法获取 MDL（元数据锁）的问题时有发生。

当执行 show processlist 命令时，若出现 waiting for table metadata lock 提示，这表明数据库遭遇了 MDL 元数据锁问题。

为此，笔者结合以往生产故障案例，梳理 MDL 锁问题的排查思路与方法。

2. 问题重现

2.1 一个有隐患的脚本

生产运维脚本调用了连接池，但在执行完数据库操作后，未关闭数据库游标与连接，这为后续的 MDL 锁问题埋下了隐患。

import mysql.connector
from dbutils.pooled_db import PooledDB
# 数据库连接信息
pool = PooledDB(
    creator=mysql.connector,  # 使用mysql.connector作为数据库驱动
    mincached=1,  # 连接池中空闲连接的初始数量
    maxcached=10,  # 连接池中空闲连接的最大数量
    maxshared=3,  # 共享连接的最大数量
    maxconnections=15,  # 连接池允许的最大连接数
    blocking=True,  # 当连接池达到最大连接数时，是否阻塞等待
    host='xx.xx.xx.xx',
    user='wms',
    password='123456',
    database='wms',
    unix_socket='/data/mysql8.0.23-3306/mysql-8.0.23/mysql3306.sock'
)
try:
    # 从连接池中获取一个连接
    conn = pool.connection()
    cursor = conn.cursor()
    # 执行查询语句
    sql = "SELECT * FROM wms.order_info LIMIT 1;"
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(row)
    # 不释放连接和连接池，模拟连接未释放的情况
    # cursor.close()
    # conn.close()
    # 保持程序运行，方便在其他会话中执行 DDL 操作
    whileTrue:
        pass
except mysql.connector.Error as err:
    print(f"Error: {err}")

2.2 模拟生产 DDL 操作

变更窗口：DBA 在数据库中进行相关表的 DDL 操作时，问题逐渐显现。

// 执行脚本
[root@11-186-63-123 opt]# python3.8 pool.py

// 会话1：对该表加字段，执行 DDL 操作，发现 DDL 挂起
ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35);

// 会话2：检查数据库会话，发现产生 MDL 锁
mysql> select * from information_schema.processlist where command != 'Sleep';
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+
| ID     | USER            | HOST                | DB   | COMMAND          | TIME    | STATE                                                         | INFO                                                                |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+
|     57 | repl            | 11.186.63.118:36624 | NULL | Binlog Dump GTID | 2872846 | Master has sent all binlog to slave; waiting for more updates | NULL                                                                |
| 377524 | root            | localhost           | wms  | Query            |      37 | Waiting for table metadata lock                               | ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35)         |
|      5 | event_scheduler | localhost           | NULL | Daemon           | 3022562 | Waiting onempty queue                                        | NULL                                                                |
| 378462 | root            | localhost           | NULL | Query            |       0 | executing                                                     | select * from information_schema.processlist where command != 'Sleep' |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+
4rowsinset (0.00 sec)

// 会话3：读写操作均被阻塞，业务受到影响
mysql> select * from wms.order_info limit 1;
mysql> insert into order_info values(9911131,121,'2012-12-12 12:00:00','1',1);

由于等待获取 MDL 锁，对该表的任何操作都处于阻塞状态，严重影响业务。

3. 排查思路

3.1 查看当前已持有的 MDL 锁的事务信息

select OBJECT_SCHEMA,OBJECT_NAME,COLUMN_NAME,LOCK_TYPE,LOCK_STATUS,OWNER_THREAD_ID from performance_schema.metadata_locks where OBJECT_NAME='order_info';
+---------------+-------------+-------------+-------------------+-----------+-----------------+
| OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | LOCK_TYPE         | LOCK_STATUS | OWNER_THREAD_ID |
+---------------+-------------+-------------+-------------------+-----------+-----------------+
| wms           | order_info  | NULL        | SHARED_UPGRADABLE | GRANTED     |          392740 |
| wms           | order_info  | NULL        | EXCLUSIVE         | PENDING     |          392740 |
| wms           | order_info  | NULL        | SHARED_READ       | GRANTED     |          392747 |
+---------------+-------------+-------------+-------------------+-----------+-----------------+
3 rows in set (0.00 sec)

## LOCK_STATUS：表示锁的当前状态；GRANTED(已授予锁)，PENDING(等待授予锁)。

从查询结果可以推断，有一个事务（线程 ID 为 392747）持有 order_info 表的共享读锁，另一个事务（线程 ID 为 392740）持有 SHARED_UPGRADABLE（共享升级锁），并试图将其升级为 EXCLUSIVE （排他锁），但由于共享锁的存在而等待。

3.2 根据线程 ID 获取 MySQL 的 processlist_id

mysql> select THREAD_ID,PROCESSLIST_ID from performance_schema.threads where thread_id in (392740,392747);
+-----------+----------------+
| THREAD_ID | PROCESSLIST_ID |
+-----------+----------------+
|    392740 |         392568 |
|    392747 |         392575 |
+-----------+----------------+
2 rows in set (0.00 sec)

3.3 根据 processlist_id 获取 sql_text

mysql> SELECT a.thread_id, a.sql_text  FROM performance_schema.events_statements_current a  WHERE a.THREAD_ID IN (     SELECT b.THREAD_ID      FROM performance_schema.threads b      WHERE b.PROCESSLIST_ID IN (392568, 392575) );
+-----------+-------------------------------------------------------------+
| thread_id | sql_text                                                    |
+-----------+-------------------------------------------------------------+
|    392740 | ALTER TABLE wms.order_info MODIFY COLUMN status varchar(30) |
|    392747 | SELECT * FROM wms.order_info LIMIT 1                        |
+-----------+-------------------------------------------------------------+
2 rows inset (0.00 sec)

综上所述：select 查询会话产生的 SHARED_READ（共享读锁），导致 SHARED_UPGRADABLE（共享升级锁）无法升级为 EXCLUSIVE （排他锁），故导致 DDL 挂起。

4. 解决方案

为了解决 DDL 挂起的问题，需要杀死持有 order_info 表共享读锁的相关事务。

kill 392575;

执行上述命令后，可以看到 DDL 操作成功执行。

mysql> ALTER TABLE wms.order_info MODIFY COLUMN status varchar(30);
Query OK, 1000001 rows affected (14 min 53.45 sec)
Records: 1000001  Duplicates: 0  Warnings: 0

5. 总结

5.1 锁分类

锁类型	作用范围	核心作用	查看方法
行锁	InnoDB 存储引擎层	实现事务并发控制与数据一致性，通过索引记录锁标志锁定特定行，执行中自动获取和释放	可通过 SHOW ENGINE INNODB STATUS 查看行锁相关信息
MDL 锁	MySQLServer 层	保护表元数据，操作表时自动获取，防止表结构被修改	若有事务持有 MDL 写锁，其他等待获取 MDL 锁的会话会显示处于 Waiting for table metadata lock 状态。
全局锁	MySQLServer 层	对整个数据库实例锁定，执行 FLUSH TABLES WITH READ LOCK 获取全局读锁，使数据库只读，阻塞写操作，常用于数据库逻辑备份保证数据一致性	1. SHOW PROCESSLIST 查看加锁会话语句2. 观察写操作会话，等待时显示 Waiting for global read lock

5.2 共享升级锁

SHARED_UPGRADABLE 是一种元数据锁（Metadata Lock，简称 MDL），属于 MySQL 中的锁类型之一。它允许持有该锁的事务在特定条件下将锁升级为其他类型，如 EXCLUSIVE 锁或 SHARED_NO_WRITE 锁。

升级机制

当事务持有 SHARED_UPGRADABLE 锁时，可以根据操作需求将其升级为 SHARED_NO_WRITE 锁（允许读取但不允许写入）或 EXCLUSIVE 锁（独占锁，不允许其他事务同时访问）。这种升级机制在数据库操作中用于确保数据的一致性和并发控制。例如，在对表结构进行修改（如 DDL操作）时，可能需要将 SHARED_UPGRADABLE 锁升级为 EXCLUSIVE 锁，以防止其他事务在表结构修改过程中对表进行读写操作。