MySQL Metadata Lock (MDL) 深度解析（三）：鎖類型、對象維度與查詢阻塞原理

前言：MDL 核心使命

Metadata Lock (MDL) 是 MySQL 5.5+ 引入的**元數據鎖機制**，核心目的是**保護資料庫對象定義（表結構、視圖、函數等）在 DDL 與 DML/查詢並發時的一致性**。本文補充前兩篇未覆蓋的鎖類型細分、對象維度劃分，並解釋查詢阻塞成因與 mysqldump 一致性備份原理。

一、MDL 鎖類型完整體系（按鎖模式劃分）

二、MDL 鎖對象維度劃分（按保護範圍）

三、查詢阻塞成因深度解析

經典阻塞鏈：長事務 → DDL → 後續查詢全阻塞

-- 會話 A：長時間運行的事務 (未提交)
BEGIN;
SELECT * FROM t WHERE id = 1 FOR UPDATE;  -- 持有表 MDL_SHARED (隱式)
-- 停留 10 分鐘...

-- 會話 B：DDL 操作
ALTER TABLE t ADD COLUMN c INT;  -- 申請 MDL_EXCLUSIVE，被 A 阻塞，進入 Waiting for table metadata lock

-- 會話 C：普通查詢 (自動提交)
SELECT * FROM t;  -- 申請 MDL_SHARED，被 B 的 EXCLUSIVE 等待阻塞！

-- 會話 D...N：所有後續訪問 t 的查詢全部堆積

根因： MDL 兼容性矩陣中，**EXCLUSIVE 與所有鎖互斥**。DDL 等待 MDL_EXCLUSIVE 時，已在隊列中佔位，**後續所有申請 SHARED/IS 的查詢均在 DDL 之後排隊**，形成「DDL 阻塞查詢」的連鎖效應。

排查與緩解

1. SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME='t'\G 定位持有者
2. KILL 長事務會話 (慎用，業務影響)
3. DDL 使用 pt-online-schema-change / gh-ost 繞過 MDL_EXCLUSIVE
4. 設置 lock_wait_timeout 避免無限等待
5. 監控 metadata_locks 表，告警長時間等待

四、mysqldump 一致性備份原理與 MDL 關係

單庫/單表備份 (--single-transaction)

mysqldump --single-transaction --master-data=2 db_name > dump.sql

1. 啟動事務：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2. 獲取全局讀鎖 (FTWRL) 極短時間：FLUSH TABLES WITH READ LOCK → 獲取 GLOBAL MDL_SHARED → 記錄 Binlog 位點 → **立即釋放**
3. 事務內 SELECT 數據：MVCC 保證讀到快照視圖，**不持有 MDL_SHARED** (InnoDB 表)
4. 非事務表 (MyISAM) 仍需持有表級 MDL_SHARED 直到備份結束

全實例備份 (--master-data=1/2, 無 --single-transaction)

• FLUSH TABLES WITH READ LOCK 持有 **GLOBAL MDL_SHARED** 整個備份過程
• **所有寫入阻塞**，唯讀模式，業務影響極大，**生產嚴禁**

關鍵差異總結

最佳實踐： 生產備份**強制使用 --single-transaction --master-data=2**，確保 InnoDB 表零阻塞、基於位點增量恢復。MyISAM 表建議遷移至 InnoDB 或接受短暫鎖表。