遇到故障，我們往往想的是如何解決這個(gè)故障，而不是從故障的根本去思考出現(xiàn)這個(gè)故障的原因？這樣的結(jié)果，只能使我們得到了魚，失去了漁。今天，我們就來分享一個(gè)由USE DB堵塞故障引發(fā)的思考案例。
故障描述
今天一個(gè)朋友遇到數(shù)據(jù)庫遇到一個(gè)嚴(yán)重的故障，故障環(huán)境如下：
MYSQL 5.6.16
RR隔離級(jí)別
GITD關(guān)閉
表現(xiàn)如下：
use db不能進(jìn)入數(shù)據(jù)庫
show table status不能查詢到表信息
schema.processlist來看有大量的 Waiting for table metadata lock
情急之下他殺掉了一大堆線程后發(fā)現(xiàn)還是不能恢復(fù)，最后殺掉了一個(gè)沒有及時(shí)提交的事物才恢復(fù)正常。也僅僅留下了如下圖的一個(gè)截圖：
故障信息提取
還是回到上圖，我們可以歸納一下語句類型如下：
1、CREATE TABLE A AS SELECT B
其STATE為 sending data
2、DROP TABLE A
其STATE為 Waiting for table metadata lock
3、SELECT * FROM A
其STATE為 Waiting for table metadata lock
4、 SHOW TABLE STATUS[like 'A']
其STATE為 Waiting for table metadata lock
信息分析
要分析出這個(gè)案列其實(shí)不太容易因?yàn)樗荕YSQL層MDL LOCK和RR模式innodb row lock的一個(gè)綜合案列，并且我們要對(duì)schema.processlist的STATE比較敏感才行。
建議先閱讀我的如下文章來學(xué)習(xí)MDL LOCK：
https://www.jb51.net/article/131383.htm
本節(jié)關(guān)于MDL LOCK的驗(yàn)證使用下面兩種方式：
方式一：筆者在MDL LOCK源碼加鎖函數(shù)處加日志輸出，如果要分析各種語句加MDL LOCK的類型還只能用這種方式，因?yàn)镸DL LOCK加鎖往往一閃而過，performance_schema.metadata_locks 沒有辦法觀察到。
方式二：處于堵塞情況下使用5.7版本的performance_schema.metadata_locks觀察。
在P_S中打開mdl監(jiān)測方法如下：
一、關(guān)于CREATE TABLE A AS SELECT B 對(duì)B表sending data的分析
關(guān)于sending data這個(gè)狀態(tài)其實(shí)可以代表很多含義，從我現(xiàn)有的對(duì)的了解，這是MYSQL上層對(duì)SELECT類型語句的這類語句在INNODB層和MYSQL層進(jìn)行數(shù)據(jù)交互的時(shí)候一個(gè)統(tǒng)稱，所以出現(xiàn)它的可能包含:
確實(shí)需要訪問數(shù)據(jù)量特別大，可能需要優(yōu)化。
由于INNODB 層的獲取row lock需要等待，比如我們常見的SELECT FOR UPDATE。
同時(shí)我們還需要注意在RR模式下SELECT B這一部分加鎖方式和INSERT...SELECT是一致的參考不再贅述：
從他反應(yīng)的情況因?yàn)樗谧詈髿⒌袅艘粋€(gè)長期的未提交的事物所以他因?yàn)槭乔闆r2。并且整個(gè)CREATE TABLE A AS SELECT B語句由于B表上某些數(shù)據(jù)庫被上了鎖而不能獲取，導(dǎo)致整個(gè)語句處于sending data狀態(tài)下。
二、關(guān)于SHOW TABLE STATUS[like 'A'] Waiting for table metadata lock的分析
這是本案例中最重要的一環(huán)，SHOW TABLE STATUS[like 'A']居然被堵塞其STATE為Waiting for table metadata lock并且注意這里是table因?yàn)镸DL LOCK類型分為很多。我在MDL介紹的那篇文章中提到了desc 一個(gè)表的時(shí)候會(huì)上MDL_SHARED_HIGH_PRIO(SH)，其實(shí)在SHOW TABLE STATUS的時(shí)候也會(huì)對(duì)本表上MDL_SHARED_HIGH_PRIO(SH)。
方式一
方式二
兩種方式都能觀察到MDL_SHARED_HIGH_PRIO(SH)的存在并且我模擬的是處于堵塞情況下的。
但是MDL_SHARED_HIGH_PRIO(SH) 是一個(gè)優(yōu)先級(jí)非常高的一個(gè)MDL LOCK類型表現(xiàn)如下：
兼容性：
阻塞隊(duì)列優(yōu)先級(jí)：
其被堵塞的條件除了被MDL_EXCLUSIVE(X)堵塞沒有其他的可能。那么這就是一個(gè)非常重要的突破口。
三、關(guān)于CREATE TABLE A AS SELECT B 對(duì)A表的加MDL LOCK的分析
這一點(diǎn)也是我以前不知道的,也是本案列中花時(shí)間最多的地方,前文已經(jīng)分析過要讓SHOW TABLE STATUS[like 'A']這種只會(huì)上MDL_SHARED_HIGH_PRIO(SH) MDL LOCK的語句堵塞在MDL LOCK上只有一種可能那就是A表上了MDL_EXCLUSIVE(X)。
那么我開始懷疑這個(gè)DDL語句在語句結(jié)束之前會(huì)對(duì)A表上MDL_EXCLUSIVE(X) ，然后進(jìn)行實(shí)際測試不出所料確實(shí)是這樣的如下：
方式一
方式二
這里比較遺憾在performance_schema.metadata_locks中并沒有顯示出MDL_EXCLUSIVE(X)，而顯示為MDL_SHARED(S)是我們?cè)谖逸敵龅娜罩局锌梢钥吹竭@里做了升級(jí)操作將MDL_SHARED(S) 升級(jí)為了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表來看，只有MDL_EXCLUSIVE(X)會(huì)堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我們應(yīng)該能夠確認(rèn)這里確實(shí)做了升級(jí)操作，否則SHOW TABLE STATUS[like 'A'] 是不會(huì)被堵塞的。
四、關(guān)于SELECT * FROM A Waiting for table metadata lock的分析
也許大家認(rèn)為SELECT不會(huì)上鎖，但是那是在innodb 層次，在MYSQL層會(huì)上MDL_SHARED_READ(SR) 如下：
方式一
方式二
可以看到確實(shí)有MDL_SHARED_READ(SR)的存在，當(dāng)前處于堵塞狀態(tài)
其兼容性如下：
顯然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。
五、關(guān)于DROP TABLE A Waiting for table metadata lock的分析
這一點(diǎn)很好分析因?yàn)锳表上了X鎖而DROP TABLE A必然上MDL_EXCLUSIVE(X)鎖它當(dāng)然和MDL_EXCLUSIVE(X)不兼容。如下：
方式一
方式二
其中EXCLUSIVE就是我們說的MDL_EXCLUSIVE(X)它確實(shí)存在當(dāng)前處于堵塞
六、為何use db也會(huì)堵塞？
如果使用mysql客戶端不使用-A選項(xiàng)(或者 no-auto-rehash)在USE DB的時(shí)候至少要做如下事情：
1、 對(duì)db下每個(gè)表上MDL (SH) lock如下（調(diào)用MDL_context::acquire_lock 這里給出堵塞時(shí)候的信息）
方式一
方式二
可以看到USE DB確實(shí)也因?yàn)镸DL_SHARED_HIGH_PRIO(SH) 發(fā)生了堵塞。
2、對(duì)每個(gè)表加入到table cache，并且打開表(調(diào)用open_table_from_share())
那么這種情況就和SHOW TABLE STATUS[like 'A']被堵塞的情況一模一樣了，也是由于MDL 鎖不兼容造成的。
分析梳理
有了前面的分析那么我們可以梳理這個(gè)故障發(fā)生的原因如下：
有一個(gè)在B表上長期未提交的DML
語句會(huì)在innodb層對(duì)B表某些數(shù)據(jù)加innodb row lock。
由步驟1引起了CREATE TABLE A AS SELECT B的堵塞
因?yàn)镽R模式下SELECT B必然對(duì)B表上滿足的數(shù)據(jù)上鎖，因?yàn)椴襟E1已經(jīng)加鎖所以觸發(fā)等待，STATE為sending data。
由步驟2引起了其他語句的堵塞
因?yàn)镃RATE TABLE A AS SELECT B在A表建立完成之前會(huì)上MDL_EXCLUSIVE(X),這把鎖會(huì)堵塞其他全部的關(guān)于A表的語句，包括DESC/SHOW TABLE STATUS/USE DB(非-A) 這種只上MDL_SHARED_HIGH_PRIO(SH)MDL LOCK 的語句。STATE統(tǒng)一為Waiting for table metadata lock。
模擬測試
測試環(huán)境：
5.7.14
GITD關(guān)閉
RR隔離級(jí)別
使用腳本：
步驟如下：
session1
|
session2
|
session3
|
session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(由于b表innodb row lock堵塞)------------show table status like 'a';(由于a表MDL LOCK堵塞)------------use test(由于a表MDL LOCK堵塞)
最后我們看到的等待狀態(tài)如下：
這樣我們就完美的模擬出線上的狀態(tài)，如果我們殺掉session1中的事物，自然就全部解鎖了，讓我們?cè)賮砜匆幌聀erformance_schema.metadata_locks中的輸出：
我們可以看到如上的輸出，但是需要注意LOCK_TYPE: SHARED它不可能堵塞LOCK_TYPE: SHARED_HIGH_PRIO(可以參考附錄或者我以前寫的MDL LOCK分析的文章)如上文分析這里實(shí)際上是做了升級(jí)操作升級(jí)為了MDL_EXCLUSIVE(X)。
總結(jié)
RC模式下雖然CREATE TABLE A SELECT B中B表不會(huì)上任何INNODB ROW LOCK但是如果B表非常大那么A表也會(huì)處于MDL_EXCLUSIVE(X)保護(hù)下，因此也會(huì)觸發(fā)USE DB\SHOW TABLE STATUS等待的情況。
如果打開GTID不能使用CREATE TABLE A SELECT B這樣的語句。
對(duì)于DML/DDL混用的系統(tǒng)一定要注意并發(fā)，就像本例中如果注意到高并發(fā)下的情況可以想辦法避免。
這個(gè)案列再次說明了長期不提交的事物可能引發(fā)悲劇，所以建議監(jiān)控超過N秒沒結(jié)束的事務(wù)。
附錄
MDL LOCK TYPE
兼容性矩陣
等待隊(duì)列優(yōu)先級(jí)矩陣
