????????????????????????????????????????????????????1
最近遇到一次metadata lock導(dǎo)致大量線程卡住的問(wèn)題，比較有代表性，撰文一篇，希望對(duì)廣大DBA們有幫助。
同事反饋說(shuō)在使用pt-online-schema-change添加索引的時(shí)候，一直卡住，而且會(huì)卡住很久。
登錄服務(wù)器，使用show full processlist命令查看線程狀態(tài)，發(fā)現(xiàn)大量的Waiting for table metadata lock，如下圖：
找到等待時(shí)間最長(zhǎng)Waiting for table metadata lock的SQL，正是同事添加索引的時(shí)候創(chuàng)建觸發(fā)器的SQL，如下圖：
初步確定原因是創(chuàng)建觸發(fā)器的時(shí)候，有某個(gè)事務(wù)持有metadata lock沒(méi)有釋放，導(dǎo)致創(chuàng)建觸發(fā)器的SQL被阻塞，進(jìn)而導(dǎo)致后面關(guān)于t_log_sp_detail_10的所有操作都被阻塞。
解決辦法，終止添加索引的操作，kill掉創(chuàng)建觸發(fā)器的線程，業(yè)務(wù)恢復(fù)。
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2
這里面引申出2個(gè)問(wèn)題：
1、是什么SQL持有metadata lock那么長(zhǎng)的時(shí)間一直不釋放？
2、為什么metadata lock那么長(zhǎng)的時(shí)間都不超時(shí)？
下面就來(lái)尋根問(wèn)底：
我們的環(huán)境是5.7，在MySQL 5.7中，可以通過(guò)查看performance_schema.metadata_locks來(lái)查看對(duì)應(yīng)metadata lock的情況，開(kāi)啟方式如下：
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';
不過(guò)在performance_schema.metadata_locks表中并沒(méi)有記錄對(duì)應(yīng)的線程ID，需要結(jié)合performance_schema.INNODB_TRX來(lái)進(jìn)一步分析。
但是，那臺(tái)機(jī)器關(guān)閉了performance_schema，如果要啟用的話必須重啟實(shí)例，因此只能使用笨一點(diǎn)的辦法。
只要抓取對(duì)應(yīng)的信息就能快速定位，這些信息包括：
（1）、開(kāi)啟general日志（短暫開(kāi)啟）
（2）、抓取show full processlist的信息（每隔1秒）
（3）、抓取performance_schema.INNODB_TRX的信息（每隔1秒）
通知同事，繼續(xù)使用pt添加索引，重現(xiàn)問(wèn)題。
問(wèn)題重現(xiàn)后，查看抓取到的performance_schema.INNODB_TRX信息，找到對(duì)應(yīng)事件正在進(jìn)行的事務(wù)，截圖如下：
查看general日志對(duì)應(yīng)線程做了什么事情：
查看抓取到proocesslist信息，發(fā)現(xiàn)這個(gè)線程是sleep的狀態(tài)，問(wèn)題很明顯是這個(gè)線程沒(méi)有提交導(dǎo)致。
和業(yè)務(wù)溝通后，kill掉該線程，索引正常添加。
第1個(gè)問(wèn)題答案是select線程沒(méi)有提交導(dǎo)致。
再來(lái)看第2個(gè)問(wèn)題
之所以等待那么久都沒(méi)有超時(shí)，是因?yàn)閙etadata lock不是InnoDB引擎層的鎖，而是server層的鎖，控制鎖超時(shí)的參數(shù)不一樣，如下：
上面的截圖中innodb_lock_wait_timeout是控制InnoDB引擎層的鎖超時(shí)，而lock_wait_timeout是控制server層的鎖超時(shí)。
而上圖中，lock_wait_timeout設(shè)置得很大，可以調(diào)小。
????????????????????????????????????????????????????3
回答完上面的2個(gè)問(wèn)題，我們?cè)賮?lái)深入一點(diǎn)了解一下metadata lock這個(gè)東東。主要從3個(gè)方面來(lái)看：
一、為什么要引入metadata lock
? ? 為了在并發(fā)環(huán)境下維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)（顯式或隱式）的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫(xiě)入操作。因此從MySQL5.5版本開(kāi)始引入了MDL鎖（metadata lock），來(lái)保護(hù)表的元數(shù)據(jù)信息，用于解決或者保證DDL操作與DML操作之間的一致性。
? ? 上面的描述可能不是特別好理解，來(lái)舉一個(gè)之前姜老師說(shuō)過(guò)的例子，這個(gè)例子就能很形象地說(shuō)明為什么要引入metadata lock。看如下兩個(gè)session
上面的例子中，如果沒(méi)有metadata lock的保護(hù)，會(huì)導(dǎo)致SESSION 2順利執(zhí)行，SESSION 1出錯(cuò)。
在5.5.3版本加入metadata lock以后，由于SESSION 1先查詢了TABLE1，持有TABLE1的metadata lock，會(huì)導(dǎo)致SESSION 2等待，直到SESSION 1提交。
正因?yàn)槿绱耍琈ySQL在5.5.3版本后引入了Metadata lock鎖，事務(wù)釋放后才會(huì)釋放Metadata lock，因此，在事務(wù)完成之前，DDL是無(wú)法執(zhí)行的。
有興趣的還可以看看這個(gè)典型的bug。
二、什么場(chǎng)景下會(huì)引起metadata lock
有如下幾種場(chǎng)景會(huì)引起metadata lock鎖等待問(wèn)題
1、有長(zhǎng)時(shí)間運(yùn)行的DML語(yǔ)句
? ? ? 這種情況會(huì)導(dǎo)致metadata lock等待，在工作中如果要對(duì)某個(gè)表進(jìn)行DDL操作之前，需要先用show processlist看看是否有長(zhǎng)時(shí)間運(yùn)行的SQL，防止出現(xiàn)線上故障。
2、有未提交的事務(wù)
? ? ? 這個(gè)就是我們開(kāi)篇遇到的問(wèn)題，不管是select還是update，如果沒(méi)有提交都會(huì)造成metadata lock等待。
3、在執(zhí)行期間失敗的語(yǔ)句，不會(huì)立即釋放metadata lock
? ? 官網(wǎng)是這么說(shuō)的：
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.
? ? 舉個(gè)例子就很容易理解了，比如我開(kāi)啟一個(gè)事務(wù)，查詢一個(gè)不存在的事務(wù)，索然語(yǔ)句執(zhí)行失敗，但是持有的metadata lock并沒(méi)有釋放：
session1執(zhí)行如下操作：
session 2執(zhí)行添加字段的DDL，session2就一直在等待中，直到session1提交
三、如何快速解決metadata lock引發(fā)的鎖等待問(wèn)題
從上面原理和場(chǎng)景中，我們可以看到基本就兩種情況。
一種是有長(zhǎng)時(shí)間運(yùn)行的DML語(yǔ)句的時(shí)候，這種情況要么kill掉DDL語(yǔ)句，要么就kill掉長(zhǎng)時(shí)間運(yùn)行的DML。
另外一種情況是未提交的事務(wù)（正常的語(yǔ)句和失敗的語(yǔ)句），這種情況要解決稍微復(fù)雜一點(diǎn)，需要找到對(duì)應(yīng)沒(méi)有提交的線程進(jìn)行kill，或者kill掉DDL語(yǔ)句。最快速的解決辦法就是將所有sleep的線程都?xì)⒌簟?/p>
備注：將所有sleep的線程都?xì)⒌暨@個(gè)操作會(huì)導(dǎo)致沒(méi)有提交的事務(wù)回滾，是有風(fēng)險(xiǎn)的，請(qǐng)根據(jù)業(yè)務(wù)場(chǎng)景進(jìn)行操作。
延展閱讀：https://www.cnblogs.com/zengkefu/p/5690385.html 【姜老師的文章】
四、參考資料：
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
