文档章节

MySQL MetaData Lock 案例分享

IT--小哥
 IT--小哥
发布于 2017/07/26 19:04
字数 1149
阅读 53
收藏 0

前言:今天开发童鞋遇到一个奇怪的问题,在测试环境里面执行drop database dbname发现一直夯住不动,等了很久也没有执行,于是问题就到我这里了

 

一、什么是MetaData Lock?

MetaData Lock即元数据锁,在数据库中元数据即数据字典信息包括db,table,function,procedure,trigger,event等。metadata lock主要为了保证元数据的一致性,用于处理不同线程操作同一数据对象的同步与互斥问题

 

二、MetaData Lock的前世今生

mdl锁是为了解决一个有名的bug#989,所以在5.5.3版本引入了MDL锁。其实5.5也有类似保护元数据的机制,只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是,5.1对于元数据的保护是语句级别的,5.5对于metadata的保护是事务级别的。所谓语句级别,即语句执行完成后,无论事务是否提交或回滚,其表结构可以被其他会话更新;而事务级别则是在事务结束后才释放MDL。引入MDL锁主要是为了解决两个问题:

事务隔离问题:比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求。
数据复制问题:比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。也就是上面提到的bug#989

 

三、Waiting For Table MetaData Lock场景重现(这也是我们今天遇到的问题)

session A:注意这里是显示的提交一个事务

root@localhost:mysql.sock  18:03:49 [tom]>desc test;
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| id         | int(10)     | NO   | PRI | NULL              | auto_increment |
| name       | varchar(32) | YES  |     | NULL              |                |
| age        | int(10)     | YES  |     | NULL              |                |
| createtime | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.01 sec)

root@localhost:mysql.sock  18:03:43 [tom]>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  18:03:46 [tom]>select c99 from test;
ERROR 1054 (42S22): Unknown column 'c99' in 'field list'

session B:执行Online DDL(我这个是MySQL5.7.14官方版本哦)

root@localhost:mysql.sock  18:02:26 [tom]>Start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  18:04:16 [tom]>alter table test drop column age;
发生阻塞...

session C:processlist看不到任何test表操作,但是有MDL锁

root@localhost:mysql.sock  18:02:31 [tom]>show processlist;
+-------+---------+----------------+------+---------+------+---------------------------------+----------------------------------+
| Id    | User    | Host           | db   | Command | Time | State                           | Info                             |
+-------+---------+----------------+------+---------+------+---------------------------------+----------------------------------+
|   743 | monitor | 10.0.0.6:54020 | NULL | Sleep   |    3 |                                 | NULL                             |
| 92210 | monitor | 10.0.0.6:46778 | NULL | Sleep   |    1 |                                 | NULL                             |
| 93740 | root    | localhost      | tom  | Query   |    0 | starting                        | show processlist                 |
| 93742 | root    | localhost      | tom  | Sleep   |   64 |                                 | NULL                             |
| 93743 | root    | localhost      | tom  | Query   |    8 | Waiting for table metadata lock | alter table test drop column age |
+-------+---------+----------------+------+---------+------+---------------------------------+----------------------------------+
5 rows in set (0.00 sec)

innodb engine监控看不到任何锁冲突信息

------------
TRANSACTIONS
------------
Trx id counter 112477
Purge done for trx's n:o < 112477 undo n:o < 0 state: running but idle
History list length 556
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421340178270032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421340178271856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421340178270944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------

查看information_schema

root@localhost:mysql.sock  18:18:46 [tom]>select trx_id,trx_state,trx_started,trx_mysql_thread_id from information_schema.innodb_trx; 
Empty set (0.00 sec)

这种情况是一个特例,存在一个查询失败的语句,比如查询不存在的列,语句失败返回,但是事务没有提交,此时alter仍然会被堵住。通过show processlist看不到表上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对表进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

下面看看官方的解释:8.11.4 Metadata Locking

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.

定位问题SQL,然后杀掉对应的SQL。查看每一个session正在执行的sql,然后通过下面语句定位到问题sql,杀掉就可以了

select * from performance_schema.events_statements_current\G
select * from sys.session\G
select * from sys.processlist\G

 

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

© 著作权归作者所有

共有 人打赏支持
IT--小哥
粉丝 47
博文 121
码字总数 93642
作品 0
东城
数据库管理员
私信 提问
MySQL · 捉虫动态 · 5.7 mysql_upgrade 元数据锁等待

问题描述 如下图,mysqlupgrade 过程中,执行 DROP DATABASE IF EXISTS performanceschema 一直在等待 metadata lock 问题排查 简单粗暴的方法 有一种简单的解决方法,把其他连接kill掉,释放...

阿里云RDS-数据库内核组
2017/04/09
0
0
MySQL · 答疑解惑 · MySQL 锁问题最佳实践

前言 最近一段时间处理了较多锁的问题,包括锁等待导致业务连接堆积或超时,死锁导致业务失败等,这类问题对业务可能会造成严重的影响,没有处理经验的用户往往无从下手。下面将从整个数据库...

阿里云RDS-数据库内核组
2016/03/09
0
0
【MySQL经典案例分析】 Waiting for table metadata lock

在这篇文章中: 一、 问题是这样来的 二、 苦逼的探索过程 三、 延伸的一些思考 一、 问题是这样来的 2018年某个周末,接到连续数据库的告警,告警信息如下: 二、 苦逼的探索过程 1、总体的...

刘迪
2018/12/06
0
0
Waiting for table metadata lock

MySQL5.5 中引入了 metadata lock. 顾名思义,metadata lock 不是为了保护表中的数据的,而是保护 database objects(元数据)的。包括表结构、schema、存储过程、函数、触发器、mysql的调度事...

雪隐千寻
2017/12/06
0
0
【mysql】不可不知的Metadata Lock

一、问题发生 说一个现象,当收到服务器报警之后,数据库服务器CPU使用超过90%,通过 一看,满屏都是 状态的连接。第一反应就是kill掉这些连接,奈何连接实在太多,实在kill不过来,于是重启...

踏雪无痕SS
2018/09/20
0
0

没有更多内容

加载失败,请刷新页面

加载更多

通俗易懂解释网络工程中的技术,如STP,HSRP等

导读 在面试时,比如被问到HSRP的主备切换时间时多久,STP几个状态的停留时间,自己知道有这些东西,但在工作中不会经常用到,就老是记不住,觉得可能还是自己基础不够牢固,知识掌握不够全面...

问题终结者
昨天
3
0
看了一下Maven的内容

了解了Maven其实是一个跨IDE的标准构建工具,能推广的原因估计是借了仓库的便利。 另一个作用是可以通过Maven的功能在社区版的IDEA去创建Web项目,下次实践看看

max佩恩
昨天
1
0
day27:expect批量杀进程|

1、linux下当前目录有一个文件ip-pwd.ini,内容如下: [root@localhost_002 shell100]# cat ip-pwd.ini 10.111.11.1,root,xyxyxy10.111.11.2,root,xzxzxz10.111.11.3,root,12345610.......

芬野de博客
昨天
3
0
分布式之数据库和缓存双写一致性方案解析(二)

引言 该文是对《分布式之数据库和缓存双写一致性方案解析》,一文的补充。博主在该文中,提到了这么一句话 应该没人问我,为什么没有先更新缓存,再更新数据库这种策略。 博主当时觉得,这种...

hensemlee
昨天
5
0
druid安装与案例

druid 可以运行在单机环境下,也可以运行在集群环境下。简单起见,我们先从单机环境着手学习。 环境要求 java7 或者更高版本 linux, macOS或者其他unix系统(不支持windows系统) 8G内存 2核C...

hblt-j
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部