某日，路上收到用戶咨詢，為了清除空間，想刪除某200多G大表數(shù)據(jù)，且已經(jīng)確認(rèn)此表不再有業(yè)務(wù)訪問，于是執(zhí)行了一條命令‘delete from bigtable’，但好長(zhǎng)時(shí)間也沒刪完，經(jīng)過咨詢后，獲知drop table刪除表速度快，而且能徹底釋放空間，于是又在另外一個(gè)session中執(zhí)行了‘drop table bigtable’命令，但是這個(gè)命令并沒有快速返回結(jié)果，光標(biāo)一直hang在原地不動(dòng)。最后找我們協(xié)助，在登錄數(shù)據(jù)庫(kù)執(zhí)行‘show processlist’后發(fā)現(xiàn)drop語(yǔ)句的狀態(tài)是‘waiting for table metadata lock’，而之前執(zhí)行的另外一個(gè)delete語(yǔ)句依舊能看到，狀態(tài)為‘updating’，截圖如下：

到底什么是metadata lock？這個(gè)鎖等待是如何產(chǎn)生的？會(huì)帶來什么影響？最后又如何來解決？今天我們挑6個(gè)常見問題給大家解答一下。

一、什么是metadata lock

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

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

 session2:  
 DROP TABLE t;

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

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

很顯然備庫(kù)執(zhí)行binlog時(shí)會(huì)先刪除表t，然后執(zhí)行insert 會(huì)報(bào)1032 error，導(dǎo)致復(fù)制中斷。為了解決該bug,MySQL 在5.5.3引入了MDL鎖（metadata lock），來保護(hù)表的元數(shù)據(jù)信息，用于解決或者保證DDL操作與DML操作之間的一致性。

再舉一個(gè)簡(jiǎn)單的例子，如果你在查詢一個(gè)表的過程中，另外一個(gè)session對(duì)該表刪除了一個(gè)列，那前面的查詢到底該顯示什么呢？如果在RR隔離級(jí)別下，事物中再次執(zhí)行相同的語(yǔ)句還會(huì)和之前結(jié)果一致嗎？為了防止這種情況，表查詢開始MySQL會(huì)在表上加一個(gè)鎖，來防止被別的session修改了表定義，這個(gè)鎖就叫‘metadata lock’，簡(jiǎn)稱MDL，翻譯成中文也叫‘元數(shù)據(jù)鎖’。

二、MDL和行鎖有什么區(qū)別

metadata lock是表級(jí)鎖，是在server層加的，適用于所有存儲(chǔ)引擎。所有的dml操作都會(huì)在表上加一個(gè)metadata讀鎖；所有的ddl操作都會(huì)在表上加一個(gè)metadata寫鎖。讀鎖和寫鎖的阻塞關(guān)系如下：

• 讀鎖和寫鎖之間相互阻塞，即同一個(gè)表上的dml和ddl之間互相阻塞。
• 寫鎖和寫鎖之間互相阻塞，即兩個(gè)session不能對(duì)表同時(shí)做表定義變更，需要串行操作。
• 讀鎖和讀鎖之間不會(huì)產(chǎn)生阻塞。也就是增刪改查不會(huì)因?yàn)閙etadata lock產(chǎn)生阻塞，可以并發(fā)執(zhí)行，日常工作中大家看到的dml之間的鎖等待是innodb行鎖引起的，和metadata lock無(wú)關(guān)。

熟悉innodb行鎖的同學(xué)這里可能有點(diǎn)困惑，因?yàn)樾墟i分類和metadata lock很類似，也主要分為讀鎖和寫鎖，或者叫共享鎖和排他鎖，讀寫鎖之間阻塞關(guān)系也一致。二者最重要的區(qū)別一個(gè)是表鎖，一個(gè)是行鎖，且行鎖中的讀寫操作對(duì)應(yīng)在metadata lock中都屬于讀鎖。

大家也許會(huì)奇怪，以前聽說普通查詢不加鎖的，怎么這里又說要加表鎖，我們做一個(gè)簡(jiǎn)單測(cè)試：

session1：查詢前，先看一下metadata_locks表，這個(gè)表位于performance_schema下，記錄了metadata lock的加鎖信息。

mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             12 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)

session2：執(zhí)行簡(jiǎn)單查詢，為了讓表處于執(zhí)行狀態(tài)，這里使用了sleep函數(shù)。

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

session1：

mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | db1                | t1             | NULL        |       139776154308336 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              53 |             22 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             13 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

此時(shí)再次查看metadata_lock表，發(fā)現(xiàn)多了一條t1的加鎖記錄，加鎖類型為SHARED_READ,且狀態(tài)是已授予（GRANTED)。大家通常理解的查詢不加鎖，是指不在表上加innodb行鎖。

