技术运维问题 - MYSQL使用 -RDS MySQL 表上 Metadata lock 的产生和处理-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

技术运维问题 - MYSQL使用 -RDS MySQL 表上 Metadata lock 的产生和处理

李沃晟 2018-09-23 00:19:25 1045
1. Metadata lock wait 出现的场景
2. Metadata lock wait 的含义
3. 导致 Metadata lock wait 等待的活动事务
4. 解决方案
5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务


1. Metadata lock wait 出现的场景


  • 创建、删除索引

  • 修改表结构

  • 表维护操作(optimize table、repair table 等)

  • 删除表
  • 获取表上表级写锁 (lock table tab_name write)


注:
  • 支持事务的 InnoDB 引擎表和 不支持事务的 MyISAM 引擎表,都会出现 Metadata Lock Wait 等待现象。
  • 一旦出现 Metadata Lock Wait 等待现象,后续所有对该表的访问都会阻塞在该等待上,导致连接堆积,业务受影响。



2. Metadata lock wait 的含义


为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ,来保护表的元数据信息。
因此在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在 Metadata lock wait 。

3. 导致 Metadata lock wait 等待的活动事务


  • 当前有对表的长时间查询

  • 显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚。

  • 表上有失败的查询事务


4. 解决方案


  • show processlist 查看会话有长时间未完成的查询,使用kill 命令终止该查询。


[url=http://img01.taobaocdn.com/tfscom/TB1ZdsQJpXXXXbJXVXXXXXXXXXX.png]
查询 information_schema.innodb_trx 看到有长时间未完成的事务, 使用 kill 命令终止该查询。

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,(selectid, timefrominformation_schema.processlistwheretime = (selectmax(time)frominformation_schema.processlistwherestate = 'Waiting for table metadata lock'and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) pwhere timestampdiff(second, i.trx_started, now()) > p.timeand i.trx_mysql_thread_id  not in (connection_id(),p.id);-- 请根据具体的情景修改查询语句-- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话
[url=http://img01.taobaocdn.com/tfscom/TB1JEA4JpXXXXb.XpXXXXXXXXXX.png]


注:关于清理会话,请参考:[url=https://help.aliyun.com/knowledge_detail/7593778.html]RDS MySQL 如何终止会话

  • 如果上面两个检查没有发现,或者事务过多,建议使用下面的查询将相关库上的会话终止-- RDS for MySQL 5.6
  • select
  •     concat('kill ', a.owner_thread_id, ';')
  • from
  •     information_schema.metadata_locks a
  •         left join
  •     (select
  •         b.owner_thread_id
  •     from
  •         information_schema.metadata_locks b, information_schema.metadata_locks c
  •     where
  •         b.owner_thread_id = c.owner_thread_id
  •             and b.lock_status = 'granted'
  •             and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id
  • where
  •     a.lock_status = 'granted'
  •         and d.owner_thread_id is null;
  • -- RDS for MySQL 5.5
  • select
  •     concat('kill ', p1.id, ';')
  • from
  •     information_schema.processlist p1,
  •     (select
  •         id, time
  •     from
  •         information_schema.processlist
  •     where
  •         time = (select
  •                 max(time)
  •             from
  •                 information_schema.processlist
  •             where
  •                 state = 'Waiting for table metadata lock'
  •                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2
  • where
  •     p1.time >= p2.time
  •         and p1.command in ('Sleep' , 'Query')
  •         and p1.id not in (connection_id() , p2.id);
  • -- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件;
  • -- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话




  • 如问题还未解决,请联系[url=https://selfservice.console.aliyun.com/ticket/createIndex.htm]售后技术支持。
SQL 运维 关系型数据库 MySQL 索引 RDS
分享到
取消 提交回答
全部回答(0)
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题