# 一步步搞懂 MySQL 後設資料鎖（MDL）

某日，路上收到使用者諮詢，為了清除空間，想刪除某 200 多 G 大表資料，且已經確認此表不再有業務訪問，於是執行了一條命令‘delete from bigtable’，但好長時間也沒刪完，經過諮詢後，獲知 drop table 刪除錶速度快，而且能徹底釋放空間，於是又在另外一個 session 中執行了‘drop table bigtable’命令，但是這個命令並沒有快速返回結果，游標一直 hang 在原地不動。最後找我們協助，在登入資料庫執行‘show processlist’後發現 drop 語句的狀態是‘waiting for table metadata lock’，而之前執行的另外一個 delete 語句依舊能看到，狀態為‘updating’.

到底什麼是 metadata lock？這個鎖等待是如何產生的？會帶來什麼影響？最後又如何來解決？今天我們挑 6 **個常見問題**給大家解答一下。

**一、什麼是 metadata lock？**

在 MySQL5.5.3 之前，有一個著名的 **bug#989**，大致如下:

```
 session1:  
 BEGIN;
 INSERT INTO t ... ;
 COMMIT;

session2:  
 DROP TABLE t;
```

然而上面的操作流程在 binlog 記錄的順序是

```
 DROP TABLE t; 
 BEGIN;  
 INSERT INTO t ... ; 
 COMMIT;
```

很顯然備庫執行 binlog 時會先刪除表 t，然後執行 insert 會報 1032 error，導致複製中斷。為了解決該 bug,MySQL 在 5.5.3 引入了 MDL 鎖（metadata lock），來保護表的後設資料資訊，用於解決或者保證 DDL 操作與 DML 操作之間的一致性。

再舉一個簡單的例子，如果你在查詢一個表的過程中，另外一個 session 對該表刪除了一個列，那前面的查詢到底該顯示什麼呢？如果在 RR 隔離級別下，事物中再次執行相同的語句還會和之前結果一致嗎？為了防止這種情況，表查詢開始 MySQL 會在表上加一個鎖，來防止被別的 session 修改了表定義，這個鎖就叫‘metadata lock’，簡稱 MDL，翻譯成中文也叫‘後設資料鎖’。

**二、MDL 和行鎖有什麼區別？**

**metadata lock 是表級鎖，是在 server 層加的，適用於所有儲存引擎。**所有的 dml 操作都會在表上加一個 metadata 讀鎖；所有的 ddl 操作都會在表上加一個 metadata 寫鎖。讀鎖和寫鎖的阻塞關係如下：

- 讀鎖和寫鎖之間相互阻塞，即同一個表上的 dml 和 ddl 之間互相阻塞。
- 寫鎖和寫鎖之間互相阻塞，即兩個 session 不能對錶同時做表定義變更，需要序列操作。
- 讀鎖和讀鎖之間不會產生阻塞。也就是增刪改查不會因為 metadata lock 產生阻塞，可以併發執行，日常工作中大家看到的 dml 之間的鎖等待是 innodb 行鎖引起的，和 metadata lock 無關。

熟悉 innodb 行鎖的同學這裡可能有點困惑，因為行鎖分類和 metadata lock 很類似，也主要分為讀鎖和寫鎖，或者叫共享鎖和排他鎖，讀寫鎖之間阻塞關係也一致。二者最重要的區別一個是表鎖，一個是行鎖，且行鎖中的讀寫操作對應在 metadata lock 中都屬於讀鎖。

大家也許會奇怪，以前聽說普通查詢不加鎖的，怎麼這裡又說要加表鎖，我們做一個簡單測試：

session1：查詢前，先看一下 metadata_locks 表，這個表位於 performance_schema 下，記錄了 metadata lock 的加鎖資訊。

session2：執行簡單查詢，為了讓表處於執行狀態，這裡使用了 sleep 函式。

```
mysql> select sleep(10) from t1;
+-----------+
| sleep(10) |
+-----------+
|         0 |
|         0 |
|         0 |
+-----------+
3 rows in set (30.00 sec)
```

session1：

此時再次檢視 metadata_lock 表，發現多了一條 t1 的加鎖記錄，加鎖型別為 SHARED_READ, 且狀態是已授予（GRANTED)。大家通常理解的查詢不加鎖，是指不在表上加 innodb 行鎖。

如果在執行 sleep 期間，另外一個 session 執行了一個加欄位操作，此時就會產生 metadata lock 鎖等待：

