溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“Mysql中USE DB堵塞故障分析”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Mysql中USE DB堵塞故障分析”吧！
歸納一下語句類型如下：
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è)綜合案列，并且我們要對schema.processlist的STATE比較敏感才行。
本節(jié)關(guān)于MDL LOCK的驗(yàn)證使用下面兩種方式：
方式1 筆者在MDL LOCK源碼加鎖函數(shù)處加日志輸出，但是如果要分析各種語句加MDL LOCK的類型還只能用這種方式，因?yàn)镸DL LOCK加鎖往往一閃而過，performance_schema.metadata_locks 沒有辦法觀察到
方式2 處于堵塞情況下performance_schema.metadata_locks的輸出
打開如下：
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';
select * from performance_schema.metadata_locks\G
1、關(guān)于CREATE TABLE A AS SELECT B 對B表sending data的分析
關(guān)于sending data這個(gè)狀態(tài)其實(shí)可以代表很多含義，從我現(xiàn)有的對的了解，這是MYSQL上層對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是一致的參考不在熬述：
http://blog.itpub.net/7728585/viewspace-2146183/
從他反應(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)下。
2、關(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ì)對本表上MDL_SHARED_HIGH_PRIO(SH)。
方式1：
方式2：
兩種方式都能觀察到MDL_SHARED_HIGH_PRIO(SH)的存在并且我模擬的是處于堵塞情況下的。
兼容性：
阻塞隊(duì)列優(yōu)先級：
其被堵塞的條件除了被MDL_EXCLUSIVE(X)堵塞沒有其他的可能。那么這就是一個(gè)非常重要的突破口。
這一點(diǎn)也是我以前不知道的,也是本案列中花時(shí)間最多的地方,前文已經(jīng)分析過要讓SHOW TABLE STATUS[like 'A']這種只會(huì)上MDL_SHARED_HIGH_PRIO(SH) MDL LOCK的語句堵塞在MDL LOCK上只有一種可能那就是A表上了MDL_EXCLUSIVE(X)。那么我開始
方式1：
方式2：
這里比較遺憾在performance_schema.metadata_locks中并沒有顯示出MDL_EXCLUSIVE(X)，而顯示為MDL_SHARED(S) 但是我們在我輸出的日志中可以看到這里做了升級操作將MDL_SHARED(S) 升級為了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表來看，只有MDL_EXCLUSIVE(X)會(huì)堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我們應(yīng)該能夠確認(rèn)這里確實(shí)做了升級操作，否則SHOW TABLE STATUS[like 'A'] 是不會(huì)被堵塞的。
也許大家認(rèn)為SELECT不會(huì)上鎖，但是那是在innodb 層次，在MYSQL層會(huì)上MDL_SHARED_READ(SR) 如下：
方式1：
方式2：
可以看到確實(shí)有MDL_SHARED_READ(SR)的存在，當(dāng)前處于堵塞狀態(tài)
其兼容性如下：
顯然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。
這一點(diǎn)很好分析因?yàn)锳表上了X鎖而DROP TABLE A必然上MDL_EXCLUSIVE(X)鎖它當(dāng)然和MDL_EXCLUSIVE(X)不兼容。如下：
方式1：
方式2：
其中EXCLUSIVE就是我們說的MDL_EXCLUSIVE(X)它確實(shí)存在當(dāng)前處于堵塞
如果使用mysql客戶端不使用-A選項(xiàng)(或者 no-auto-rehash)在USE DB的時(shí)候至少要做如下事情：
方式1：
方式2：
可以看到USE DB確實(shí)也因?yàn)镸DL_SHARED_HIGH_PRIO(SH) 發(fā)生了堵塞。
那么這種情況就和SHOW TABLE STATUS[like 'A']被堵塞的情況一模一樣了，也是由于MDL 鎖不兼容造成的。
有了前面的分析那么我們可以梳理這個(gè)故障發(fā)生的原因如下：
1、有一個(gè)在B表上長期未提交的DML
2、由步驟1引起了CREATE TABLE A AS SELECT B的堵塞
3、由步驟2引起了其他語句的堵塞
測試環(huán)境：
5.7.14
GITD關(guān)閉
RR隔離級別
使用腳本： mysql> SHOW TABLE STATUS like 'a' \G
2017-11-10T03:01:48.142334Z 6 [Note] (acquire_lock)**THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!**
2017-11-10T03:01:48.142381Z 6 [Note] (>MDL PRINT) Thread id is 6:
2017-11-10T03:01:48.142396Z 6 [Note] (->MDL PRINT) DB_name is:test
2017-11-10T03:01:48.142409Z 6 [Note] (-->MDL PRINT) OBJ_name is:a
2017-11-10T03:01:48.142421Z 6 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-10T03:01:48.142434Z 6 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH)
2017-11-10T03:01:48.142447Z 6 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
*************************** 7. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864665152
LOCK_TYPE: SHARED_HIGH_PRIO
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_base.cc:2821
OWNER_THREAD_ID: 38
OWNER_EVENT_ID: 1695
但是MDL_SHARED_HIGH_PRIO(SH) 是一個(gè)優(yōu)先級非常高的一個(gè)MDL LOCK類型表現(xiàn)如下： Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SH | + + + + + + + + + - |
Request | Pending requests for lock |
type | S SH SR SW SU SNW SNRW X |
----------+---------------------------------+
SH | + + + + + + + + |
懷疑這個(gè)DDL語句在語句結(jié)束之前會(huì)對A表上MDL_EXCLUSIVE(X) ，然后進(jìn)行實(shí)際測試不出所料確實(shí)是這樣的如下： 2017-11-10T05:38:16.824713Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-11-10T05:38:16.824727Z 4 [Note] (>MDL PRINT) Thread id is 4:
2017-11-10T05:38:16.824739Z 4 [Note] (->MDL PRINT) DB_name is:test
2017-11-10T05:38:16.824752Z 4 [Note] (-->MDL PRINT) OBJ_name is:a
2017-11-10T05:38:16.824764Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-10T05:38:16.824776Z 4 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-11-10T05:38:16.824788Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S)
2017-11-10T05:38:16.824799Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-11-10T05:38:16.825286Z 4 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-11-10T05:38:16.825312Z 4 [Note] (>MDL PRINT) Thread id is 4:
2017-11-10T05:38:16.825332Z 4 [Note] (->MDL PRINT) DB_name is:test
2017-11-10T05:38:16.825345Z 4 [Note] (-->MDL PRINT) OBJ_name is:a
2017-11-10T05:38:16.825357Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-10T05:38:16.825369Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X)
2017-11-10T05:38:16.825381Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733998842016
LOCK_TYPE: SHARED
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6314
OWNER_THREAD_ID: 36
OWNER_EVENT_ID: 1553
select * from a;
2017-11-10T03:31:31.209772Z 6 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-11-10T03:31:31.209824Z 6 [Note] (>MDL PRINT) Thread id is 6:
2017-11-10T03:31:31.209851Z 6 [Note] (->MDL PRINT) DB_name is:test
2017-11-10T03:31:31.209870Z 6 [Note] (-->MDL PRINT) OBJ_name is:a
2017-11-10T03:31:31.209885Z 6 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-10T03:31:31.209965Z 6 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ(SR)
2017-11-10T03:31:31.209985Z 6 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864625136
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:6314
OWNER_THREAD_ID: 38
OWNER_EVENT_ID: 1764
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SR | + + + + + + + + - - |
drop table a;
2017-11-09T10:58:28.673015Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-11-09T10:58:28.673030Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-11-09T10:58:28.673042Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-11-09T10:58:28.673054Z 3 [Note] (-->MDL PRINT) OBJ_name is:t10
2017-11-09T10:58:28.673067Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-09T10:58:28.673094Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X)
2017-11-09T10:58:28.673109Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864625472
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:6314
OWNER_THREAD_ID: 38
OWNER_EVENT_ID: 1832
use test
2017-11-10T03:46:50.223628Z 5 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-11-10T03:46:50.223666Z 5 [Note] (>MDL PRINT) Thread id is 5:
2017-11-10T03:46:50.223696Z 5 [Note] (->MDL PRINT) DB_name is:test
2017-11-10T03:46:50.223714Z 5 [Note] (-->MDL PRINT) OBJ_name is:a
2017-11-10T03:46:50.223725Z 5 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-11-10T03:46:50.223735Z 5 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH)
2017-11-10T03:46:50.223755Z 5 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
*************************** 7. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733797429008
LOCK_TYPE: SHARED_HIGH_PRIO
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_base.cc:2821
OWNER_THREAD_ID: 37
OWNER_EVENT_ID: 187
三、分析梳理
語句會(huì)在innodb層對B表某些數(shù)據(jù)加innodb row lock。
因?yàn)镽R模式下SELECT B必然對B表上滿足的數(shù)據(jù)上鎖，因?yàn)椴襟E1已經(jīng)加鎖所以觸發(fā)等待，STATE為sending data。
因?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。
四、模擬測試
create table b (id int);
insert into b values(1);
set global innodb_lock_wait_timeout=1000;
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';
select * from performance_schema.metadata_locks\G
(請重新連接讓參數(shù)生效)
感謝各位的閱讀，以上就是“Mysql中USE DB堵塞故障分析”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Mysql中USE DB堵塞故障分析這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
