Waiting for table metadata lock

今天药忘吃喽~ 2022-12-24 02:58 190阅读 0赞

mysql查询卡死,从navicat客户端查看“服务器监控”看到state有很多“Waiting for table metadata lock”。

我的解决方式就是kill掉“Waiting for table metadata lock”的查询。kill掉就好了。

出现问题的原因可能跟我给表加索引有关系。因为发现多表联查sql很慢,而且在这之前已经执行了多次这个sql,都没返回结果。

而此时我又去alter表,可能导致了死锁。后续的insert也堵塞在那里。

等我kill掉“Waiting for table metadata lock”的查询sql后,insert马上被消费了。

网上看到的答案:

MySQL在进行一些alter table等DDL操作时,如果该表上有未提交的事务则会出现 Waiting for table metadata lock ,而一旦出现metadata lock,该表上的后续操作都会被阻塞。

1、metadata Lock 简述
为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。

因此从MySQL5.5版本开始引入了MDL锁(metadata lock),来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。

对于引入MDL,其主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。所以在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在Metadata lock wait 。
支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表,都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象,后续所有对该表的访问都会阻塞在该等待上,导致连接堆积,业务受影响。
2、常见的MDL场景
①当前有执行DML操作时执行DDL操作
② 当前有对表的长时间查询或使用mysqldump/mysqlpump时,使用alter会被堵住
③ 显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,DDL会被堵住
④ 表上有失败的查询事务,比如查询不存在的列,语句失败返回,但是事务没有提交,此时DDL仍然会被堵住

处理方法: kill 掉 DDL所在的session.

扩展:

  • DML(data manipulation language)数据操纵语言:就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作
  • DDL(data definition language)数据库定义语言:其实就是我们在创建表的时候用到的一些sql,比如说:CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上
  • DCL(Data Control Language)数据库控制语言:是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。这个比较少用到

发表评论

表情:
评论列表 (有 0 条评论,190人围观)

还没有评论,来说两句吧...

相关阅读

    相关 mysql Waiting for table metadata lock

    当我们在进行表的更新操作或者是想要drop table的操作的时候,有时候,会卡死,这样的原因在于事务还未提交或者未回滚。但是此时有其他的用户在执行表的ddl语句。 (更新表