一步步搞懂MySQL元數據鎖(MDL)

来源:https://www.cnblogs.com/Jcloud/archive/2022/09/16/16699250.html
-Advertisement-
Play Games

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


某日,路上收到用戶咨詢,為了清除空間,想刪除某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的加鎖信息。

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:執行簡單查詢,為了讓表處於執行狀態,這裡使用了sleep函數。

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表,發現多了一條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:

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的線程還未執行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也有類似參數控制:

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

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

 

當然,生產環境中,我們很少會等待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成功。

 

小結

生產環境大多是dml操作,metadata讀鎖之間不會產生鎖等待,而目前MySQL的ddl操作大多可以online執行,因此即使有寫鎖,也會很快降級為讀鎖,所以ddl執行期間阻塞dml的幾率也很小。最容易出現的情況是由於有未完成的事務,導致ddl metadata 寫鎖無法加上,只能在鎖隊列等待,而一旦進入鎖隊列,寫鎖又會阻塞其他的讀鎖,導致資料庫連接快速增長,直至消耗殆盡,最終業務受到影響。

 

為了儘可能避免類似問題,下麵是幾個小建議:

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

 

作者:翟振興


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在VMware虛擬機下centOS7下配置橋接網路 首先,在以下的配置都操作好之後,要確認宿主機的網路連接方式, 若為需要認證的網路,則可能會出現宿主機與虛擬機能互相ping,但虛擬機不能ping外網的情況 一、虛擬機配置 確保虛擬機為關機的狀態,在VMware中編輯一欄打開虛擬網路編輯器,點擊更改 ...
  • 數據結構概述: 程式 = 數據結構 + 演算法 演算法:一些計算方法,解決問題的流程/步驟(順序、分支、迴圈......),通俗的說,演算法相當於邏輯,小部分已經被人們發掘出來了(這裡的小部分指的是書本上講的一些經典的解決一類問題的方法),解決問題的一種模式。 數據結構:將數據按照某一種特定的結構(方法) ...
  • Set A Light 3D Studio for Mac是一款可以幫助攝影工作者對3D室內攝影進行燈光位置調整的軟體,你可以清楚的瞭解到不同位置的燈光怎麼佈局。Set A Light 3D Studio Mac模擬影樓的效果,使用它可以預先在PC或Mac上搭建的照明設置,逼真地模擬預期的畫面效果和 ...
  • sed用法 基礎sed命令 sed命令的基本語法 sed OPTIONS… [SCRIPT] [INPUTFILE…] 常用的選項: -n,–quiet: 不輸出模式空間中的內容 -i: 直接編輯原文件,預設不對原文件進行操作 -e: 可以使用多個命令(腳本)進行操作 -f /path/from/s ...
  • 超級熱鍵可通過簡單編程 —— 自動化完成複雜操作,提升效率。 ▶ 快速上手 本教程需要一個很小的開源軟體 ImTip ( 體積 639 KB ), 請右鍵點開 ImTip 托盤菜單,然後點擊「管理超級熱鍵」: 然後將熱鍵配置改為如下代碼,並且勾選「啟用超級熱鍵」,再點擊「保存」按鈕使熱鍵生效。 // ...
  • Linux的哲學思想 優勢 一切都是一個文件。(包括硬體,文本,二進位,源代 碼) 系統中擁有小型,單一用途的程式。(一個程式只負責 做好自己的本職工作) 當遇到複雜任務,通過不同功能用途的程式組合起來 完成。 輕量級,一臺服務 dhcp ip 資料庫服務 網 頁 避免令人困惑的用戶界面就是沒有複雜 ...
  • 摘要:如果你的數據量很大,想儘快完成任務執行,可否有其他方案?那一定不要錯過GaussDB(DWS)的MERGE INTO功能。 本文分享自華為雲社區《一招教你如何高效批量導入與更新數據》,作者: acydy。 當前GaussDB(DWS)提供了MERGE INTO功能。本篇文章介紹MERGE IN ...
  • 8月27日,ChunJun社區與OceanBase社區聯合組織的開源線下Meetup成功舉辦,會上重磅發佈了「OceanBase&ChunJun:構建一體化數據集成方案」。 這是OceanBase&ChunJun聯合解決方案的首次發佈,將針對分庫分表的實時數據集成、跨集群/租戶的數據集成、不同數據源 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...