一次Waiting for table metadata lock的处理

一个没提交的事务使用了A表， 另外一个session 对A表进行alter，出现waiting for table metadata lock 


在insert into t select * from share 运行时， 同时执行alter table t add index(play_count)， 
alter table语句会Waiting for table metadata lock， 直到insert into … select 语句结束。 


不是传说5.6支持online DDL么？ 怎么还会Waiting for table metadata lock? 

后来想想， online DDL应该是指在alter table进行的时候， 插入/修改/删除数据的sql语句不会Waiting for table metadata lock. （ps：顺序不一样）

参考：http://www.cnblogs.com/dyllove98/archive/2013/07/16/3194332.html

版本：mysql5.5.17 

查看：Innodb_buffer_pool_pages_free = 0 

解决：set global innodb_stats_on_metadata=0 

查看丁奇老大博客：原文http://dinglin.iteye.com/blog/1575840 

这个问题来自冷之同学测试时候碰到的一个“诡异现象”。 



1、 测试现象       

测试的库有很多数据，但是重启之后，只对一个表的5w条记录作查询。查询条件客户端控制，确保查询范围。innodb_buffer_pool_size设置为35G。 

现象1：查询性能会出现大幅度抖动； 

现象2：介入追查后发现，Innodb_buffer_pool_pages_free = 0 



         其中bp剩余量这个是最直观异常的，因为访问的5w行记录撑死也不可能把35G内存吃光的。在QA同学确认没有别人在使用这个库的情况下。 



2、过程和原因 

         其实几乎确定还是有别的查询在访问的。所以打开general_log。 发现除了QA同学压的语句外，这个Server上还有一些监控语句。 

         其中一个语句如下 

select constraint_schema,table_name,constraint_name,constraint_type from information_schema.table_constraints where table_schema not in ('information_schema', 'mysql', 'test');  

         这个语句访问了表 information_schema.table_constraints. 

跟踪发现这个语句触发了读盘操作。原因是需要访问引擎的info()接口，而InnoDB此时又“顺手”做了更新索引统计的操作dict_update_statistics。 

更新索引统计的基本流程是随机读取部分demo行。所以这个操作实际上是访问了这个Server里面的所有表，因此不只是访问5w行。 

而且由于别的表事先没有被访问，就会导致读盘操作，也包括BP的LRU更新。 



3、哪些表会触发 

         不只是上面提到的table_constraints，information_schema库下的一下几个表，访问时候都会触发这个“顺手”操作。 

information_schema.TABLES 

information_schema.STATISTICS 

information_schema.PARTITIONS 

information_schema.KEY_COLUMN_USAGE 

information_schema.TABLE_CONSTRAINTS 

information_schema.REFERENTIAL_CONSTRAINTS 

         其实还有 show table status ，也会触发这个操作，只是只处理单表，所以影响没那么明显。 



4、修改 

头痛医头的方法是把这些监控去掉。但实际上像TABLES、TABLE_CONSTRAINTS这些表，都是静态数据，访问时不作索引统计也没关系的。 

另外一个方法就是把innodb_stats_on_metadata设置成off，这样上述说到的这些表访问都不会触发索引统计。 

         实际上这个动态统计的功能已经不推荐了，官方已经在6.0以后增加参数控制DML期间也不作动态统计了。因此这个参数配置成off更合理些（默认是on）

本文转载自ITEYE文章：【转载】关于Waiting for table metadata lock的处理

在进行alter table操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，也会在Opening tables的阶段进入Waiting for table metadata lock的队列。如果是产品环境的核心表出现了这样的锁等待队列，就会造成灾难性的后果。

造成alter table产生Waiting for table metadata lock的原因其实很简单，一般是以下几个简单的场景：

场景一：

通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

这是最基本的一种情形，这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中（见下图），在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册： http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html ） 


 

场景二：

通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。

场景三：

通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下：

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

总之，alter table的语句是很危险的，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

本文转载自推酷文章：MySQL出现Waiting for table metadata lock的场景浅析