Mysql DDL出現長時間等待MDL問題分析

一灰灰發表於2019-03-08
MySql

給表新增欄位時，發現鎖表了，檢視程式，提示Waiting for table metadata lock，等待鎖釋放；然而蛋疼的是幾分鐘過去了，依然沒有任何的進展，特此記錄下這個問題的定位過程以及MDL的相關背景知識

看到上面的表現，基本問題就來了

  • Metadata Lock 是什麼鬼
  • 是什麼原因導致一直等待

I. 問題定位

首先需要確認什麼地方加鎖，從mysql出發，應該怎麼定位？

1. 定位過程

對於mysql而言，一般來講上鎖和事物時伴生關係，所以我們的直觀出發點就是查詢db當前正在執行的事物

-- 查詢當前正在執行的事物的sql
SELECT * FROM information_schema.INNODB_TRX;
複製程式碼

輸出結果如下，首先拿到事物對應的程式id

Mysql DDL出現長時間等待MDL問題分析

拿到id之後，則可以分析對應的程式資訊

-- 查詢程式資訊
show processlist

-- 查詢所有的程式資訊
show full processlist
複製程式碼

然後定位到具體的程式

Mysql DDL出現長時間等待MDL問題分析

然後登陸到目標機器，檢視埠號對應的程式，通過lsof命令檢視

lsof -i tcp:52951
複製程式碼

從圖中可以看出，是一個python程式的mysql連線開啟的事物，程式id為5436

Mysql DDL出現長時間等待MDL問題分析

接著檢視程式對應的資訊

ps aux | grep 5436
複製程式碼

Mysql DDL出現長時間等待MDL問題分析

這個指令碼正是測試aiomysql的python指令碼，內容比較簡單

import asyncio
import aiomysql

loop = asyncio.get_event_loop()


@asyncio.coroutine
def test_example():
    conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
                                       user='root', password='', db='test',
                                       loop=loop, autocommit=False)

    cur = yield from conn.cursor()
    yield from cur.execute("SELECT * from test_table")
    print(cur.description)
    r = yield from cur.fetchall()
    print(r)
    yield from cur.close()
    conn.close()

loop.run_until_complete(test_example())
複製程式碼

2. 原因分析

對python不太熟，直接藉助google查一下，發現有同樣的問題

這個問題丟擲，在通過with開啟連線獲取遊標後，執行mysql，但是沒有commit之前，會鎖表，這個期間修改表都會出現等待

下面近給出瞭解答，並沒有看到更多的深層次的說明，先記錄下，解決辦法就是在建立連線池的時候，選擇自動提交方式，然後就不會有這個問題了

pool = await aiomysql.create_pool(
    host="localhost",
    user="test",
    password="test",
    db="test",
    autocommit=True,
    cursorclass=DictCursor,
    loop=loop)
複製程式碼

II. Metadata Lock說明

找到一篇文章說MDL的，推薦詳細閱讀 MySQL表結構變更你不可不知的Metadata Lock詳解

1. MDL 說明

抓一下核心的要點，簡單說一下看完這篇文章之後的樸素理解

MetaData Lock 簡稱為MDL，簡單來說就是表的後設資料鎖；當修改表結構的時候，就需要持有這個鎖

a. 作用

MDL的主要作用只有一點，保護一個正在執行的事物表結構不被修改

有一個原則，MDL是事物級別的，只有事物結束之後才會釋放，而這裡面說的事物分為兩類

  • 顯示事物：
    • 關閉autocommit
    • 以begin或start transaction開始的操作
  • AC-NL-RO(auto-commit non-locking read-only):
    • auto commit 開啟之下的select操作

b. 例項說明

直接看上面的說明，不太直觀，一個經典的case如下

Mysql DDL出現長時間等待MDL問題分析

session1 開啟了一個事物，執行查詢操作；但是現在session2 要刪除表，如果執行成功，那麼session1的第二次查詢就跪了，這樣就違背了事物的原則，所有在5.5版本引入了MDL，來保證在事物執行期間，表結構不被修改

2. 出現MDL等待原因及解決方法

當我們出現修改表結構，就需要獲取MDL的排他鎖，因此只有這個表沒有事物在執行時，才能獲取成功；當持有獨佔鎖之後，這個表的其他操作將被阻塞（即不能插入資料，修改資料，也不能開啟事物操作）

因此在執行DDL時，一直出現等待MDL的時候，常見的原因有下面三個

a. 長事物，阻塞DDL，從而阻塞所有同表的後續操作

通過 show processlist看到表上有正在進行的操作（包括讀），此時修改表時也會等待獲取MDL，這種時候解決辦法要麼就是等待執行完畢，要麼就是直接kill掉程式

b. 未提交事物，阻塞DDL

通過 show processlist沒有找到表上的操作，但是通過information_schema.innodb_trx發現有未提交的事物，

c. 異常的狀況