如果在執(zhí)行sleep期間，另外一個(gè)session執(zhí)行了一個(gè)加字段操作，此時(shí)就會(huì)產(chǎn)生metadata lock鎖等待：

session2:

mysql> select sleep(10) from t1;

執(zhí)行中......

session3：

mysql> alter table t1 add col1 int;

阻塞中......

session1:

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
| Id | User            | Host      | db   | Command | Time   | State                           | Info                        |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 861577 | Waiting on empty queue          | NULL                        |
| 18 | root            | localhost | db1  | Sleep   |     50 |                                 | NULL                        |
| 19 | root            | localhost | NULL | Query   |      0 | starting                        | show processlist            |
| 20 | root            | localhost | db1  | Query   |     11 | Waiting for table metadata lock | alter table t1 add col1 int |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
4 rows in set (0.00 sec)

顯然，id為20的線程還未執(zhí)行alter操作，狀態(tài)為‘Waiting for table metadata lock’，也就是在等待session2的sleep操作完成。

三、MDL為什么會(huì)造成系統(tǒng)崩潰

舉一個(gè)簡(jiǎn)單例子:

• session1啟動(dòng)一個(gè)事務(wù)，對(duì)表t1執(zhí)行一個(gè)簡(jiǎn)單的查詢；
• session2對(duì)t1加一個(gè)字段；
• session3來對(duì)t1做一個(gè)查詢；
• session4來對(duì)t1做一個(gè)update;

各個(gè)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的一個(gè)事務(wù)沒有提交，導(dǎo)致session2的ddl操作被阻塞，session3和session4本身不會(huì)被session1阻塞，但由于在鎖隊(duì)列中，session2排隊(duì)更早，它準(zhǔn)備加的是metadata lock寫鎖，阻塞了session3和session4的讀鎖。如果t1是一個(gè)執(zhí)行頻繁的表，show processlist會(huì)發(fā)現(xiàn)大量‘waiting for table metadata lock’的線程，數(shù)據(jù)庫(kù)連接很快就會(huì)消耗完，導(dǎo)致業(yè)務(wù)系統(tǒng)無(wú)法正常響應(yīng)。

此時(shí)如果session1提交，是session2的alter語(yǔ)句先執(zhí)行還是session3和session4先執(zhí)行呢？之前一直以為先到的先執(zhí)行，當(dāng)然是session2先執(zhí)行，但經(jīng)過測(cè)試，在5.7中，session3和session4先執(zhí)行，session2最后執(zhí)行，也就會(huì)出現(xiàn)alter長(zhǎng)時(shí)間無(wú)法執(zhí)行的情況；而在8.0中，session2先執(zhí)行，session3和session4后執(zhí)行，由于5.6以后ddl是online的，session2并不會(huì)阻塞session3和session4，感覺這樣才是合理的，alter不會(huì)被‘餓死’。

四、MDL的生命周期有多長(zhǎng)

事務(wù)！事務(wù)！事務(wù)！ 重要的事情說三遍，表上的metadata lock的生命周期從事務(wù)中的第一條涉及自身的語(yǔ)句開始，到整個(gè)事務(wù)結(jié)束而結(jié)束。而5.5之前是基于語(yǔ)句的，事務(wù)中執(zhí)行完語(yǔ)句就釋放，如果此時(shí)另外一個(gè)session對(duì)表做了一個(gè)刪字段操作，那么就會(huì)造成兩個(gè)問題：

• ddl操作如果先于事務(wù)完成，那么binlog中ddl就會(huì)排在事務(wù)之前，明顯和邏輯不符，觸發(fā)了本文開始提到的bug。
• 如果是RR隔離級(jí)別，那么事務(wù)中此表第二次執(zhí)行將無(wú)法返回同樣的結(jié)果，無(wú)法滿足可重復(fù)讀的要求。

所以，如果要降低metadata lock的鎖等待時(shí)間，最好要及時(shí)提交事務(wù)，同時(shí)盡量避免大事務(wù)。

那么如果發(fā)生metadata lock鎖等待，等待鎖的session會(huì)等待多長(zhǎng)時(shí)間呢？大家都知道MySQL里面行鎖等待有個(gè)超時(shí)時(shí)間（參數(shù)innodb_lock_wait_timeout），默認(rèn)50s。metadata lock也有類似參數(shù)控制：

mysql> show variables like 'lock_wait_timeout'      ;
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)

這么長(zhǎng)的數(shù)字，掰著指頭算了半天，居然真的是......一年，環(huán)游世界一圈回來還得接著等!!!

當(dāng)然，生產(chǎn)環(huán)境中，我們很少會(huì)等待metadata lock超時(shí)，更多的是要想辦法把產(chǎn)生metadata lock的源頭找到，快速提交或者回滾，或者想辦法kill掉。那么如何找到阻塞的源頭呢？

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

