waiting for table metadata lock 问题深入分析

时间:2021-07-28 18:20:08
      相信很多msyql dba都碰到锁的问题,在MySQL 5.5.3版本中引入了Metadata lock: DDL语句打破了事务的隔离级别。
那么会有同学问,为什么在Mysql 5.5.3之前就很少遇到这种锁呢?原因是
    5.5.3版本之前,MySQL事务对于表结构元数据( Metadata)的锁定是 语句(statement)粒度的: 即语句执行完成后,不管事务是否可以完成,其表结构就可以被其他会话更新掉
    引入Metadata lock后,表结构元数据(Metadata)的锁定变成了 事务(transaction)粒度的,即 只有事务结束时才会释放Metadata lock

怎么出现的?

程序或者脚本显式开启事务(start transaction),该事务内的query语句(包含select)会占用相关表的metadata lock(profile:Opening tables阶段)。导致后续的所有DDL操作语句全部被阻塞,原因就是获取不到metadata lock。(在mysql 5.6版本后有优化)官方手册参阅:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

我们下面以现网case来探讨这个问题的出现于解决:

case

业务执行一条简单的alter table 操作,增加一个字段,很普通的一条sql,而且表不大,数据量很少,执行却消耗几百秒没反应(现场忘记截图)

waiting for table metadata lock 问题深入分析

补充一个测试图

从图可以看出业务执行的语句遇到metadata lock了。

1、        分析mysql的实例的情况

1.1 mysql> show processlist;

waiting for table metadata lock 问题深入分析

除了有一个 Waiting for table 之外没有其它的操作进程,全部是sleep进程。这时你觉得奇怪吗?为什么没有其它的进程锁住这个表,会导致这个ddl语句一直卡住呢? 我们接着分析。

1.2 查看表是否太大 mysql> show table status like 'tbl_xx' \G

waiting for table metadata lock 问题深入分析

                           图1.2

看出表非常小,不存在由于数据量大导致更新慢的问题;

1.3 查看引擎状态 mysql> show engine innodb status \G

waiting for table metadata lock 问题深入分析

数据量太大,一屏幕都显示不完,不看了。

既然几个比较直接的方法都查不到原因,那只能更深入的查下了,我打算从数据字典中查下(information_schema,performance_schema):

1.4,查找当前等待事务:

mysql> select * from  performance_schema .events_waits_current;

Empty set (0.03 sec)

显示空。

查找information_schema中的事件表(EVENTS)、锁等待表(INNODB_LOCK_WAITS),innodb当前出现的锁(INNODB_LOCKS)均没看到异常(这里就不贴图了)。

1.5 查找事务

既然造成该锁的原因是事务没有提交导致的,那我们应该去查找当前是否有事务在运行(runing注:由于事务一直是runing状态,这也就是为什么我之前查找各种锁都找不到的原因)

mysql> select * from information_schema.innodb_trx;

(此图又被刷不见了)不过有重大发现:一个trx_mysql_thread_id: 275255348 是从trx_started: 2015-12-03 14:58:45 一直处于runing状态的。

既然我们找到了id了 那我们再回顾使用show processlist查找该ID就行了:

waiting for table metadata lock 问题深入分析

发现了吗,该ID一直是sleep状态。很难发现该进程打开了这个表(可以通过show open tables 查看当前打开的表)。

 

解决办法:询问了开发这个点的脚本,操作。确认后通过后台mysql 直接kill掉这个进程,业务的alter操作瞬间完成。

附:欢迎大家一起探讨研究