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

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

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

2、加鎖規(guī)則

獲取規(guī)則：

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

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

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

場(chǎng)景一

線程 1:

LOCK TABLE t WRITE, t_new WRITE; 

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

線程 2:

INSERT INTO t VALUES(1); 

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

線程 3:

RENAME TABLE t TO t_old, t_new TO t; 

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

線程 1:

UNLOCK TABLES; 

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

場(chǎng)景二

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

線程 1:

LOCK TABLE t WRITE, new_t WRITE; 

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

線程 2:

INSERT INTO t VALUES(1); 

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

線程 3:

RENAME TABLE t TO old_t, new_t TO t; 

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

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

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

performance_schema.metadata_locks 表中記錄了元數(shù)據(jù)鎖相關(guān)的信息，開(kāi)啟方式如下：在線開(kāi)啟 metadata_locks，操作如下：

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

若可停庫(kù)維護(hù)，則在 my.cnf 中添加如下：

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

5、如何優(yōu)化元數(shù)據(jù)鎖

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

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