Mysql中USE DB堵塞故障分析

2025-06-24 07:59来源：互联网 [ 大中小 ]

一、故障信息提取

归纳一下语句类型如下：

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>SHOWTABLESTATUSlike'a'\G2017-11-10T03:01:48.142334Z6[Note](acquire_lock)**THISMDLLOCKacquireWAIT(MDL_LOCKWAITQUE)!**2017-11-10T03:01:48.142381Z6[Note](>MDLPRINT)Threadidis6:2017-11-10T03:01:48.142396Z6[Note](->MDLPRINT)DB_nameis:test2017-11-10T03:01:48.142409Z6[Note](-->MDLPRINT)OBJ_nameis:a2017-11-10T03:01:48.142421Z6[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-10T03:01:48.142434Z6[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_HIGH_PRIO(SH)2017-11-10T03:01:48.142447Z6[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

方式2：

***************************7.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:aOBJECT_INSTANCE_BEGIN:140733864665152LOCK_TYPE:SHARED_HIGH_PRIOLOCK_DURATION:TRANSACTIONLOCK_STATUS:PENDINGSOURCE:sql_base.cc:2821OWNER_THREAD_ID:38OWNER_EVENT_ID:1695

两种方式都能观察到MDL_SHARED_HIGH_PRIO(SH)的存在并且我模拟的是处于堵塞情况下的。
但是MDL_SHARED_HIGH_PRIO(SH) 是一个优先级非常高的一个MDL LOCK类型表现如下：

兼容性：

Request|Grantedrequestsforlock|type|SSHSRSWSWLPSUSROSNWSNRWX|----------+---------------------------------------------+SH|+++++++++-|

阻塞队列优先级：

Request|Pendingrequestsforlock|type|SSHSRSWSUSNWSNRWX|----------+---------------------------------+SH|++++++++|

其被堵塞的条件除了被MDL_EXCLUSIVE(X)堵塞没有其他的可能。那么这就是一个非常重要的突破口。

这一点也是我以前不知道的,也是本案列中花时间最多的地方,前文已经分析过要让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.824713Z4[Note](acquire_lock)THISMDLLOCKacquireok!2017-11-10T05:38:16.824727Z4[Note](>MDLPRINT)Threadidis4:2017-11-10T05:38:16.824739Z4[Note](->MDLPRINT)DB_nameis:test2017-11-10T05:38:16.824752Z4[Note](-->MDLPRINT)OBJ_nameis:a2017-11-10T05:38:16.824764Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-10T05:38:16.824776Z4[Note](---->MDLPRINT)Fastpathis:(Y)2017-11-10T05:38:16.824788Z4[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2017-11-10T05:38:16.824799Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-11-10T05:38:16.825286Z4[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO2017-11-10T05:38:16.825312Z4[Note](>MDLPRINT)Threadidis4:2017-11-10T05:38:16.825332Z4[Note](->MDLPRINT)DB_nameis:test2017-11-10T05:38:16.825345Z4[Note](-->MDLPRINT)OBJ_nameis:a2017-11-10T05:38:16.825357Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-10T05:38:16.825369Z4[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)2017-11-10T05:38:16.825381Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

方式2：

***************************1.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:aOBJECT_INSTANCE_BEGIN:140733998842016LOCK_TYPE:SHAREDLOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:sql_parse.cc:6314OWNER_THREAD_ID:36OWNER_EVENT_ID:1553

这里比较遗憾在performance_schema.metadata_locks中并没有显示出MDL_EXCLUSIVE(X)，而显示为MDL_SHARED(S) 但是我们在我输出的日志中可以看到这里做了升级操作将MDL_SHARED(S) 升级为了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表来看，只有MDL_EXCLUSIVE(X)会堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我们应该能够确认这里确实做了升级操作，否则SHOW TABLE STATUS[like 'A'] 是不会被堵塞的。

也许大家认为SELECT不会上锁，但是那是在innodb 层次，在MYSQL层会上MDL_SHARED_READ(SR) 如下：

方式1：

select*froma;2017-11-10T03:31:31.209772Z6[Note](acquire_lock)THISMDLLOCKacquireWAIT(MDL_LOCKWAITQUE)!2017-11-10T03:31:31.209824Z6[Note](>MDLPRINT)Threadidis6:2017-11-10T03:31:31.209851Z6[Note](->MDLPRINT)DB_nameis:test2017-11-10T03:31:31.209870Z6[Note](-->MDLPRINT)OBJ_nameis:a2017-11-10T03:31:31.209885Z6[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-10T03:31:31.209965Z6[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ(SR)2017-11-10T03:31:31.209985Z6[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

方式2：

OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:aOBJECT_INSTANCE_BEGIN:140733864625136LOCK_TYPE:SHARED_READLOCK_DURATION:TRANSACTIONLOCK_STATUS:PENDINGSOURCE:sql_parse.cc:6314OWNER_THREAD_ID:38OWNER_EVENT_ID:1764

可以看到确实有MDL_SHARED_READ(SR)的存在，当前处于堵塞状态

其兼容性如下：

Request|Grantedrequestsforlock|type|SSHSRSWSWLPSUSROSNWSNRWX|----------+---------------------------------------------+SR|++++++++--|

显然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。

这一点很好分析因为A表上了X锁而DROP TABLE A必然上MDL_EXCLUSIVE(X)锁它当然和MDL_EXCLUSIVE(X)不兼容。如下：

方式1：

droptablea;2017-11-09T10:58:28.673015Z3[Note](acquire_lock)THISMDLLOCKacquireok!2017-11-09T10:58:28.673030Z3[Note](>MDLPRINT)Threadidis3:2017-11-09T10:58:28.673042Z3[Note](->MDLPRINT)DB_nameis:test2017-11-09T10:58:28.673054Z3[Note](-->MDLPRINT)OBJ_nameis:t102017-11-09T10:58:28.673067Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-09T10:58:28.673094Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)2017-11-09T10:58:28.673109Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

方式2：

OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:aOBJECT_INSTANCE_BEGIN:140733864625472LOCK_TYPE:EXCLUSIVELOCK_DURATION:TRANSACTIONLOCK_STATUS:PENDINGSOURCE:sql_parse.cc:6314OWNER_THREAD_ID:38OWNER_EVENT_ID:1832

其中EXCLUSIVE就是我们说的MDL_EXCLUSIVE(X)它确实存在当前处于堵塞

如果使用mysql客户端不使用-A选项(或者 no-auto-rehash)在USE DB的时候至少要做如下事情：

方式1：

usetest2017-11-10T03:46:50.223628Z5[Note](acquire_lock)THISMDLLOCKacquireWAIT(MDL_LOCKWAITQUE)!2017-11-10T03:46:50.223666Z5[Note](>MDLPRINT)Threadidis5:2017-11-10T03:46:50.223696Z5[Note](->MDLPRINT)DB_nameis:test2017-11-10T03:46:50.223714Z5[Note](-->MDLPRINT)OBJ_nameis:a2017-11-10T03:46:50.223725Z5[Note](--->MDLPRINT)Namespaceis:TABLE2017-11-10T03:46:50.223735Z5[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_HIGH_PRIO(SH)2017-11-10T03:46:50.223755Z5[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

方式2：

***************************7.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:aOBJECT_INSTANCE_BEGIN:140733797429008LOCK_TYPE:SHARED_HIGH_PRIOLOCK_DURATION:TRANSACTIONLOCK_STATUS:PENDINGSOURCE:sql_base.cc:2821OWNER_THREAD_ID:37OWNER_EVENT_ID:187

可以看到USE DB确实也因为MDL_SHARED_HIGH_PRIO(SH) 发生了堵塞。

那么这种情况就和SHOW TABLE STATUS[like 'A']被堵塞的情况一模一样了，也是由于MDL 锁不兼容造成的。

有了前面的分析那么我们可以梳理这个故障发生的原因如下：

1、有一个在B表上长期未提交的DML
语句会在innodb层对B表某些数据加innodb row lock。

2、由步骤1引起了CREATE TABLE A AS SELECT B的堵塞
因为RR模式下SELECT B必然对B表上满足的数据上锁，因为步骤1已经加锁所以触发等待，STATE为sending data。

3、由步骤2引起了其他语句的堵塞
因为CRATE TABLE A AS SELECT B在A表建立完成之前会上MDL_EXCLUSIVE(X),这把锁会堵塞其他全部的关于A表的语句，包括DESC/SHOW TABLE STATUS/USE DB(非-A) 这种只上MDL_SHARED_HIGH_PRIO(SH)MDL LOCK 的语句。STATE统一为Waiting for table metadata lock。

测试环境：

5.7.14

GITD关闭

RR隔离级别

使用脚本：

createtableb(idint);insertintobvalues(1);setglobalinnodb_lock_wait_timeout=1000;UPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAME='global_instrumentation';UPDATEperformance_schema.setup_instrumentsSETENABLED='YES'WHERENAME='wait/lock/metadata/sql/mdl';select*fromperformance_schema.metadata_locks\G(请重新连接让参数生效)