session2:

mysql> select sleep(10) from t1;

執行中......

session3：

mysql> alter table t1 add col1 int;

阻塞中......

session1:

顯然，id 為 20 的執行緒還未執行 alter 操作，狀態為‘Waiting for table metadata lock’，也就是在等待 session2 的 sleep 操作完成。

**三、MDL 為什麼會造成系統崩潰？**

舉一個簡單例子:

- session1 啟動一個事務，對錶 t1 執行一個簡單的查詢；
- session2 對 t1 加一個欄位；
- session3 來對 t1 做一個查詢；
- session4 來對 t1 做一個 update;

各個 session 序列操作。

session1：

```
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+------+------+-------+
| id | name | age  | birth |
+----+------+------+-------+
|  1 | aa   |   10 | NULL  |
+----+------+------+-------+
1 row in set (0.00 sec)
```

session2：

mysql> alter table t1 add col1 int;

阻塞中...

session3:

mysql> select sleep(10) from t1 ;

阻塞中...

session4:

mysql> update t1 set name='aaaa' where id=2;

阻塞中...

也就是由於 session1 的一個事務沒有提交，導致 session2 的 ddl 操作被阻塞，session3 和 session4 本身不會被 session1 阻塞，但由於在鎖佇列中，session2 排隊更早，它準備加的是 metadata lock 寫鎖，阻塞了 session3 和 session4 的讀鎖。如果 t1 是一個執行頻繁的表，show processlist 會發現大量‘waiting for table metadata lock’的執行緒，資料庫連線很快就會消耗完，導致業務系統無法正常響應。

此時如果 session1 提交，是 session2 的 alter 語句先執行還是 session3 和 session4 先執行呢？之前一直以為先到的先執行，當然是 session2 先執行，但經過測試，在 5.7 中，session3 和 session4 先執行，session2 最後執行，也就會出現 alter 長時間無法執行的情況；而在 8.0 中，session2 先執行，session3 和 session4 後執行，由於 5.6 以後 ddl 是 online 的，session2 並不會阻塞 session3 和 session4，感覺這樣才是合理的，alter 不會被‘餓死’。

**四、MDL 的生命週期有多長？**

**事務！事務！事務！** 重要的事情說三遍，表上的 metadata lock 的生命週期從事務中的第一條涉及自身的語句開始，到整個事務結束而結束。而 5.5 之前是基於語句的，事務中執行完語句就釋放，如果此時另外一個 session 對錶做了一個刪欄位操作，那麼就會造成兩個問題：

- ddl 操作如果先於事務完成，那麼 binlog 中 ddl 就會排在事務之前，明顯和邏輯不符，觸發了本文開始提到的 bug。
- 如果是 RR 隔離級別，那麼事務中此表第二次執行將無法返回同樣的結果，無法滿足可重複讀的要求。

所以，如果要降低 metadata lock 的鎖等待時間，最好要及時提交事務，同時儘量避免大事務。

那麼如果發生 metadata lock 鎖等待，等待鎖的 session 會等待多長時間呢？大家都知道 MySQL 裡面行鎖等待有個超時時間（引數 innodb_lock_wait_timeout），預設 50s。metadata lock 也有類似引數控制：

這麼長的數字，掰著指頭算了半天，居然真的是...... 一年，環遊世界一圈回來還得接著等！！！

當然，生產環境中，我們很少會等待 metadata lock 超時，更多的是要想辦法把產生 metadata lock 的源頭找到，快速提交或者回滾，或者想辦法 kill 掉。那麼如何找到阻塞的源頭呢？

**五、如何快速找到阻塞源頭？**

**快速解決問題永遠是第一位的**，一旦出現長時間的 metadata lock，尤其是在訪問頻繁的業務表上產生，通常會導致表無法訪問，讀寫全被阻塞，此時找到阻塞源頭是第一位的。這裡最重要的表就是前面提到過的
performance_schema.metadata_locks 表。

metadata_locks 是 5.7 中被引入，記錄了 metadata lock 的相關資訊，包括持有物件、型別、狀態等資訊。但 5.7 預設設定是關閉的（8.0 預設開啟），需要透過下面命令開啟設定：

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

如果要永久生效，需要在配置檔案中加入如下內容：

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

單純查詢這個表無法得出具體的阻塞關係，也無法得知什麼語句造成的阻塞，這裡要關聯另外兩個表 performance_schema.thread 和
performance_schema.events_statements_history,thread 表可以將執行緒 id 和 show processlist 中 id 關聯，events_statements_history 表可以得到事務的歷史 sql，關聯後的完整 sql 如下：

