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

开发者社区> 问答> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

2018-09-23 00:19:25 1327 0
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]售后技术支持。
取消 提交回答
全部回答(0)
相关问答

1

回答

【RDS】RDS MySQL如何使用utf8mb4字符集存储emoji表情?

2022-01-14 15:02:34 1001浏览量 回答数 1

1

回答

RDS MySQL迁移至PolarDB MySQL中其他限制的说明是什么?

2021-12-14 15:52:42 107浏览量 回答数 1

1

回答

RDS MySQL同步至PolarDB MySQL集群中支持的同步架构是什么?

2021-12-12 22:38:14 104浏览量 回答数 1

0

回答

RDS MySQL同步至阿里云消息队列Kafka版中注意事项的说明是什么?

2021-12-12 23:28:55 227浏览量 回答数 0

1

回答

RDS MySQL同步至DataHub中支持同步的SQL操作是什么?

2021-12-12 23:57:40 142浏览量 回答数 1

1

回答

RDS MySQL实例间的同步支持的目标库信息配置的说明是什么?

2021-12-12 21:48:18 100浏览量 回答数 1

1

回答

RDS MySQL实例间的同步支持的源数据库有哪些?

2021-12-12 21:42:24 104浏览量 回答数 1

0

回答

RDS MySQL同步至阿里云消息队列Kafka版中源库的环境要求的说明是什么?

2021-12-12 23:28:54 206浏览量 回答数 0

1

回答

RDS mysql数据库用户名填写完 并没有提示我输入密码

2017-01-17 15:15:01 2593浏览量 回答数 1

0

回答

RDSmysql数据库不能用identity作为数据库名如何解决

2015-03-27 18:27:42 4293浏览量 回答数 0
+关注
梨好橙
4年前:计科小白 现在:计科老白
12
文章
467
问答
问答排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载