Metadata Lock (MDL) 是 MySQL 5.5 引入的元數據鎖子系統，用於協調 DDL 與 DML、事務之間的元數據訪問衝突。本文基於官方文檔與實驗驗證，系統闡述 MDL 的獲取/釋放語義、鎖模式兼容性、DDL 執行期鎖行為、長事務阻塞傳導機制，並提供完整的實驗復現步驟與生產應對策略。
核心語義：運行中事務必須在首次訪問對象時獲取該對象 MDL，並在事務提交/回滾時統一釋放 (PREPARE 語句除外)。這與 5.5 前「語句級釋放」是根本區別。
| 鎖模式 | 代碼常量 | 典型獲取者 | 兼容性 (✅=兼容 ❌=阻塞) |
|---|---|---|---|
| 共享讀 (MDL_SHARED_READ) | SR | SELECT、INSERT、UPDATE、DELETE、LOCK TABLE READ | 與 SR、SW 兼容；與 SW、X 衝突 |
| 共享寫 (MDL_SHARED_WRITE) | SW | LOCK TABLE WRITE (隱式)、隱式排他需求 | 與 SR 兼容；與 SW、X 衝突 |
| 排他 (MDL_EXCLUSIVE) | X | DDL (ALTER/DROP/TRUNCATE)、LOCK TABLE WRITE (顯式) | 與所有模式衝突 |
關鍵結論：DML 操作獲取 **SR (共享讀)**，互不阻塞；DDL 獲取 **X (排他)**，阻塞所有其他；LOCK TABLE WRITE 獲取 **X**，同樣阻塞所有。
CREATE TABLE cats (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO cats VALUES (1,'Andy'),(2,''),(3,NULL);
CREATE TABLE uu_test (userId INT PRIMARY KEY, user_Sex CHAR(1), user_QQ VARCHAR(20), user_homeTel VARCHAR(20));
INSERT INTO uu_test SELECT seq, IF(seq%2,'M','F'), CONCAT('QQ',seq), CONCAT('138',LPAD(seq,8,'0')) FROM seq_1_to_256;-- Session A
LOCK TABLE cats WRITE; -- 獲取 cats X 鎖，持有直到 UNLOCK TABLES
-- Session B
SELECT * FROM cats; -- 阻塞：Waiting for table metadata lock
-- Session A 驗證
SHOW PROCESSLIST;
-- Id=3 Sleep (LOCK TABLE), Id=1 Waiting for table metadata lock (SELECT)
-- 嘗試 KILL Session A → 無效，必須 UNLOCK TABLES
KILL 3; -- 不釋放 MDL
UNLOCK TABLES; -- 釋放 MDL，Session B 即刻恢復洞察：LOCK TABLE WRITE 持有 X 鎖直到顯式解鎖，**KILL 無法釋放 MDL**，這與事務鎖行為不同。
-- Session A (autocommit=0)
SET autocommit=0;
SELECT * FROM cats; -- 隱式事務開始，獲取 cats SR 鎖
-- 不執行 COMMIT
-- Session B
ALTER TABLE cats DROP INDEX name; -- 阻塞：Waiting for table metadata lock
-- Session A 提交
COMMIT; -- 釋放 SR 鎖，Session B 即刻獲得 X 鎖執行完成 (耗時 4分30秒，含阻塞等待)-- Session A (autocommit=0)
UPDATE cats SET name='Linus' WHERE id=1; -- 持有 SR
-- Session B (autocommit=on)
UPDATE cats SET name='strup' WHERE id=3; -- 即刻執行完成，無阻塞
-- 雙方均持有 SR 鎖，兼容-- Session A (autocommit=0)
UPDATE uu_test SET sex='M' WHERE id=1; -- 持有 SR
-- Session B
ALTER TABLE uu_test ADD INDEX(userId); -- 阻塞於 Session A 的 SR
-- Session A COMMIT → Session B 即刻執行-- Session A
ALTER TABLE uu_test ADD INDEX(user_homeTel); -- 耗時 12.86 秒
-- Session B (DDL 執行期間發起)
UPDATE uu_test SET user_Sex='M' WHERE userId=1; -- **即刻執行完成，無阻塞！**
-- 結論：DDL 僅在**開始瞬間**獲取 X 鎖，隨後**立即釋放**，執行過程不持有 MDL
-- 這與事務/LOCK TABLE 持有鎖直到結束形成鮮明對比-- Session A (autocommit=0)
SELECT userId,user_Sex FROM uu_test LIMIT 2; -- 持有 SR，不提交
-- Session B
ALTER TABLE uu_test ADD INDEX(user_QQ); -- 阻塞於 A 的 SR，排隊等待 X
-- Session C
SELECT * FROM uu_test LIMIT 1; -- **被阻塞！** 等待 X 鎖 (Session B) 釋放
-- 狀態鏈：A(SR持有) → B(X等待) → C(SR等待)
-- 所有後續訪問 uu_test 的查詢全部堆積
-- 解除：Session A COMMIT → B 獲得 X → 執行 → 釋放 X → C 獲得 SR → 執行
-- 觀察到：C 先於 B 完成 (因 B 執行耗時長，C 僅需瞬間 SR)SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS 區塊關鍵輸出：
---TRANSACTION 22564, ACTIVE 103 sec -- Session A 長事務
MySQL thread id 1, query id 46 ... cleaning up
Trx read view will not see trx with id >= 22565, sees < 22565
-- INNODB_TRX 表
SELECT * FROM information_schema.INNODB_TRX\G
-- trx_id=22564, trx_state=RUNNING, trx_started=..., trx_mysql_thread_id=1
-- 精確定位未提交事務的線程 IDSHOW PROCESSLIST 找 Waiting for table metadata lockINNODB_TRX 找長事務 / SHOW PROCESSLIST 找 LOCK TABLE WRITEKILL ;
-- 後續查詢即刻恢復，DDL 線程報錯 "Lost connection" KILL ;
-- 事務回滾，MDL 釋放 SET SESSION lock_wait_timeout=30; (DDL 執行前)lock_wait_timeout (MDL) vs innodb_lock_wait_timeout (行鎖，默認 50s)| 層面 | 措施 | 工具/實現 |
|---|---|---|
| 應用開發 | 強制短事務、顯式邊界、禁用 autocommit=0 長連線 | 框架事務管理、代碼審查、靜態分析 |
| 監控告警 | 長事務 (>10s)、MDL 等待 (>1s)、DDL 執行 (>阈值) | Prometheus + Grafana + Alertmanager、Performance Schema metadata_locks |
| 變更管理 | DDL 僅低峰、預檢無長事務、強制線上工具 | pt-online-schema-change、gh-ost、變更工單系統 |
| 架構演進 | MySQL 8.0 INSTANT DDL、分庫分表降低單表體積 | 版本升級規劃、分片中間件 |
lock_wait_timeout 會話級防護；Performance Schema.metadata_locks 可觀測 (5.7+)Metadata Lock 是 MySQL 高併發下元數據一致性的守門員，其「事務級持有、DDL 互斥」特性在未加治理的系統中極易成為穩定性殺手。將 MDL 可觀測性納入監控體系、DDL 流程工具化、事務邊界框架化，是從「被動救火」轉向「主動免疫」的關鍵路徑。