通過 show processlist 和事物查詢都沒有的情況下，可能的場景是一個顯示的事物中，對錶的操作出現了異常，雖然事物失敗，但是持有的鎖還沒有釋放，也會導致這個原因

可以在performance_schema.events_statements_current表中查詢失敗的語句

3. MDL分類與sql例項

前面兩小節，分別說明什麼是MDL（樸素理解為表的後設資料鎖），以及當修改表時出現長時間的等待MDL的原因分析；正常看完之後，應該會有下面的疑惑

  • MDL有哪些型別
  • 哪些sql會持有MDL

對於MDL的型別，從網上截一張圖

Mysql DDL出現長時間等待MDL問題分析

接下來需要分析下不同鎖模式對應的sql

屬性 含義 事例
MDL_INTENTION_EXCLUSIVE(IX) 意向排他鎖用於global和commit的加鎖。 truncate table t1; insert into t1 values(3,’abcde’); 會加如下鎖 (GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE）(SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE)
MDL_SHARED(S) 只訪問後設資料 比如表結構，不訪問資料。 set golbal_read_only =on 加鎖 (GLOBAL，MDL_EXPLICIT，MDL_SHARED）
MDL_SHARED_HIGH_PRIO(SH) 用於訪問information_scheam表，不涉及資料。 select * from information_schema.tables;show create table xx; desc xxx; 會加如下鎖： (TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO)
MDL_SHARED_READ(SR) 訪問表結構並且讀表資料 select * from t1; lock table t1 read; 會加如下鎖: (TABLE，MDL_TRANSACTION，MDL_SHARE_READ）
MDL_SHARED_WRITE(SW) 訪問表結構並且寫表資料 insert/update/delete/select .. for update 會加如下鎖：(TABLE，MDL_TRANSACTION，MDL_SHARE_WRITE)
MDL_SHARED_UPGRADABLE(SU) 是mysql5.6引入的新的metadata lock,可以說是為了online ddl 才引入的。特點是允許DML，防止DDL； alter table/create index/drop index 會加該鎖; 加入下鎖 （TABLE，MDL_TRANSACTION，MDL_SHARED_UPGRADABLE）
MDL_SHARED_NO_WRITE(SNW) 可升級鎖，訪問表結構並且讀寫表資料，並且禁止其它事務寫。 alter table t1 modify c bigint; (非onlineddl) (TABLE，MDL_TRANSACTION，MDL_SHARED_NO_WRITE）
MDL_SHARED_NO_READ_WRITE(SNRW) 可升級鎖，訪問表結構並且讀寫表資料，並且禁止其它事務讀寫。 lock table t1 write; 加鎖 (TABLE，MDL_TRANSACTION,MDL_SHARED_NO_READ_WRITE
MDL_EXCLUSIVE(X) 防止其他執行緒讀寫後設資料 CREATE/DROP/RENAME TABLE，其他online DDL在rename階段也持有X鎖(TABLE，MDL_TRANSACTION，MDL_EXCLUSIVE）

4， 小結

上面的內容，可能資訊量比較大，特別是MDL的鎖分類情況，很難抓住重點，針對我們日常接觸中，簡單給出小結

  • MDL是為了保證事物執行過程中，表結構不被修改引入的；因此修改表結構的前提是這個表上沒有事物（沒有正在執行，失敗，或者未提交的事物）
  • DDL執行，一般來講是需要獲取排他的MDL
  • DML都會開啟事物，因此會獲取 MDL_SW 鎖
  • DQL語句會獲取 MDL_SR 鎖

幾個簡稱的說明

  • MDL: metadata lock，可以簡單理解為表的後設資料鎖
  • DDL: 資料定義語言，可以簡單理解為表的操作，如建立，修改，刪除表、檢視等，新增索引、欄位等操作
  • DML: 資料操作語言，也就是我們常規理解的 insert, update, delete 語句
  • DQL: 資料查詢語言，常見的select語句

幾個常見疑問解答

a. 為什麼同一張表的多個DDL不能並行執行

  • MDL讀鎖是互相相容的,可以有多個增刪查改
  • MDL寫鎖是互斥的,只能有一個表的DDL

b. 為什麼有時候DDL會卡住

  • MDL讀寫鎖之間是互斥的,所以如果DDL卡住,就證明有事務在執行,不能申請MDL寫鎖

c. 常見卡住的場景

  • 非常頻繁的業務高峰期
  • 有慢查詢把持著MDL讀鎖
  • 有事物一直未提交

d. 為什麼需要MDL鎖

  • 當事務本身執行的時候理論上是不能容忍表結構在中途發生改變的

5. 更多參考

相關博文或者問答

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

一灰灰的個人部落格，記錄所有學習和工作中的博文，歡迎大家前去逛逛

2. 宣告

盡信書則不如，已上內容，純屬一家之言，因個人能力有限，難免有疏漏和錯誤之處，如發現bug或者有更好的建議，歡迎批評指正，不吝感激

3. 掃描關注

一灰灰blog

QrCode

知識星球

goals

相關文章