发布时间:2025-11-05 07:24:52 来源:创站工坊 作者:数据库
在生产环境中,生产锁故DBA 经常需要执行 DDL 变更操作。运维引在此过程中,脚本无法获取 MDL(元数据锁)的障排问题时有发生。
当执行 show processlist 命令时,生产锁故若出现 waiting for table metadata lock 提示,运维引这表明数据库遭遇了 MDL 元数据锁问题。脚本

为此,障排笔者结合以往生产故障案例,生产锁故梳理 MDL 锁问题的运维引排查思路与方法。
生产运维脚本调用了连接池,但在执行完数据库操作后,障排未关闭数据库游标与连接,生产锁故这为后续的运维引 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}")1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34. 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);1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.由于等待获取 MDL 锁,对该表的任何操作都处于阻塞状态,严重影响业务。
从查询结果可以推断,有一个事务(线程 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)1.2.3.4.5.6.7.8. 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)1.2.3.4.5.6.7.8.综上所述:select 查询会话产生的 SHARED_READ(共享读锁),导致 SHARED_UPGRADABLE(共享升级锁)无法升级为 EXCLUSIVE (排他锁),故导致 DDL 挂起。
为了解决 DDL 挂起的问题,需要杀死持有 order_info 表共享读锁的相关事务。
复制kill 392575;1.执行上述命令后,可以看到 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: 01.2.3.锁类型
作用范围
核心作用
查看方法
行锁
InnoDB
存储引擎层
实现事务并发控制与数据一致性,通过索引记录锁标志锁定特定行,执行中自动获取和释放
可通过 SHOW ENGINE INNODB STATUS 查看行锁相关信息
MDL 锁
MySQL
Server 层
保护表元数据,操作表时自动获取,防止表结构被修改
若有事务持有 MDL 写锁,其他等待获取 MDL 锁的云服务器提供商会话会显示处于 Waiting for table metadata lock 状态。
全局锁
MySQL
Server 层
对整个数据库实例锁定,执行 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 锁,以防止其他事务在表结构修改过程中对表进行读写操作。
5.3 如何优化与避免 MDL 锁MDL 锁一旦发生,会对业务造成极大影响,因为后续所有对该表的访问都会被阻塞,导致连接积压。为了尽量避免 MDL 锁的发生,以下是几点优化建议:
开启 metadata_locks 表记录 MDL 锁,以便更好地监控和分析锁的免费信息发布网使用情况。设置参数 lock_wait_timeout 为较小值,使被阻塞的操作能够主动停止,避免长时间等待。规范使用事务,及时提交事务,避免使用大事务,减少锁的持有时间。增强监控告警,及时发现 MDL 锁问题,以便及时采取措施解决。将 DDL 操作及备份操作放在业务低峰期执行,减少对业务的影响。少用工具开启事务进行查询,图形化工具使用后要及时关闭,避免不必要的锁占用。规范运维脚本的使用,避免出现未关闭数据库游标与连接等情况,本次故障就是由这种情况引发的。随便看看