????????????????????????????????????????????????????1
最近遇到一次metadata lock導致大量線程卡住的問題苍凛，比較有代表性慌申，撰文一篇胶滋，希望對廣大DBA們有幫助璧疗。
同事反饋說在使用pt-online-schema-change添加索引的時候，一直卡住，而且會卡住很久怨酝。
登錄服務器傀缩，使用show full processlist命令查看線程狀態(tài)，發(fā)現大量的Waiting for table metadata lock农猬，如下圖：
找到等待時間最長Waiting for table metadata lock的SQL赡艰，正是同事添加索引的時候創(chuàng)建觸發(fā)器的SQL，如下圖：
初步確定原因是創(chuàng)建觸發(fā)器的時候斤葱，有某個事務持有metadata lock沒有釋放慷垮，導致創(chuàng)建觸發(fā)器的SQL被阻塞，進而導致后面關于t_log_sp_detail_10的所有操作都被阻塞揍堕。
解決辦法料身，終止添加索引的操作，kill掉創(chuàng)建觸發(fā)器的線程鹤啡，業(yè)務恢復惯驼。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2
這里面引申出2個問題：
1蹲嚣、是什么SQL持有metadata lock那么長的時間一直不釋放递瑰？
2、為什么metadata lock那么長的時間都不超時隙畜？
下面就來尋根問底：
我們的環(huán)境是5.7抖部，在MySQL 5.7中，可以通過查看performance_schema.metadata_locks來查看對應metadata lock的情況议惰，開啟方式如下：
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
不過在performance_schema.metadata_locks表中并沒有記錄對應的線程ID慎颗，需要結合performance_schema.INNODB_TRX來進一步分析。
但是言询，那臺機器關閉了performance_schema俯萎，如果要啟用的話必須重啟實例，因此只能使用笨一點的辦法运杭。
只要抓取對應的信息就能快速定位夫啊，這些信息包括：
（1）、開啟general日志（短暫開啟）
（2）辆憔、抓取show full processlist的信息（每隔1秒）
（3）撇眯、抓取performance_schema.INNODB_TRX的信息（每隔1秒）
通知同事，繼續(xù)使用pt添加索引虱咧，重現問題熊榛。
問題重現后，查看抓取到的performance_schema.INNODB_TRX信息腕巡，找到對應事件正在進行的事務玄坦，截圖如下：
查看general日志對應線程做了什么事情：
查看抓取到proocesslist信息，發(fā)現這個線程是sleep的狀態(tài)绘沉，問題很明顯是這個線程沒有提交導致煎楣。
和業(yè)務溝通后云挟，kill掉該線程，索引正常添加转质。
第1個問題答案是select線程沒有提交導致园欣。
再來看第2個問題
之所以等待那么久都沒有超時，是因為metadata lock不是InnoDB引擎層的鎖休蟹，而是server層的鎖沸枯，控制鎖超時的參數不一樣，如下：
上面的截圖中innodb_lock_wait_timeout是控制InnoDB引擎層的鎖超時赂弓，而lock_wait_timeout是控制server層的鎖超時绑榴。
而上圖中，lock_wait_timeout設置得很大盈魁，可以調小翔怎。
????????????????????????????????????????????????????3
回答完上面的2個問題，我們再來深入一點了解一下metadata lock這個東東杨耙。主要從3個方面來看：
一赤套、為什么要引入metadata lock
? ? 為了在并發(fā)環(huán)境下維護表元數據的數據一致性，在表上有活動事務（顯式或隱式）的時候珊膜，不可以對元數據進行寫入操作容握。因此從MySQL5.5版本開始引入了MDL鎖（metadata lock），來保護表的元數據信息车柠，用于解決或者保證DDL操作與DML操作之間的一致性剔氏。
? ? 上面的描述可能不是特別好理解，來舉一個之前姜老師說過的例子竹祷，這個例子就能很形象地說明為什么要引入metadata lock谈跛。看如下兩個session
上面的例子中塑陵，如果沒有metadata lock的保護感憾，會導致SESSION 2順利執(zhí)行，SESSION 1出錯猿妈。
在5.5.3版本加入metadata lock以后吹菱，由于SESSION 1先查詢了TABLE1，持有TABLE1的metadata lock彭则，會導致SESSION 2等待鳍刷，直到SESSION 1提交。
正因為如此俯抖，MySQL在5.5.3版本后引入了Metadata lock鎖输瓜，事務釋放后才會釋放Metadata lock，因此，在事務完成之前尤揣，DDL是無法執(zhí)行的搔啊。
有興趣的還可以看看這個典型的bug。
二北戏、什么場景下會引起metadata lock
有如下幾種場景會引起metadata lock鎖等待問題
1负芋、有長時間運行的DML語句
? ? ? 這種情況會導致metadata lock等待，在工作中如果要對某個表進行DDL操作之前嗜愈，需要先用show processlist看看是否有長時間運行的SQL旧蛾，防止出現線上故障。
2蠕嫁、有未提交的事務
? ? ? 這個就是我們開篇遇到的問題锨天，不管是select還是update，如果沒有提交都會造成metadata lock等待剃毒。
3病袄、在執(zhí)行期間失敗的語句，不會立即釋放metadata lock
? ? 官網是這么說的：
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.
? ? 舉個例子就很容易理解了赘阀，比如我開啟一個事務益缠，查詢一個不存在的事務，索然語句執(zhí)行失敗纤壁，但是持有的metadata lock并沒有釋放：
session1執(zhí)行如下操作：
session 2執(zhí)行添加字段的DDL左刽，session2就一直在等待中，直到session1提交
三酌媒、如何快速解決metadata lock引發(fā)的鎖等待問題
從上面原理和場景中，我們可以看到基本就兩種情況迄靠。
一種是有長時間運行的DML語句的時候秒咨，這種情況要么kill掉DDL語句，要么就kill掉長時間運行的DML掌挚。
另外一種情況是未提交的事務（正常的語句和失敗的語句）雨席，這種情況要解決稍微復雜一點，需要找到對應沒有提交的線程進行kill吠式，或者kill掉DDL語句陡厘。最快速的解決辦法就是將所有sleep的線程都殺掉。
備注：將所有sleep的線程都殺掉這個操作會導致沒有提交的事務回滾特占，是有風險的糙置，請根據業(yè)務場景進行操作。
延展閱讀：https://www.cnblogs.com/zengkefu/p/5690385.html 【姜老師的文章】
四是目、參考資料：
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
