什么是MySQL元數據鎖

這篇文章主要介紹“什么是MySQL元數據鎖”，在日常操作中，相信很多人在什么是MySQL元數據鎖問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”什么是MySQL元數據鎖”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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 操作及備份操作放在業務低峰期執行。 

到此，關于“什么是MySQL元數據鎖”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！