mysql metadata lock(二)

標籤：

     上一篇《mysql metadata lock(一)》介紹了為什麼引入MDL，MDL作用以及MDL鎖導致阻塞的幾種典型情境，文章的最後還留下了一個小小的疑問。本文將更詳細的介紹MDL，主要側重介紹MDL的原理和實現。一般而言，商務資料庫系統實現鎖，一般將鎖劃分為讀鎖(共用鎖定)和寫鎖(排它鎖)，為了進一步提高並發性，還會加入意圖共用鎖和意向排它鎖。但是偏偏mysql的MDL搞地比較複雜，但目的也是為了提高並發度。MDL包含有9種類型，詳細參考表1。主要其實也是兩大類，只是對共用鎖定做了進一步細分。

一、MDL的鎖類型

鎖名稱	鎖類型	說明	適用語句
MDL_INTENTION_EXCLUSIVE	共用鎖定	意圖鎖定，鎖住一個範圍	任何語句都會擷取MDL意圖鎖定， 然後再擷取更強層級的MDL鎖。
MDL_SHARED		共用鎖定，表示只訪問表結構
MDL_SHARED_HIGH_PRIO		共用鎖定，只訪問表結構	show create table 等 只訪問INFORMATION_SCHEMA的語句
MDL_SHARED_READ		訪問表結構並且讀表資料	select語句 LOCK TABLE ...  READ
MDL_SHARED_WRITE		訪問表結構並且寫表資料	SELECT ... FOR UPDATE DML語句
MDL_SHARED_UPGRADABLE		可升級鎖，訪問表結構並且讀寫表資料	Alter語句中間過程會使用
MDL_SHARED_NO_WRITE		可升級鎖，訪問表結構並且讀寫表資料，並且禁止其它事務寫。	Alter語句中間過程會使用
MDL_SHARED_NO_READ_WRITE		可升級鎖，訪問表結構並且讀寫表資料，並且禁止其它事務讀寫。	LOCK TABLES ... WRITE
MDL_EXCLUSIVE	寫鎖	禁止其它事務讀寫。	CREATE/DROP/RENAME TABLE等DDL語句。

　　　　　　　　　　　　　　　　　　　　表1

二、MDL的相容性矩陣

	IX	S	SH	SR	SW	SU	SNW	SNRW	X
IX	1	1	1	1	1	1	1	1	1
S	1	1	1	1	1	1	1	1	0
SH	1	1	1	1	1	1	1	1	0
SR	1	1	1	1	1	1	1	0	0
SW	1	1	1	1	1	1	0	0	0
SU	1	1	1	1	1	1	0	0	0
SNW	1	1	1	1	0	0	0	0	0
SNRW	1	1	1	0	0	0	0	0	0
X	1	0	0	0	0	0	0	0	0

說明：橫向表示其它事務已經持有的鎖，縱向表示事務想加的鎖

 

三、幾種典型語句的加(釋放)鎖流程

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鎖，大部分情況都是線上出現異常了。那麼出現異常後，我們如何去判斷是MDL鎖導致的呢。監視MDL鎖主要有兩種方法，一種是通過show  processlist命令，判斷是否有事務處於“Waiting for table metadata lock”狀態，另外就是通過mysql的profile，分析特定語句在每個階段的耗時時間。

拋出幾個問題：

1. select 與alter是否會相互阻塞
2. dml與alter是否會相互阻塞
3. 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階段。結合第一個和第二個問題，就可以回答《mysql metadata lock(一)》的疑問了。

第三個問題，顯然，由於MDL_SHARED_WRITE與MDL_SHARED_READ相容，所以它們不會因為MDL而導致等待的情況。具體例子和profile分析可以參考《mysql metadata lock(一)》。

 

mysql metadata lock(二)