这篇文章主要讲解了“Mysql中USE DB堵塞故障分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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
二、信息分析
要分析出这个案列其实不太容易因为他是MYSQL层MDL LOCK和RR模式innodb row lock的一个综合案列，并且我们要对schema.processlist的STATE比较敏感才行。
本节关于MDL LOCK的验证使用下面两种方式：
方式1 笔者在MDL LOCK源码加锁函数处加日志输出，但是如果要分析各种语句加MDL LOCK的类型还只能用这种方式，因为MDL 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、关于CREATE TABLE A AS SELECT B 对B表sending data的分析
关于sending data这个状态其实可以代表很多含义，从我现有的对的了解，这是MYSQL上层对SELECT类型语句的这类语句在INNODB层和MYSQL层进行数据交互的时候一个统称，所以出现它的可能包含:
确实需要访问数据量特别大，可能需要优化
由于INNODB 层的获取row lock需要等待，比如我们常见的SELECT FOR UPDATE
同时我们还需要注意在RR模式下SELECT B这一部分加锁方式和INSERT…SELECT是一致的参考不在熬述：
http://blog.itpub.net/7728585/viewspace-2146183/
从他反应的情况因为他在最后杀掉了一个长期的未提交的事物所以他因为是情况2。并且整个CREATE TABLE A AS SELECT B语句由于B表上某些数据库被上了锁而不能获取，导致整个语句处于sending data状态下。
2、关于SHOW TABLE STATUS[like 'A'] Waiting for table metadata lock的分析
这是本案例中最重要的一环，SHOW TABLE STATUS[like 'A']居然被堵塞其STATE为Waiting for table metadata lock并且注意这里是table因为MDL LOCK类型分为很多。我在MDL介绍的那篇文章中提到了desc 一个表的时候会上MDL_SHARED_HIGH_PRIO(SH)，其实在SHOW TABLE STATUS的时候也会对本表上MDL_SHARED_HIGH_PRIO(SH)。
方式1：
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
方式2：
*************************** 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)的存在并且我模拟的是处于堵塞情况下的。
但是MDL_SHARED_HIGH_PRIO(SH) 是一个优先级非常高的一个MDL LOCK类型表现如下：
3、关于CREATE TABLE A AS SELECT B 对A表的加MDL LOCK的分析
这一点也是我以前不知道的,也是本案列中花时间最多的地方,前文已经分析过要让SHOW TABLE STATUS[like 'A']这种只会上MDL_SHARED_HIGH_PRIO(SH) MDL LOCK的语句堵塞在MDL LOCK上只有一种可能那就是A表上了MDL_EXCLUSIVE(X)。那么我开始
怀疑这个DDL语句在语句结束之前会对A表上MDL_EXCLUSIVE(X) ，然后进行实际测试不出所料确实是这样的如下：
方式1：
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
