**前言：**

當你在MySQL中執行一條SQL時，語句並沒有在你預期的時間內執行完成，這時候我們通常會登陸到MySQL資料庫上檢視是不是出了什麼問題，通常會使用的一個命令就是 show processlist，看看有哪些session，這些session在做什麼事情。當你看到 `waiting for table metadata lock` 時，那就是遇到MDL後設資料鎖了。本篇文章將會介紹MDL鎖的產生與排查過程。

#### 1.什麼是MDL鎖

MDL全稱為metadata lock，即後設資料鎖。MDL鎖主要作用是維護表後設資料的資料一致性，在表上有活動事務（顯式或隱式）的時候，不可以對後設資料進行寫入操作。因此從MySQL5.5版本開始引入了MDL鎖，來保護表的後設資料資訊，用於解決或者保證DDL操作與DML操作之間的一致性。

對於引入MDL，其主要解決了2個問題，一個是事務隔離問題，比如在可重複隔離級別下，會話A在2次查詢期間，會話B對錶結構做了修改，兩次查詢結果就會不一致，無法滿足可重複讀的要求；另外一個是資料複製的問題，比如會話A執行了多條更新語句期間，另外一個會話B做了表結構變更並且先提交，就會導致slave在重做時，先重做alter，再重做update時就會出現複製錯誤的現象。

後設資料鎖是server層的鎖，表級鎖，每執行一條DML、DDL語句時都會申請MDL鎖，DML操作需要MDL讀鎖，DDL操作需要MDL寫鎖（MDL加鎖過程是系統自動控制，無法直接干預，讀讀共享，讀寫互斥，寫寫互斥），申請MDL鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖。一旦出現寫鎖等待，不但當前操作會被阻塞，同時還會阻塞後續該表的所有操作。事務一旦申請到MDL鎖後，直到事務執行完才會將鎖釋放。（這裡有種特殊情況如果事務中包含DDL操作，mysql會在DDL操作語句執行前，隱式提交commit，以保證該DDL語句操作作為一個單獨的事務存在，同時也保證後設資料排他鎖的釋放）。

> _注：_ 支援事務的InnoDB引擎表和不支援事務的MyISAM引擎表，都會出現Metadata Lock Wait等待現象。一旦出現Metadata Lock Wait等待現象，後續所有對該表的訪問都會阻塞在該等待上，導致連線堆積，業務受影響。

#### 2.模擬與查詢MDL鎖

MDL鎖通常發生在DDL操作掛起的時候，原因是有未提交的事務對該表進行DML操作。而MySQL的會話那麼多，不知道哪個會話的操作沒有及時提交影響了DDL。通常我們排查這類問題，往往需要從information_schema.innodb_trx表中查詢當前在執行的事務，但當SQL已經執行過了，沒有commit，這個時候這個表中是看不到SQL的。

在MySQL5.7中，performance_schema庫中新增了metadata_locks表，專門記錄MDL的相關資訊。首先要開啟MDL鎖記錄，執行如下SQL開啟：

```sql
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
```

下面展示下模擬及查詢MDL鎖的過程：

```sql
# 會話1 事務中執行DML操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | from1    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            2 |   1002 | dfsfd    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            3 |   1003 | fdgfg    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            4 |   1004 | sdfsdf   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            5 |   1005 | dsfsdg   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            6 |   1006 | fgd      | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            7 |   1007 | fgds     | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            8 |   1008 | dgfsa    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            9 |   1009 | xin      | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+

# 會話2 對該表加欄位 執行DDL操作 發現DDL掛起
mysql> alter table student_tb add stu_age int after stu_name;

# 結果解讀：從上面結果明顯可以看出會話31持有student_tb表的SHARED_WRITE鎖，
# 需要等待其提交後或手動殺掉該會話方可解除MDL鎖。
```

#### 3.如何最佳化與避免MDL鎖

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

- 開啟metadata_locks表記錄MDL鎖。
- 設定引數lock_wait_timeout為較小值，使被阻塞端主動停止。
- 規範使用事務，及時提交事務，避免使用大事務。
- 增強監控告警，及時發現MDL鎖。
- DDL操作及備份操作放在業務低峰期執行。
- 少用工具開啟事務進行查詢，圖形化工具要及時關閉。

**總結：**

本篇文章主要分三方面來詳解MDL鎖，首先介紹了MDL鎖產生的原因及作用，然後我們模擬出MDL鎖，並給出查詢及解決方法，最後給出幾點避免MDL鎖的建議。其實，MDL鎖在DB運維過程中經常遇到，它不是洪水猛獸，只是為了保護資料庫物件，保證資料一致性。希望大家看完這篇文章後能對MDL鎖有更清晰的認識。

**參考：**

- [https://blog.csdn.net/wanbf123/article/details/80697787](https://blog.csdn.net/wanbf123/article/details/80697787)
- [https://blog.csdn.net/finalkof1983/article/details/88063328](https://blog.csdn.net/finalkof1983/article/details/88063328)
- [https://www.jianshu.com/p/c2efdde99e2f](https://www.jianshu.com/p/c2efdde99e2f)

深入理解MDL後設資料鎖

相關文章