Mysql中USE DB堵塞故障分析

共计 8865 个字符，预计需要花费 23 分钟才能阅读完成。

这篇文章主要讲解了“Mysql 中 USE DB 堵塞故障分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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 类型表现如下：

兼容性：

 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 | + + + + + + + + |

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

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

方式 2：

*************************** 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

这里比较遗憾在 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] 是不会被堵塞的。

4、关于 SELECT * FROM A Waiting for table metadata lock 的分析
也许大家认为 SELECT 不会上锁，但是那是在 innodb 层次，在 MYSQL 层会上 MDL_SHARED_READ(SR) 如下：

方式 1：

 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

方式 2：

 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

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

其兼容性如下：

 Request | Granted requests for lock |
 type | S SH SR SW SWLP SU SRO SNW SNRW X |
 ----------+---------------------------------------------+
 SR | + + + + + + + + - - |

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

5、关于 DROP TABLE A Waiting for table metadata lock 的分析
这一点很好分析因为 A 表上了 X 锁而 DROP TABLE A 必然上 MDL_EXCLUSIVE(X)锁它当然和 MDL_EXCLUSIVE(X)不兼容。如下：

方式 1：

 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

方式 2：

 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

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

6、为何 use db 也会堵塞？
如果使用 mysql 客户端不使用 - A 选项 (或者 no-auto-rehash) 在 USE DB 的时候至少要做如下事情：

1、对 db 下每个表上 MDL (SH) lock 如下（调用 MDL_context::acquire_lock 这里给出堵塞时候的信息）：
方式 1：

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

方式 2：

*************************** 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

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

2、对每个表加入到 table cache，并且打开表(调用 open_table_from_share())
那么这种情况就和 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 隔离级别

使用脚本：

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
(请重新连接让参数生效)

感谢各位的阅读，以上就是“Mysql 中 USE DB 堵塞故障分析”的内容了，经过本文的学习后，相信大家对 Mysql 中 USE DB 堵塞故障分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！