快速解決問題永遠(yuǎn)是第一位的，一旦出現(xiàn)長(zhǎng)時(shí)間的metadata lock，尤其是在訪問頻繁的業(yè)務(wù)表上產(chǎn)生，通常會(huì)導(dǎo)致表無(wú)法訪問，讀寫全被阻塞，此時(shí)找到阻塞源頭是第一位的。這里最重要的表就是前面提到過的
performance_schema.metadata_locks表。

metadata_locks是5.7中被引入，記錄了metadata lock的相關(guān)信息，包括持有對(duì)象、類型、狀態(tài)等信息。但5.7默認(rèn)設(shè)置是關(guān)閉的（8.0默認(rèn)打開），需要通過下面命令打開設(shè)置：

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

如果要永久生效，需要在配置文件中加入如下內(nèi)容：

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

單純查詢這個(gè)表無(wú)法得出具體的阻塞關(guān)系，也無(wú)法得知什么語(yǔ)句造成的阻塞，這里要關(guān)聯(lián)另外兩個(gè)表performance_schema.thread和
performance_schema.events_statements_history,thread表可以將線程id和show processlist中id關(guān)聯(lián)，events_statements_history表可以得到事務(wù)的歷史sql，關(guān)聯(lián)后的完整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

對(duì)于前面的例子執(zhí)行此sql，得到一個(gè)清晰的阻塞關(guān)系：

               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)

根據(jù)顯示結(jié)果，processlist_id為27的線程阻塞了28的線程，我們需要kill 27即可解鎖。

實(shí)際上，MySQL也提供了一個(gè)類似的視圖來解決metadata lock問題，視圖名稱為sys.schema_table_lock_waits，但此視圖查詢結(jié)果有bug，不是很準(zhǔn)確，建議大家還是參考上面sql。

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

通過前面的介紹，本文開始的案例產(chǎn)生的過程就很簡(jiǎn)單了：用戶執(zhí)行了一個(gè)全表delete，在目標(biāo)表上加了metadata讀鎖，由于表很大，讀鎖長(zhǎng)時(shí)間無(wú)法釋放，后來另外一個(gè)session執(zhí)行了drop table操作，又需要在表上加metadata寫鎖，由于讀寫鎖互相阻塞，drop操作只能等待delete操作完成才能獲得寫鎖，因此從表面來看，二個(gè)命令都長(zhǎng)時(shí)間沒有響應(yīng)，其實(shí)內(nèi)部一個(gè)在執(zhí)行，一個(gè)在等待。

那怎么來解決呢？因?yàn)閺膕how processlist以及客戶描述可以很清楚的知道故障機(jī)制，當(dāng)時(shí)建議客戶將delete操作kill掉，等數(shù)據(jù)回滾完后再執(zhí)行drop操作因?yàn)閐elete已經(jīng)執(zhí)行了一段時(shí)間，回滾過程可能會(huì)較長(zhǎng)，客戶最終kill delete后順利drop成功。

小結(jié)

生產(chǎn)環(huán)境大多是dml操作，metadata讀鎖之間不會(huì)產(chǎn)生鎖等待，而目前MySQL的ddl操作大多可以online執(zhí)行，因此即使有寫鎖，也會(huì)很快降級(jí)為讀鎖，所以ddl執(zhí)行期間阻塞dml的幾率也很小。最容易出現(xiàn)的情況是由于有未完成的事務(wù)，導(dǎo)致ddl metadata 寫鎖無(wú)法加上，只能在鎖隊(duì)列等待，而一旦進(jìn)入鎖隊(duì)列，寫鎖又會(huì)阻塞其他的讀鎖，導(dǎo)致數(shù)據(jù)庫(kù)連接快速增長(zhǎng)，直至消耗殆盡，最終業(yè)務(wù)受到影響。

為了盡可能避免類似問題，下面是幾個(gè)小建議：

• 生產(chǎn)環(huán)境的任何大表或頻繁操作的小表，ddl都要非常慎重，最好在業(yè)務(wù)低峰期執(zhí)行。
• 設(shè)計(jì)上要盡可能避免大事務(wù)，大事務(wù)不僅僅會(huì)帶來各種鎖問題，還好引起復(fù)制延遲/回滾空間爆滿等各類問題。
• 要及時(shí)提交事務(wù)，經(jīng)常發(fā)現(xiàn)客戶端設(shè)置了事務(wù)手工提交，但sql執(zhí)行后忘記點(diǎn)擊提交按鈕，導(dǎo)致事務(wù)長(zhǎng)時(shí)間無(wú)法提交。建議監(jiān)控實(shí)例中的長(zhǎng)事務(wù)，避免由于各種原因?qū)е率聞?wù)沒有及時(shí)提交。

到此這篇關(guān)于一文搞懂MySQL元數(shù)據(jù)鎖(MDL)的文章就介紹到這了,更多相關(guān)MySQL元數(shù)據(jù)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！