一文搞懂MySQL元數(shù)據(jù)鎖(MDL)
某日，路上收到用戶咨詢，為了清除空間，想刪除某200多G大表數(shù)據(jù)，且已經確認此表不再有業(yè)務訪問，于是執(zhí)行了一條命令‘delete from bigtable’，但好長時間也沒刪完，經過咨詢后，獲知drop table刪除表速度快，而且能徹底釋放空間，于是又在另外一個session中執(zhí)行了‘drop table bigtable’命令，但是這個命令并沒有快速返回結果，光標一直hang在原地不動。最后找我們協(xié)助，在登錄數(shù)據(jù)庫執(zhí)行‘show processlist’后發(fā)現(xiàn)drop語句的狀態(tài)是‘waiting for table metadata lock’，而之前執(zhí)行的另外一個delete語句依舊能看到，狀態(tài)為‘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;
很顯然備庫執(zhí)行binlog時會先刪除表t，然后執(zhí)行insert 會報1032 error，導致復制中斷。為了解決該bug,MySQL 在5.5.3引入了MDL鎖（metadata lock），來保護表的元數(shù)據(jù)信息，用于解決或者保證DDL操作與DML操作之間的一致性。
再舉一個簡單的例子，如果你在查詢一個表的過程中，另外一個session對該表刪除了一個列，那前面的查詢到底該顯示什么呢？如果在RR隔離級別下，事物中再次執(zhí)行相同的語句還會和之前結果一致嗎？為了防止這種情況，表查詢開始MySQL會在表上加一個鎖，來防止被別的session修改了表定義，這個鎖就叫‘metadata lock’，簡稱MDL，翻譯成中文也叫‘元數(shù)據(jù)鎖’。
二、MDL和行鎖有什么區(qū)別
metadata lock是表級鎖，是在server層加的，適用于所有存儲引擎。所有的dml操作都會在表上加一個metadata讀鎖；所有的ddl操作都會在表上加一個metadata寫鎖。讀鎖和寫鎖的阻塞關系如下：
- 讀鎖和寫鎖之間相互阻塞，即同一個表上的dml和ddl之間互相阻塞。
- 寫鎖和寫鎖之間互相阻塞，即兩個session不能對表同時做表定義變更，需要串行操作。
- 讀鎖和讀鎖之間不會產生阻塞。也就是增刪改查不會因為metadata lock產生阻塞，可以并發(fā)執(zhí)行，日常工作中大家看到的dml之間的鎖等待是innodb行鎖引起的，和metadata lock無關。
熟悉innodb行鎖的同學這里可能有點困惑，因為行鎖分類和metadata lock很類似，也主要分為讀鎖和寫鎖，或者叫共享鎖和排他鎖，讀寫鎖之間阻塞關系也一致。二者最重要的區(qū)別一個是表鎖，一個是行鎖，且行鎖中的讀寫操作對應在metadata lock中都屬于讀鎖。
大家也許會奇怪，以前聽說普通查詢不加鎖的，怎么這里又說要加表鎖，我們做一個簡單測試：
session1：查詢前，先看一下metadata_locks表，這個表位于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í)行簡單查詢，為了讓表處于執(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)
此時再次查看metadata_lock表，發(fā)現(xiàn)多了一條t1的加鎖記錄，加鎖類型為SHARED_READ,且狀態(tài)是已授予（GRANTED)。大家通常理解的查詢不加鎖，是指不在表上加innodb行鎖。
如果在執(zhí)行sleep期間，另外一個session執(zhí)行了一個加字段操作，此時就會產生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為什么會造成系統(tǒng)崩潰
舉一個簡單例子:
- session1啟動一個事務，對表t1執(zhí)行一個簡單的查詢；
- 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是一個執(zhí)行頻繁的表，show processlist會發(fā)現(xiàn)大量‘waiting for table metadata lock’的線程，數(shù)據(jù)庫連接很快就會消耗完，導致業(yè)務系統(tǒng)無法正常響應。
此時如果session1提交，是session2的alter語句先執(zhí)行還是session3和session4先執(zhí)行呢？之前一直以為先到的先執(zhí)行，當然是session2先執(zhí)行，但經過測試，在5.7中，session3和session4先執(zhí)行，session2最后執(zhí)行，也就會出現(xiàn)alter長時間無法執(zhí)行的情況；而在8.0中，session2先執(zhí)行，session3和session4后執(zhí)行，由于5.6以后ddl是online的，session2并不會阻塞session3和session4，感覺這樣才是合理的，alter不會被‘餓死’。
四、MDL的生命周期有多長
事務！事務！事務！ 重要的事情說三遍，表上的metadata lock的生命周期從事務中的第一條涉及自身的語句開始，到整個事務結束而結束。而5.5之前是基于語句的，事務中執(zhí)行完語句就釋放，如果此時另外一個session對表做了一個刪字段操作，那么就會造成兩個問題：
- ddl操作如果先于事務完成，那么binlog中ddl就會排在事務之前，明顯和邏輯不符，觸發(fā)了本文開始提到的bug。
- 如果是RR隔離級別，那么事務中此表第二次執(zhí)行將無法返回同樣的結果，無法滿足可重復讀的要求。
所以，如果要降低metadata lock的鎖等待時間，最好要及時提交事務，同時盡量避免大事務。
那么如果發(fā)生metadata lock鎖等待，等待鎖的session會等待多長時間呢？大家都知道MySQL里面行鎖等待有個超時時間（參數(shù)innodb_lock_wait_timeout），默認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)
這么長的數(shù)字，掰著指頭算了半天，居然真的是......一年，環(huán)游世界一圈回來還得接著等!!!
當然，生產環(huán)境中，我們很少會等待metadata lock超時，更多的是要想辦法把產生metadata lock的源頭找到，快速提交或者回滾，或者想辦法kill掉。那么如何找到阻塞的源頭呢？
五、如何快速找到阻塞源頭
快速解決問題永遠是第一位的，一旦出現(xiàn)長時間的metadata lock，尤其是在訪問頻繁的業(yè)務表上產生，通常會導致表無法訪問，讀寫全被阻塞，此時找到阻塞源頭是第一位的。這里最重要的表就是前面提到過的
performance_schema.metadata_locks表。
metadata_locks是5.7中被引入，記錄了metadata lock的相關信息，包括持有對象、類型、狀態(tài)等信息。但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'
單純查詢這個表無法得出具體的阻塞關系，也無法得知什么語句造成的阻塞，這里要關聯(lián)另外兩個表performance_schema.thread和
performance_schema.events_statements_history,thread表可以將線程id和show processlist中id關聯(lián)，events_statements_history表可以得到事務的歷史sql，關聯(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對于前面的例子執(zhí)行此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)根據(jù)顯示結果，processlist_id為27的線程阻塞了28的線程，我們需要kill 27即可解鎖。
實際上，MySQL也提供了一個類似的視圖來解決metadata lock問題，視圖名稱為sys.schema_table_lock_waits，但此視圖查詢結果有bug，不是很準確，建議大家還是參考上面sql。
六、本文開始的案例最終如何解決
通過前面的介紹，本文開始的案例產生的過程就很簡單了：用戶執(zhí)行了一個全表delete，在目標表上加了metadata讀鎖，由于表很大，讀鎖長時間無法釋放，后來另外一個session執(zhí)行了drop table操作，又需要在表上加metadata寫鎖，由于讀寫鎖互相阻塞，drop操作只能等待delete操作完成才能獲得寫鎖，因此從表面來看，二個命令都長時間沒有響應，其實內部一個在執(zhí)行，一個在等待。
那怎么來解決呢？因為從show processlist以及客戶描述可以很清楚的知道故障機制，當時建議客戶將delete操作kill掉，等數(shù)據(jù)回滾完后再執(zhí)行drop操作因為delete已經執(zhí)行了一段時間，回滾過程可能會較長，客戶最終kill delete后順利drop成功。
小結
生產環(huán)境大多是dml操作，metadata讀鎖之間不會產生鎖等待，而目前MySQL的ddl操作大多可以online執(zhí)行，因此即使有寫鎖，也會很快降級為讀鎖，所以ddl執(zhí)行期間阻塞dml的幾率也很小。最容易出現(xiàn)的情況是由于有未完成的事務，導致ddl metadata 寫鎖無法加上，只能在鎖隊列等待，而一旦進入鎖隊列，寫鎖又會阻塞其他的讀鎖，導致數(shù)據(jù)庫連接快速增長，直至消耗殆盡，最終業(yè)務受到影響。
為了盡可能避免類似問題，下面是幾個小建議：
- 生產環(huán)境的任何大表或頻繁操作的小表，ddl都要非常慎重，最好在業(yè)務低峰期執(zhí)行。
- 設計上要盡可能避免大事務，大事務不僅僅會帶來各種鎖問題，還好引起復制延遲/回滾空間爆滿等各類問題。
- 要及時提交事務，經常發(fā)現(xiàn)客戶端設置了事務手工提交，但sql執(zhí)行后忘記點擊提交按鈕，導致事務長時間無法提交。建議監(jiān)控實例中的長事務，避免由于各種原因導致事務沒有及時提交。
到此這篇關于一文搞懂MySQL元數(shù)據(jù)鎖(MDL)的文章就介紹到這了,更多相關MySQL元數(shù)據(jù)鎖內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
本篇文章是對MySQL的數(shù)據(jù)庫引擎的類型進行了詳細的分析介紹，需要的朋友參考下2013-06-06
Centos 7下使用RPM包安裝MySQL 5.7.9教程
這篇文章主要為大家詳細介紹了Centos 7下使用RPM包安裝MySQL 5.7.9的教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-05-05
MySQL數(shù)據(jù)備份、還原、數(shù)據(jù)庫遷移以及表的導出和導入
作為流行的開源數(shù)據(jù)庫管理系統(tǒng),MySQL的使用者眾多,為了維護數(shù)據(jù)安全性,數(shù)據(jù)備份是必不可少的,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)備份、還原、數(shù)據(jù)庫遷移以及表的導出和導入的相關資料,需要的朋友可以參考下2022-11-11
Linux中更改轉移mysql數(shù)據(jù)庫目錄的步驟
前幾天發(fā)現(xiàn)由于MySQL的數(shù)據(jù)庫太大，默認安裝的/var盤已經再也無法容納新增加的數(shù)據(jù)，只能想辦法轉移數(shù)據(jù)的目錄。網上有很多相關的文章寫到轉移數(shù)據(jù)庫目錄的文章，但轉載的過程中還會有一些錯誤，因為大部分人根本就沒測試過，這篇文章是本文測試過整理好后分享給大家。2016-11-11
MySQL 5.5.49 大內存優(yōu)化配置文件優(yōu)化詳解
最近mysql服務器升級到了MySQL 5.5.49版本，性能比mysql 5.0.**肯定效率高了不少，但mysql的默認配置文件不合理，這里是針對大內存訪問量大的機器的配置方案，需要的朋友可以參考下2016-05-05
在本文里我們給大家分享了關于MySQL數(shù)據(jù)的導出與導入的相關實例和知識點內容，需要的朋友們跟著學習下。2019-03-03
數(shù)據(jù)庫管理系統(tǒng)中,死鎖是指多個事務互相等待對方釋放資源,導致事務僵持不前,影響系統(tǒng)穩(wěn)定性,本文詳細介紹了如何在MySQL中排查和分析死鎖問題,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-09-09
MySQL重啟之后無法寫入數(shù)據(jù)的問題排查及解決
客戶在給系統(tǒng)打補丁之后需要重啟服務器,數(shù)據(jù)庫在重啟之后,read_only 的設置與標準配置 文件中不一致,導致主庫在啟動之后無法按照預期寫入,所以本文給大家介紹了MySQL重啟之后無法寫入數(shù)據(jù)的問題排查及解決,需要的朋友可以參考下2024-05-05
在平常的項目中,我們會需要批量操作數(shù)據(jù)庫的時候,例如：批量修改,批量插入,那我們不應該使用 for 循環(huán)去操作數(shù)據(jù)庫,這樣會導致我們反復與數(shù)據(jù)庫發(fā)生連接和斷開連接,影響性能和增加操作時間,所以可以使用SQL 批量修改的方式去操作數(shù)據(jù)庫,感興趣的朋友一起學習下吧2023-09-09
連接(join)查詢是將兩個查詢的結果以“橫向對接”的方式合并起來的結果,這篇文章主要給大家介紹了關于MYSQL連接查詢的相關資料,需要的朋友可以參考下2021-06-06