- 当前位置：首页 >域名 >生产运维脚本引发的 MDL 锁故障排查之旅
生产运维脚本引发的 MDL 锁故障排查之旅
发布时间：2025-11-05 08:13:51 来源：码上建站 作者：域名
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}")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 锁，对该表的任何操作都处于阻塞状态，严重影响业务。
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(等待授予锁)。1.2.3.4.5.6.7.8.9.10.11.从查询结果可以推断，有一个事务（线程 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 挂起。
4. 解决方案
为了解决 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.5. 总结
5.1 锁分类锁类型
作用范围
核心作用
查看方法
行锁
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 操作及备份操作放在业务低峰期执行，减少对业务的影响。少用工具开启事务进行查询，图形化工具使用后要及时关闭，避免不必要的锁占用。规范运维脚本的使用，避免出现未关闭数据库游标与连接等情况，本次故障就是由这种情况引发的。- 电脑凤凰系统使用技巧教程（掌握电脑凤凰系统的关键技巧，提升工作效率）
- 电信版5s怎么样？详细分析！（一键了解电信版5s的性能和功能）
- JBL耳机（手机赠送的JBL耳机音质细腻，带来卓越的音乐享受）
- DSP音效的魅力（以数字信号处理为核心的音效技术为您带来震撼感官体验）
- 一体机系统重装教程（简明易懂的一体机系统重装步骤，帮你重拾流畅体验）
- 开博尔盒子——一款功能强大的智能家居设备（了解开博尔盒子，打造智能舒适的家居生活）
- 8G1600内存条（选择8G1600内存条，享受流畅运行和高效工作）
- 以奇鱼旅行怎么样（探索奇幻海底世界的绝佳旅行体验）
- 电脑开机错误00005（深入了解电脑开机错误00005，避免数据丢失和系统崩溃）
- 蚂蜂窝特价（以蚂蜂窝特价怎么样？一站式旅游攻略平台为您提供全面的信息）
随便看看
- Copyright © 2025 Powered by 生产运维脚本引发的 MDL 锁故障排查之旅,码上建站 滇ICP备2023006006号-47sitemap