MySQL出現Waiting for table metadata lock的原因以及解決方案

標籤：view   測試   thread   5.6   官方   pop   災難   logs   alter   

MySQL出現Waiting for table metadata lock的原因以及解決方案

MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待情境。而且，一旦alter table TableA的操作停滯在Waiting for table metadata lock的狀態，後續對TableA的任何操作（包括讀）都無法進行，因為他們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果是產品環境的核心表出現了這樣的鎖等待隊列，就會造成災難性的後果。

造成alter table產生Waiting for table metadata lock的原因其實很簡單，一般是以下幾個簡單的情境：

情境一：長事物運行，阻塞DDL，繼而阻塞所有同表的後續操作

通過show processlist可以看到TableA上有進行中的操作（包括讀），此時alter table語句無法擷取到metadata 獨佔鎖，會進行等待。

這是最基本的一種情形，這個和mysql 5.6中的online ddl並不衝突。一般alter table的操作過程中（見），在after create步驟會擷取metadata 獨佔鎖，當進行到altering table的過程時（通常是最花時間的步驟），對該表的讀寫都可以正常進行，這就是online ddl的表現，並不會像之前在整個alter table過程中阻塞寫入。（當然，也並不是所有類型的alter操作都能online的，具體可以參見官方手冊：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）
處理方法： kill 掉 DDL所在的session.

 

情境二：未提交事物，阻塞DDL，繼而阻塞所有同表的後續操作

通過show processlist看不到TableA上有任何操作，但實際上存在有未提交的事務，可以在 information_schema.innodb_trx中查看到。在事務沒有完成之前，TableA上的鎖不會釋放，alter table同樣擷取不到metadata的獨佔鎖。

處理方法：通過 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然後 kill 掉，讓其復原。

 

情境三：

通過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 但是解釋這一行為的原因很難理解，因為錯誤的語句根本不會被記錄到二進位日誌。

處理方法：通過performance_schema.events_statements_current找到其sid, kill 掉該session. 也可以 kill 掉DDL所在的session.

 

總之，alter table的語句是很危險的(其實他的危險其實是未提交事物或者長事務導致的)，在操作之前最好確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有明確交易中的報錯語句。如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設定好逾時時間，避免長時間的metedata鎖等待。

   waiting for table metadata lock 問題深入分析      相信很多msyql dba都碰到鎖的問題，在MySQL 5.5.3版本中引入了Metadata lock： DDL語句打破了事務的隔離等級。
那麼會有同學問，為什麼在Mysql 5.5.3之前就很少遇到這種鎖呢？原因是
    5.5.3版本之前,MySQL事務對於表結構中繼資料( Metadata)的鎖定是 語句(statement)粒度的：即語句執行完成後，不管事務是否可以完成，其表結構就可以被其他會話更新掉！
    引入Metadata lock後，表結構中繼資料(Metadata)的鎖定變成了 事務(transaction)粒度的,即 只有事務結束時才會釋放Metadata lock。

怎麼出現的？

程式或者指令碼顯式開啟事務（start transaction），該事務內的query語句(包含select)會佔用相關表的metadata lock(profile:Opening tables階段)。導致後續的所有DDL動作陳述式全部被阻塞，原因就是擷取不到metadata lock。（在mysql 5.6版本後有最佳化）官方手冊參閱：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

我們下面以現網case來探討這個問題的出現於解決：

 

case

業務執行一條簡單的alter table 操作，增加一個欄位，很普通的一條sql，而且表不大，資料量很少，執行卻消耗幾百秒沒反應（現場忘記）

補充一個測試圖

從圖可以看出業務執行的語句遇到metadata lock了。

1、        分析mysql的執行個體的情況

1.1 mysql> show processlist;

除了有一個 Waiting for table 之外沒有其它的操作進程，全部是sleep進程。這時你覺得奇怪嗎？為什麼沒有其它的進程鎖住這個表，會導致這個ddl語句一直卡住呢？ 我們接著分析。

1.2 查看錶是否太大 mysql> show table status like ‘tbl_xx‘ \G

                           圖1.2

看出表非常小，不存在由於資料量大導致更新慢的問題；

1.3 查看引擎狀態 mysql> show engine innodb status \G

資料量太大，一螢幕都顯示不完，不看了。

既然幾個比較直接的方法都查不到原因，那隻能更深入的查下了，我打算從資料字典中查下（information_schema，performance_schema）：

1.4，尋找當前等待事務：

mysql> select * from  performance_schema .events_waits_current;

Empty set (0.03 sec)

顯示空。

尋找information_schema中的事件表（EVENTS）、鎖等待表（INNODB_LOCK_WAITS），innodb當前出現的鎖（INNODB_LOCKS）均沒看到異常（這裡就不貼圖了）。

1.5 尋找事務

既然造成該鎖的原因是事務沒有提交導致的，那我們應該去尋找當前是否有事務在運行（runing註：由於事務一直是runing狀態，這也就是為什麼我之前尋找各種鎖都找不到的原因）

mysql> select * from information_schema.innodb_trx;

（此圖又被刷不見了）不過有重大發現：一個trx_mysql_thread_id: 275255348 是從trx_started: 2015-12-03 14:58:45 一直處於runing狀態的。

既然我們找到了id了 那我們再回顧使用show processlist尋找該ID就行了：

發現了嗎，該ID一直是sleep狀態。很難發現該進程開啟了這個表（可以通過show open tables 查看當前開啟的表）。

 

解決辦法：詢問了開發這個點的指令碼，操作。確認後通過後台mysql 直接kill掉這個進程，業務的alter操作瞬間完成。

附：歡迎大家一起探討研究

MySQL出現Waiting for table metadata lock的原因以及解決方案