Waiting for table metadata lock

问题描述

新建表或修改表时，Navicat操作超时，命令行操作一直等待
use xxdatabase 时卡死，提示连接数据库时加上 -A 参数，提示如下

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

解释如下：选择数据库时如果不加 -A 就要预读数据库信息，当使用-A参数时，就不预读数据库信息。由于数据库中有死锁，导致部分数据表无法打开，所以 use 命令卡死，后面会说明。
试了一下同一服务器上的其他数据库，操作正常，初步怀疑是死锁导致的，于是 执行命令 show processlist 结果如下

发现大量的 Waiting for table metadata lock 信息，有很多 alter table 和 create table 语句，都在等待。
除了第一条的 Opening tables 外，后面的几乎都是 Waiting；问题基本确定，kill 掉 Opening tables 进程，问题解决。出现的原因还不清楚

什么是metadata lock

简称 MDL，是为了保护一个处于事务中的表的结构不被修改。metadata 元数据，即表结构。
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
MDL是事务级别的，只有在事务结束后才会释放。
MDL起源一个bug：http://bugs.mysql.com/bug.php?id=989
大意如下：

Description:
If user1 has an active transaction on a table and then user2 drops this table, then user1 does COMMIT, then in the binlog we have something like:
DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;
which is wrong

当一个会话在主库执行DML操作还没提交时，另一个会话对同一个对象执行了DDL操作如drop table，而由于MySQL的binlog是基于事务提交的先后顺序进行记录的，因此在从库上应用时，就出现了先drop table，然后再向table中insert的情况，导致从库应用出错。
一句话：新增或修改数据时不允许修改表结构。

产生metadata lock的原因

从上面可以知道，在对数据表操作的事务未提交时，进行DDL操作会产生 MDL

MDL超时

metadata lock不是InnoDB引擎层的锁，而是server层的锁，控制锁超时的参数不一样，如下：

上面的截图中innodb_lock_wait_timeout是控制InnoDB引擎层的锁超时，而lock_wait_timeout是控制server层的锁超时，其默认值为31536000（365天）

参考

https://www.cnblogs.com/linga/p/9558201.html
https://www.cnblogs.com/digdeep/p/4892953.html
https://www.jianshu.com/p/e14c39e430bf
https://www.cnblogs.com/chenpingzhao/p/9642732.html