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

1、什么是元數(shù)據(jù)鎖

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

2、加鎖規(guī)則

獲取規(guī)則：

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

3、模擬加鎖規(guī)則

兩個相同表結(jié)構(gòu)的表 t 和 t_new 開始。三個線程來操作這些表：

場景一

線程 1:

LOCK TABLE t WRITE, t_new WRITE; 

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

線程 2:

INSERT INTO t VALUES(1); 

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

線程 3:

RENAME TABLE t TO t_old, t_new TO t; 

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

線程 1:

UNLOCK TABLES; 

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

場景二

兩個具有相同表結(jié)構(gòu)的表 t 和 new_t ，同樣是三個線程來操作這些表

線程 1:

LOCK TABLE t WRITE, new_t WRITE; 

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

線程 2:

INSERT INTO t VALUES(1); 

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

線程 3:

RENAME TABLE t TO old_t, new_t TO t; 

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

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

4、 如何監(jiān)控元數(shù)據(jù)鎖

performance_schema.metadata_locks 表中記錄了元數(shù)據(jù)鎖相關(guān)的信息，開啟方式如下：在線開啟 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、如何優(yōu)化元數(shù)據(jù)鎖

MDL 鎖一旦發(fā)生會對業(yè)務造成極大影響，因為后續(xù)所有對該表的訪問都會被阻塞，造成連接積壓。我們?nèi)粘ＲM量避免 MDL 鎖的發(fā)生，下面給出幾點優(yōu)化建議可供參考：

• 開啟 metadata_locks 表記錄 MDL 鎖。
• 設置參數(shù) lock_wait_timeout 為較小值，使被阻塞端主動停止。
• 規(guī)范使用事務，及時提交事務，避免使用大事務。
• 增強監(jiān)控告警，及時發(fā)現(xiàn) MDL 鎖。
• DDL 操作及備份操作放在業(yè)務低峰期執(zhí)行。