Waiting for table metadata lock异常

前言

在5.6.23生产环境的时候，有一次遇到了一个报错：show processlist 中看到大量的Waiting for table metadata lock, 并且同时引起了应用端的大量请求阻塞，无法读写数据，因此对这一问题进行了分析研究，并记录于此，希望能帮助遇到同样问题的你；

结论

在分析具体场景之前，我先说一下结论：
mysql5.5版本后引入了MDL锁（meta data lock）机制，表级别的锁，系统会自动加持，其只分为读锁和写锁，在对表进行增删改查操作时会加上MDL读锁，而修改表结构则会加上MDL 写锁，写锁与读锁互斥；
直到事务提交前MDL锁才会被释放掉，哪怕是执行失败的事务，如果没有进行commit或rollback操作,则该事务仍然是会持有表的MDL锁，并且有可能这个执行失败的事务不会出现在information_schema.innodb_trx字典表中；
如果此时同时有表的DDL操作出现，则会因请求MDL写锁而被阻塞，而后续新的对该表的增删改查操作都会被阻塞，并处于“Waiting for table metadata lock”状态；
我们需要做的就是找到持有MDL锁的那个大事务，然后kill掉即可解决问题；

情况1

大事务在执行中，或者执行完，但是都没有显示commit或rollback
出现此种异常，一般都是由于大事务未提交，持续占有表的metadata lock读锁，从而阻塞DDL操作去获取表的metadata lock写锁，进一步又影响到了后续新进的select 语句去获取metadata lock读锁；

复现

1.关闭autocommit, 执行一个select（session1）

mysql>set autocommit=0;
mysql>begin;
mysql> select * from t2;        ---此时没有commit提交此事务，此时持有matedata lock读锁
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+

2.修改表的元数据(在另一个窗口session2执行)

mysql> rename table t2 to t3;        ---此时就会被阻塞，因为此SQL需要一把MDL写锁

3.阻塞
再开第三个窗口session3，就可以看到阻塞,，但是却看不到是哪条SQL阻塞了它

此时再对此表执行select 则会被持续阻塞（session4）

mysql> select * from t2;        ---此时select语句想要请求MDL读锁，但是被session2的MDL写锁阻塞了

4.查看运行中的事务
此时，通过information_schema.innodb_trx可以看到正在running中的事务id

当然，我们通过information_schema.processlist里的time时间以及信息也可以大致判断此session，只要将其kill掉，waiting for lock的异常就会解决

情况2

有时候是DDL操作被阻塞了，但是innodb_trx字典表中却看不到任何running中的事务
结论原因:
在某些事务的SQL执行失败后，在没有显示的执行rollback操作前，发起式还是会持有表的metadata lock而不释放的，但此时因为information_schema.innodb_trx字典表不记录执行失败的事务，所以此时我们如果稍微不注意就会陷入迷茫，纠结为什么running中的事务，但是却有锁等待；
所以此时我们通过查看performance_schema.events_statements_current来找到执行失败的SQL的Thread_id，再去select PROCESSLIST_ID from performance_schema.threads where thread_id=32114中获取到session id，确认后直接kill此session即可解决waiting for table metadata lock的异常问题；

实验复现：

1.在session1中select一个不存在的列

此时事务SQL报错，但是并没有释放MDL读锁

mysql>set autocommit=0;
mysql>begin;
mysql>select age from t2 where id=1;        ---此时事务没有自动rollback
ERROR 1054 (42S22): Unknown column 'age' in 'field list'

2.执行DDL操作

在session2 中执行rename的DDL操作，此时则会被被阻塞

3.查看阻塞

此时查看innodb_trx是空的，没有running中的事务

mysql> select * from information_schema.innodb_trx\G;
Empty set (0.00 sec)

说明：为什么此时innodb_trx没有正在运行的事务呢？因为innodb_trx不记录执行失败的事务，但是执行失败的事务在rollback前还是会持有表的metadata lock，(因为我们session1中没有显示的执行rollback操作),而DDL操作还是会被阻塞

此时我们可以查看performance_schema.events_statements_current来找到这条语句

mysql> select * from performance_schema.events_statements_current\G;

上图中国的Tread_id其实是事务id不是session id，可以通过如下字典吧查看到session id

mysql> select thread_id,name,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_DB,PROCESSLIST_COMMAND from performance_schema.threads where thread_id=32114;
+-----------+----------------------------------------+----------------+------------------+----------------+---------------------+
| thread_id | name                                   | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+-----------+----------------------------------------+----------------+------------------+----------------+---------------------+
|     32095| thread/sql/one_connection              |          32114| mydba            | jianlong_proxy | Sleep               |

此时我们直接kill掉32095的session即可解决此异常

相关参数：

• innodb_lock_wait_timeout ，指定行锁等待的时间，默认50秒，如果50秒没有拿到想要的锁则会被返回错误信息ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，注：在事务因timeout引发错误异常时，是不会进行rollback的，需要用户自己判断 是否commit或rollback；
• innodb_rollback_on_timeout, 默认OFF，默认在事务timeout超时innodb仅仅会rollback 事务里的最后一条语句，但是若为ON，则在事务timeout时会rollback整个事务；

参考

《MySQL实战45讲》06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
《MySQL技术内幕 InnoDB存储引擎》第6章 锁
知乎文章：https://zhuanlan.zhihu.com/p/30551926