MySQL Metadata Lock (MDL) 核心機制補遺（二）：鎖類型兼容性矩陣與死鎖場景分析

MDL 鎖類型兼容性矩陣 (核心參考表)

「✓」=兼容 (可並發持有)、「✗」=衝突 (阻塞等待)、「→」=申請方向

關鍵兼容性結論

• IS 最弱：與除 X 外所有鎖兼容，DML 隱式獲取
• S/SH 兼容：多讀者可並發，寫者 (SW/X) 需等待
• SW 較強：僅兼容 IS，阻塞所有讀鎖 (S/SH)
• X 最強：與**所有鎖互斥**，DDL 獲取，**導致著名的「DDL 阻塞後續所有查詢」連鎖效應**

經典死鎖/阻塞場景深度剖析

場景一：長事務 + DDL + 並發查詢 = 全表阻塞 (最常見)

-- 會話 1：長事務 (未提交)
BEGIN;
SELECT * FROM t WHERE id = 1 FOR UPDATE;  -- 隱式持有表級 MDL_SHARED (IS)
-- 業務邏輯處理 10 分鐘...

-- 會話 2：DDL
ALTER TABLE t ADD COLUMN c INT;  -- 申請 MDL_EXCLUSIVE，被會話 1 阻塞，進入等待隊列

-- 會話 3-N：正常查詢
SELECT * FROM t;  -- 申請 MDL_SHARED (IS)，發現隊列中有 X 等待，**為防飢餓，MySQL 讓查詢在 X 之後排隊**

-- 結果：會話 2 等待會話 1，會話 3-N 等待會話 2，**整表所有查詢掛起**，業務癱瘓

場景二：FLUSH TABLES WITH READ LOCK (FTWRL) 全局阻塞

-- 備份腳本執行
FLUSH TABLES WITH READ LOCK;  -- 獲取 GLOBAL MDL_SHARED
-- 此時所有寫入 (DML/DDL) 全部阻塞
-- mysqldump --single-transaction 只會瞬間持有此鎖獲取 binlog 位點
-- 無 --single-transaction 會持有整個備份過程，**生產嚴禁**

場景三：外鍵檢查觸發隱式 SH 鎖

-- 表 child 有 FK 指向 parent
INSERT INTO child ...;  -- 除自身 IS 鎖外，需檢查 parent 獲取 MDL_SHARED_HIGH_PRIO
-- 若 parent 正在 DDL (X 鎖)，INSERT 阻塞
-- 若 parent 有長事務 (S 鎖)，INSERT 可並發 (SH 兼容 S)

監控與排查實戰 SQL

-- 1. 查看當前所有 MDL 持有/等待 (5.7+ performance_schema)
SELECT 
    OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
    LOCK_TYPE, LOCK_DURATION, LOCK_STATUS,
    THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_INFO
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING' OR OBJECT_NAME = 'your_table'\G

-- 2. 結合 processlist 定位阻塞源頭
SELECT * FROM information_schema.processlist 
WHERE STATE LIKE '%metadata lock%' OR INFO LIKE '%ALTER%'\G

-- 3. 歷史 DDL 執行記錄 (需開啟 general_log 或 audit_log)

生產環境緩解策略清單

1. 在線 DDL 工具：pt-online-schema-change / gh-ost，**完全繞過 MDL_EXCLUSIVE**，原理：建影子表→同步數據→原子切換
2. 拆分大事務：業務邏輯拆小，縮短事務持有 MDL 時間
3. 設置鎖等待超時：SET GLOBAL lock_wait_timeout = 30; (默認 31536000 秒 = 1 年！)
4. 監控告警：監控 metadata_locks 中 LOCK_STATUS='PENDING' 持續 > 5s 即告警
5. DDL 低峰執行：配合 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE (5.6+)

核心心法： **MDL 是保護元數據一致性的「大鎖」，DDL 拿 X 鎖是核武器，長事務是誘發核爆的導火索。生產環境零容忍長事務，DDL 必走在線工具。**