【MySQL】MetaData Lock 之二

一 簡介 
  上一篇文章 《MetaData Lock 之一》  簡單的介紹了MySQL 引入MDL 的前因後果，本文深入瞭解MDL的實現原理和執行機制。
二 MDL 的型別
  metadata lock也是一種鎖。每個metadata lock都會定義鎖住的物件，鎖的持有時間和鎖的型別。
2.1 按照物件/範圍劃分

屬性	含義	範圍/物件
GLOBAL	全域性鎖	範圍
COMMIT	提交保護鎖	範圍
SCHEMA	庫鎖	物件
TABLE	表鎖	物件
FUNCTION	函式鎖	物件
PROCEDURE	儲存過程鎖	物件
TRIGGER	觸發器鎖	物件
EVENT	事件鎖	物件

MDL按鎖住的物件來分類，可以分為global,commit,schema, table, function,procedure,trigger,event,這些物件發生鎖等待時，我們在show processlist可以分別看到如下等待資訊。
 
2.2 按照鎖的持有時間

屬性	含義
MDL_STATEMENT	從語句開始執行時獲取，到語句執行結束時釋放。
MDL_TRANSACTION	在一個事務中涉及所有表獲取MDL，一直到事務commit或者rollback(執行緒中終清理)才釋放。
MDL_EXPLICIT	需要MDL_context::release_lock()顯式釋放。 語句或者事務結束,也仍然持有,如 Lock table, flush .. with lock語句等。

2.3 按照操作的物件

屬性	含義	事例
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, 在alter table/create index/drop index會加該鎖;可以說是為了online ddl才引入的。特點是允許DML，防止DDL；	（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）

關於global物件
   主要作用是防止DDL和寫操作的過程中，執行set golbal_read_only =on或flush tables with read lock;
 關於commit物件鎖
  主要作用是執行flush tables with read lock後，防止已經開始在執行的寫事務提交。
  insert/update/delete在提交時都會上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）鎖。
2.4 MDL 鎖的相容性矩陣



三、幾種典型語句的加(釋放)鎖流程
1.select語句操作MDL鎖流程
   1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_READ鎖
   2)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_SHARED_READ鎖
2. DML語句操作MDL鎖流程
  1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_WRITE鎖
  2)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_SHARED_WRITE鎖
3. alter操作MDL鎖流程
  1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_UPGRADABLE鎖，升級到MDL_SHARED_NO_WRITE鎖
  2)運算元據，copy data，流程如下：
     a)   建立臨時表tmp，重定義tmp為修改後的表結構
     b)   從原表讀取資料插入到tmp表
  3)將MDL_SHARED_NO_WRITE讀鎖升級到MDL_EXCLUSIVE鎖
     a)   刪除原表，將tmp重新命名為原表名
  4)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_EXCLUSIVE鎖

四、典型問題分析
通常情況下我們關注MDL鎖，大部分情況都是線上DB出現異常了。那麼出現異常後，我們如何去判斷是MDL鎖導致的呢。監視MDL鎖主要有兩種方法，一種是透過show  processlist命令，判斷是否有事務處於“Waiting for table metadata lock”狀態，另外就是透過mysql的profile，分析特定語句在每個階段的耗時時間。
丟擲幾個問題：
select 與alter是否會相互阻塞
dml與alter是否會相互阻塞
select與DML是否會相互阻塞
結合第三節幾種語句的上鎖流程，我們很容易得到這三個問題的答案。語句會在阻塞在具體某個環節，可以透過profile來驗證我們的答案是否正確。
第一個問題，當執行select語句時，只要select語句在獲取MDL_SHARED_READ鎖之前，alter沒有執行到rename階段，那麼select獲取MDL_SHARED_READ鎖成功，後續有alter執行到rename階段，請求MDL_EXCLUSIVE鎖時，就會被阻塞。rename階段會持有MDL_EXCLUSIVE鎖，但由於這個過程時間非常短(大頭都在copy資料階段)，並且是alter的最後一個階段，所以基本感覺不到alter會阻塞select語句。由於MDL鎖在事務提交後才釋放，若線上存在大查詢，或者存在未提交的事務，則會出現ddl卡住的現象。這裡要注意的是，ddl卡住後，若再有select查詢或DML進來，都會被堵住，就會出現threadrunning飆高的情況。
第二個問題，alter在opening階段會將鎖升級到MDL_SHARED_NO_WRITE，rename階段再將升級為MDL_EXCLUSIVE，由於MDL_SHARED_NO_WRITE與MDL_SHARED_WRITE互斥，所以先執行alter或先執行DML語句，都會導致語句阻塞在opening tables階段。
第三個問題，顯然，由於MDL_SHARED_WRITE與MDL_SHARED_READ相容，所以它們不會因為MDL而導致等待的情況。

五 參考資料
 [1] 《MDL 鎖介紹二 》他的三篇文章都值得一讀，強烈推薦，本系列文章也是參考他的blog 寫的。
 [2] 《》 @印風的blog 為alisql/RDS 打了N多patch的牛人。


如果您覺得從這篇文章受益，可以微信支付贊助 北在南方 一瓶飲料 ^_^