生產(chǎn)運(yùn)維腳本引發(fā)的 MDL 鎖故障排查之旅
1. 故障背景
在生產(chǎn)環(huán)境中，DBA 經(jīng)常需要執(zhí)行 DDL 變更操作。在此過程中，無法獲取 MDL（元數(shù)據(jù)鎖）的問題時(shí)有發(fā)生。
當(dāng)執(zhí)行 show processlist 命令時(shí)，若出現(xiàn) waiting for table metadata lock 提示，這表明數(shù)據(jù)庫遭遇了 MDL 元數(shù)據(jù)鎖問題。
為此，筆者結(jié)合以往生產(chǎn)故障案例，梳理 MDL 鎖問題的排查思路與方法。
2. 問題重現(xiàn)
2.1 一個(gè)有隱患的腳本
生產(chǎn)運(yùn)維腳本調(diào)用了連接池，但在執(zhí)行完數(shù)據(jù)庫操作后，未關(guān)閉數(shù)據(jù)庫游標(biāo)與連接，這為后續(xù)的 MDL 鎖問題埋下了隱患。
import mysql.connector
from dbutils.pooled_db import PooledDB
# 數(shù)據(jù)庫連接信息
pool = PooledDB(
creator=mysql.connector, # 使用mysql.connector作為數(shù)據(jù)庫驅(qū)動(dòng)
mincached=1, # 連接池中空閑連接的初始數(shù)量
maxcached=10, # 連接池中空閑連接的最大數(shù)量
maxshared=3, # 共享連接的最大數(shù)量
maxconnections=15, # 連接池允許的最大連接數(shù)
blocking=True, # 當(dāng)連接池達(dá)到最大連接數(shù)時(shí)，是否阻塞等待
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:
# 從連接池中獲取一個(gè)連接
conn = pool.connection()
cursor = conn.cursor()
# 執(zhí)行查詢語句
sql = "SELECT * FROM wms.order_info LIMIT 1;"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
print(row)
# 不釋放連接和連接池，模擬連接未釋放的情況
# cursor.close()
# conn.close()
# 保持程序運(yùn)行，方便在其他會(huì)話中執(zhí)行 DDL 操作
whileTrue:
pass
except mysql.connector.Error as err:
print(f"Error: {err}")2.2 模擬生產(chǎn) DDL 操作
變更窗口：DBA 在數(shù)據(jù)庫中進(jìn)行相關(guān)表的 DDL 操作時(shí)，問題逐漸顯現(xiàn)。
// 執(zhí)行腳本
[root@11-186-63-123 opt]# python3.8 pool.py
// 會(huì)話1：對(duì)該表加字段，執(zhí)行 DDL 操作，發(fā)現(xiàn) DDL 掛起
ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35);
// 會(huì)話2：檢查數(shù)據(jù)庫會(huì)話，發(fā)現(xiàn)產(chǎn)生 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)
// 會(huì)話3：讀寫操作均被阻塞，業(yè)務(wù)受到影響
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 鎖，對(duì)該表的任何操作都處于阻塞狀態(tài)，嚴(yán)重影響業(yè)務(wù)。
3. 排查思路
3.1 查看當(dāng)前已持有的 MDL 鎖的事務(wù)信息
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：表示鎖的當(dāng)前狀態(tài)；GRANTED(已授予鎖)，PENDING(等待授予鎖)。從查詢結(jié)果可以推斷，有一個(gè)事務(wù)（線程 ID 為 392747）持有 order_info 表的共享讀鎖，另一個(gè)事務(wù)（線程 ID 為 392740）持有 SHARED_UPGRADABLE（共享升級(jí)鎖），并試圖將其升級(jí)為 EXCLUSIVE （排他鎖），但由于共享鎖的存在而等待。
3.2 根據(jù)線程 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 根據(jù) 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 查詢會(huì)話產(chǎn)生的 SHARED_READ（共享讀鎖），導(dǎo)致 SHARED_UPGRADABLE（共享升級(jí)鎖）無法升級(jí)為 EXCLUSIVE （排他鎖），故導(dǎo)致 DDL 掛起。
4. 解決方案
為了解決 DDL 掛起的問題，需要?dú)⑺莱钟?nbsp;order_info 表共享讀鎖的相關(guān)事務(wù)。
kill 392575;執(zhí)行上述命令后，可以看到 DDL 操作成功執(zhí)行。
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. 總結(jié)
5.1 鎖分類
鎖類型 | 作用范圍 | 核心作用 | 查看方法 |
行鎖 | InnoDB 存儲(chǔ)引擎層 | 實(shí)現(xiàn)事務(wù)并發(fā)控制與數(shù)據(jù)一致性，通過索引記錄鎖標(biāo)志鎖定特定行，執(zhí)行中自動(dòng)獲取和釋放 | 可通過 |
MDL 鎖 | MySQL Server 層 | 保護(hù)表元數(shù)據(jù)，操作表時(shí)自動(dòng)獲取，防止表結(jié)構(gòu)被修改 | 若有事務(wù)持有 MDL 寫鎖，其他等待獲取 MDL 鎖的會(huì)話會(huì)顯示處于 |
全局鎖 | MySQL Server 層 | 對(duì)整個(gè)數(shù)據(jù)庫實(shí)例鎖定，執(zhí)行 FLUSH TABLES WITH READ LOCK 獲取全局讀鎖，使數(shù)據(jù)庫只讀，阻塞寫操作，常用于數(shù)據(jù)庫邏輯備份保證數(shù)據(jù)一致性 | 1. 2. 觀察寫操作會(huì)話，等待時(shí)顯示 |
5.2 共享升級(jí)鎖
SHARED_UPGRADABLE 是一種元數(shù)據(jù)鎖（Metadata Lock，簡(jiǎn)稱 MDL），屬于 MySQL 中的鎖類型之一。它允許持有該鎖的事務(wù)在特定條件下將鎖升級(jí)為其他類型，如 EXCLUSIVE 鎖或 SHARED_NO_WRITE 鎖 。
升級(jí)機(jī)制
當(dāng)事務(wù)持有 SHARED_UPGRADABLE 鎖時(shí)，可以根據(jù)操作需求將其升級(jí)為 SHARED_NO_WRITE 鎖（允許讀取但不允許寫入）或 EXCLUSIVE 鎖（獨(dú)占鎖，不允許其他事務(wù)同時(shí)訪問）。這種升級(jí)機(jī)制在數(shù)據(jù)庫操作中用于確保數(shù)據(jù)的一致性和并發(fā)控制。例如，在對(duì)表結(jié)構(gòu)進(jìn)行修改（如 DDL操作）時(shí)，可能需要將 SHARED_UPGRADABLE 鎖升級(jí)為 EXCLUSIVE 鎖，以防止其他事務(wù)在表結(jié)構(gòu)修改過程中對(duì)表進(jìn)行讀寫操作。
5.3 如何優(yōu)化與避免 MDL 鎖
MDL 鎖一旦發(fā)生，會(huì)對(duì)業(yè)務(wù)造成極大影響，因?yàn)楹罄m(xù)所有對(duì)該表的訪問都會(huì)被阻塞，導(dǎo)致連接積壓。為了盡量避免 MDL 鎖的發(fā)生，以下是幾點(diǎn)優(yōu)化建議：
- 開啟 metadata_locks 表記錄 MDL 鎖，以便更好地監(jiān)控和分析鎖的使用情況。
- 設(shè)置參數(shù) lock_wait_timeout 為較小值，使被阻塞的操作能夠主動(dòng)停止，避免長時(shí)間等待。
- 規(guī)范使用事務(wù)，及時(shí)提交事務(wù)，避免使用大事務(wù)，減少鎖的持有時(shí)間。
- 增強(qiáng)監(jiān)控告警，及時(shí)發(fā)現(xiàn) MDL 鎖問題，以便及時(shí)采取措施解決。
- 將 DDL 操作及備份操作放在業(yè)務(wù)低峰期執(zhí)行，減少對(duì)業(yè)務(wù)的影響。
- 少用工具開啟事務(wù)進(jìn)行查詢，圖形化工具使用后要及時(shí)關(guān)閉，避免不必要的鎖占用。
- 規(guī)范運(yùn)維腳本的使用，避免出現(xiàn)未關(guān)閉數(shù)據(jù)庫游標(biāo)與連接等情況，本次故障就是由這種情況引發(fā)的。
