【MySQL】MetaData Lock 之二

楊奇龍發表於2015-09-05
MySql
一 簡介 
  上一篇文章 《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可以分別看到如下等待資訊。
 
  1. Waiting for global read lock
  2. Waiting for commit lock
  3. Waiting for schema metadata lock
  4. Waiting for table metadata lock
  5. Waiting for stored function metadata lock
  6. Waiting for stored procedure metadata lock
  7. Waiting for trigger metadata lock
  8. Waiting for event metadata lock
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] 《DDL操作導致備庫複製中斷》 @印風的blog 為alisql/RDS 打了N多patch的牛人。


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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1791608/，如需轉載，請註明出處，否則將追究法律責任。

相關文章