```
SELECT
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
    sql_kill_blocking_connection
FROM
    (
        SELECT
            b.OWNER_THREAD_ID AS granted_thread_id,
            a.OBJECT_SCHEMA AS locked_schema,
            a.OBJECT_NAME AS locked_table,
            "Metadata Lock" AS locked_type,
            c.PROCESSLIST_ID AS waiting_processlist_id,
            c.PROCESSLIST_TIME AS waiting_age,
            c.PROCESSLIST_INFO AS waiting_query,
            c.PROCESSLIST_STATE AS waiting_state,
            d.PROCESSLIST_ID AS blocking_processlist_id,
            d.PROCESSLIST_TIME AS blocking_age,
            d.PROCESSLIST_INFO AS blocking_query,
            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
        FROM
            performance_schema.metadata_locks a
        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        AND a.OBJECT_NAME = b.OBJECT_NAME
        AND a.lock_status = 'PENDING'
        AND b.lock_status = 'GRANTED'
        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        AND a.lock_type = 'EXCLUSIVE'
        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        SELECT
            thread_id,
            group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
        FROM
           performance_schema.events_statements_history
        GROUP BY thread_id
    ) t2
WHERE
    t1.granted_thread_id = t2.thread_id \G
   
```

對於前面的例子執行此 sql，得到一個清晰的阻塞關係：

```
               locked_schema: db1
                locked_table: t1
                 locked_type: Metadata Lock
      waiting_processlist_id: 28
                 waiting_age: 227
               waiting_query: alter table t1 add cl3 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 27
                blocking_age: 252
              blocking_query: select * from t1
sql_kill_blocking_connection: KILL 27
1 row in set, 1 warning (0.00 sec)
```

根據顯示結果，processlist_id 為 27 的執行緒阻塞了 28 的執行緒，我們需要 kill 27 即可解鎖。

實際上，MySQL 也提供了一個類似的檢視來解決 metadata lock 問題，檢視名稱為 sys.schema_table_lock_waits，但此檢視查詢結果有 bug，不是很準確，建議大家還是參考上面 sql。

**六、本文開始的案例最終如何解決？**

透過前面的介紹，本文開始的案例產生的過程就很簡單了：使用者執行了一個全表 delete，在目標表上加了 metadata 讀鎖，由於表很大，讀鎖長時間無法釋放，後來另外一個 session 執行了 drop table 操作，又需要在表上加 metadata 寫鎖，由於讀寫鎖互相阻塞，drop 操作只能等待 delete 操作完成才能獲得寫鎖，因此從表面來看，二個命令都長時間沒有響應，其實內部一個在執行，一個在等待。

那怎麼來解決呢？因為從 show processlist 以及客戶描述可以很清楚的知道故障機制，當時建議客戶將 delete 操作 kill 掉，等資料回滾完後再執行 drop 操作因為 delete 已經執行了一段時間，回滾過程可能會較長，客戶最終 kill delete 後順利 drop 成功。

**小結**

生產環境(博主都是部署在[cnaaa伺服器}(https://www.cnaaa.com)上的)大多是 dml 操作，metadata 讀鎖之間不會產生鎖等待，而目前 MySQL 的 ddl 操作大多可以 online 執行，因此即使有寫鎖，也會很快降級為讀鎖，所以 ddl 執行期間阻塞 dml 的機率也很小。最容易出現的情況是由於有未完成的事務，導致 ddl metadata 寫鎖無法加上，只能在鎖佇列等待，而一旦進入鎖佇列，寫鎖又會阻塞其他的讀鎖，導致資料庫連線快速增長，直至消耗殆盡，最終業務受到影響。

為了儘可能避免類似問題，下面是幾個小建議：

- 生產環境的任何大表或頻繁操作的小表，ddl 都要非常慎重，最好在業務低峰期執行。
- 設計上要儘可能避免大事務，大事務不僅僅會帶來各種鎖問題，還好引起復制延遲 / 回滾空間爆滿等各類問題。
- 要及時提交事務，經常發現客戶端設定了事務手工提交，但 sql 執行後忘記點選提交按鈕，導致事務長時間無法提交。建議監控例項中的長事務，避免由於各種原因導致事務沒有及時提交。

一步步搞懂 MySQL 後設資料鎖（MDL）

相關文章