【MySQL】MetaData Lock 之三

一 簡介
 通過前面兩篇文章的介紹，相信讀到這裡的各位對MDL 鎖已經有了比較深入的瞭解了,本文將結合理論知識介紹幾組MDL 鎖的案例。

二 常見MDL 鎖的場景
1 Waiting for global read lock
  我們先構造一個Waiting for global read lock場景：
  session1: alter table t1 add c3 bigint; //大表執行需較長時間
  session2: set global read only=on; //等待
  檢視
分析： 
 alter table t1 add c3 bigint;會加（GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE) 語句結束後才釋放
 set global read only=on; 會加（GLOBAL，MDL_EXPLICIT，MDL_SHARED）
 由於session1執行時間比較長，一直持有MDL_INTENTION_EXCLUSIVE。從相容性矩陣可以看出MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的，因此發生“Waiting for global read lock ”等待。直到session 1 alter操作完成釋放MDL_INTENTION_EXCLUSIVE。set global read only=on;才可以繼續執行。  
2.2 Waiting for commit lock
session1: 
          begin;
          insert into t1 vlaues(null, 'ab');        
session2:
          flush table with read lock;//成功
session1: commit //發生等待
mysql> show processlist; 分析：
 flush table with read lock;持有（COMMIT，MDL_EXPLICIT，MDL_SHARED）
 commit時上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）鎖
 MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的，因此發生等待.
2.3 Waiting for table metadata lock
這個是比較常見的鎖等待，總結下來有如下幾種場景
1 長查詢/mysqldump 阻塞DDL 

session1	session2	session3
Select count(*) from t;
	alter table t add column c3 int;
		Show processlist； session2：copy to tmp table
	阻塞	Show processlist; session2：Waiting for table metadata lock
A：執行完畢
		Show processlist; session2：rename table
Select count(*) from t;
	B：執行完畢
		Show processlist; session1: Sending data

會話1先執行select ,會話2後執行alter,在會話1執行完畢前，會話2拿不到MDL鎖，從表格上面來看，主要阻塞在rename階段。會話1在執行完畢後，會話2拿到MDL鎖，變為rename table狀態，這個操作持續時間非常短,會話1再次執行查詢，當會話2執行完後，此時會話1正常執行。這說明對於MDL鎖而言，select會阻塞alter，而alter不會阻塞select。在rename的瞬間，alter是會阻塞select的.
當執行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飆高的情況。
2 未提交的事務阻塞 DDL 
  

session1	session2	session3
begin; update t set where id=3;
	alter table t add column c3 int;//hang
		Show processlist; session2：Waiting for table metadata lock
		Select count(*) from t;//hang
Show processlist; session2：Waiting for table metadata lock session3：Waiting for table metadata lock

session1 對錶t進行update操作，存在未提交的事務，故一直持有 MDL_SHARED_WRITE鎖，由於沒有執行COMMIT，會一直持有。session2 DDL 操作會請求TABLE-TRANSACTION-EXCLUSIVE鎖，該鎖與session1 的MDL_SHARED_WRITE 鎖互斥，故 session2 的DDL 等待；session3的查詢操作會請求TABLE- TRANSACTION- MDL_SHARED_READ鎖，雖然MDL_SHARED_READ與活躍鎖MDL_SHARED_WRITE不衝突，但是與session2的等待鎖EXCLUSIVE衝突，因此也會等待。
解決該中場景的問題比較麻煩，但從show processlist 不能檢查出哪個會話持有鎖 。可以從兩個方面進行調查 
a 查詢 information_schema.innodb_trx
  
b 檢查 show engine innodb status ; 
  
 active N sec 說明事務持續了N秒，一般而言超過10秒的事務都是有問題的 。找到了活動的事務之後，要和開發溝通看看如何處理，能否直接kill 該回話？ 根據和開發溝通的結果採取相應的措施。
3 第1種情況的特例，存在一個查詢失敗的語句，比如查詢不存在的列，語句失敗返回，但是事務沒有提交，此時alter仍然會被堵住。 
   通過show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。這很可能是因為在一個顯式的事務中，對TableA進行了一個失敗的操作（比如查詢了一個不存在的欄位），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效。從performance_schema.events_statements_current表中可以查到失敗的語句。
官方手冊上對此的說明如下：
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
也就是說除了語法錯誤，其他錯誤語句獲取到的鎖在這個事務提交或回滾之前，仍然不會釋放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解釋這一行為的原因很難理解，因為錯誤的語句根本不會被記錄到二進位制日誌。

三 參考 
[1] 《MySQL出現Waiting for table metadata lock的場景淺析》 
[2] 《MySQL中 metadata lock問題分析》
[3] 《官方Metadata Lock 介紹》

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