生產運維腳本引發的 MDL 鎖故障排查之旅
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: 05. 總結
5.1 鎖分類
鎖類型 | 作用范圍 | 核心作用 | 查看方法 |
行鎖 | InnoDB 存儲引擎層 | 實現事務并發控制與數據一致性，通過索引記錄鎖標志鎖定特定行，執行中自動獲取和釋放 | 可通過 |
MDL 鎖 | MySQL Server 層 | 保護表元數據，操作表時自動獲取，防止表結構被修改 | 若有事務持有 MDL 寫鎖，其他等待獲取 MDL 鎖的會話會顯示處于 |
全局鎖 | MySQL Server 層 | 對整個數據庫實例鎖定，執行 FLUSH TABLES WITH READ LOCK 獲取全局讀鎖，使數據庫只讀，阻塞寫操作，常用于數據庫邏輯備份保證數據一致性 | 1. 2. 觀察寫操作會話，等待時顯示 |
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 操作及備份操作放在業務低峰期執行，減少對業務的影響。
- 少用工具開啟事務進行查詢，圖形化工具使用后要及時關閉，避免不必要的鎖占用。
- 規范運維腳本的使用，避免出現未關閉數據庫游標與連接等情況，本次故障就是由這種情況引發的。
