當我們在 MySQL 中執行 DDL 語句時，經常會發現語句沒有在你預期的時間完成，這時候我們通常會使用 show full processlist ，來看看發生了什么狀況。當你看到 waiting for table metadata lock 時，那就碰到元數據鎖了。那元數據鎖是怎樣產生的又應該怎樣避免呢?讓我們從這篇文章開始了解它。

1、什么是元數據鎖

MDL 全稱為 metadata lock，即元數據鎖，一般也可稱為字典鎖。MDL 的主要作用是為了管理數據庫對象的并發訪問和確保元數據一致性。元數據鎖適用對象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。

2、加鎖規則

獲取規則：

• 語句逐個( one by one )獲取元數據鎖，不是同時獲取，并在獲取過程中執行死鎖檢測。
• DML 語句獲取鎖按照語句中 table 出現的順序來獲取鎖。
• DDL 語句、LOCK TABLES 和其他類似語句按名稱順序獲取鎖，對于隱式使用的表(例如外鍵關系中也必須鎖定的表)可能會以不同的順序獲取鎖。
• DDL 的寫鎖請求優先級高于 DML

3、模擬加鎖規則

兩個相同表結構的表 t 和 t_new 開始。三個線程來操作這些表：

場景一

線程 1:

LOCK TABLE t WRITE, t_new WRITE; 

該語句按表名順序在 t 和 t_new 上獲取寫鎖

線程 2:

INSERT INTO t VALUES(1); 

該語句處于也需要獲取表 t 上的 MDL 所以處于等待狀態

線程 3:

RENAME TABLE t TO t_old, t_new TO t; 

該語句需要按表名順序在 t 、t_new、t_old 上獲取互斥鎖，所以也處于等待狀態

線程 1:

UNLOCK TABLES; 

該語句釋放對 t 和 t_new 的寫鎖定。線程 3 對 t 加寫鎖的優先級高于 線程 2 ，因此線程 3 在 t 上優先獲得互斥鎖，然后依次在 t_new、t_old 上獲取互斥鎖，執行重命名后釋放其鎖定。線程 2 獲得 t 上的寫鎖，執行插入操作，然后釋放其鎖定。rename 操作在 insert 之前執行。

場景二

兩個具有相同表結構的表 t 和 new_t ，同樣是三個線程來操作這些表

線程 1:

LOCK TABLE t WRITE, new_t WRITE; 

該語句按表名順序在 new_t 和 t 上獲取寫鎖

線程 2:

INSERT INTO t VALUES(1); 

該語句處于也需要獲取表 t 上的 MDL 所以處于等待狀態

線程 3:

RENAME TABLE t TO old_t, new_t TO t; 

該語句需要按表名順序在 new_t 、old_t、t 上獲取互斥鎖，所以也處于等待狀態

該語句釋放對 t 和 new_t 的寫鎖定。對于 t 首先發起鎖請求的是線程 2 ，因此線程 2 優先獲得了 t 上的元數據寫鎖，執行完插入操作，然后釋放該鎖。線程 3 首先獲取的是 new_t 、old_t 的互斥鎖，最后才會請求 t 上的互斥鎖，所以線程 3 在線程 2 執行完畢之前都是處于等待狀態的。rename 操作在 insert 操作之后。

4、 如何監控元數據鎖

performance_schema.metadata_locks 表中記錄了元數據鎖相關的信息，開啟方式如下：在線開啟 metadata_locks，操作如下：

--UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation'; 
--此值默認已開啟了，可檢查確認。 
 
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl'; 

若可停庫維護，則在 my.cnf 中添加如下：

[mysqld] 
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON' 

5、如何優化元數據鎖

MDL 鎖一旦發生會對業務造成極大影響，因為后續所有對該表的訪問都會被阻塞，造成連接積壓。我們日常要盡量避免 MDL 鎖的發生，下面給出幾點優化建議可供參考：

• 開啟 metadata_locks 表記錄 MDL 鎖。
• 設置參數 lock_wait_timeout 為較小值，使被阻塞端主動停止。
• 規范使用事務，及時提交事務，避免使用大事務。
• 增強監控告警，及時發現 MDL 鎖。
• DDL 操作及備份操作放在業務低峰期執行。