当前位置: 首页 > news >正文

【MySQL经典案例分析】 Waiting for table metadata lock

本文由云+社区发表

一、 问题是这样来的

​ 2018年某个周末,接到连续数据库的告警,告警信息如下:

img

二、 苦逼的探索过程

1、总体的思路

看到too many connection的报错信息,基本上可以把问题定位在:

(1)机器负载飙升,导致SQL执行效率下降,导致连接推积

(2)业务访问量突增(或者有SQL注入现象),导致连接数打满

(3)出现“死锁”或者锁竞争严重,导致大量SQL堆积

2、排查过程

(1)机器的各项性能指标都显示正常, 没有出现高负载现象,暂时先排除了这种原因

(2)查看监控信息,发现在连接数打满的时间点前并没有访问量突增的趋势,同时通过检查告警信息并没有发现有注入工单

img

(3)最后上到服务器上查看下SQL的执行情况

①查看show full processlist;

img

​ 大量的请求都是在“Waiting for table metadata lock”,可以分成三类请求:

  • Select请求
  • Rename请求
  • Sleep请求

    ②分析Waiting for table metadata lock

​ 一般来说常见的“Waiting for table metadata lock”会出现在DDL操作或者是有未提交的事务上,从information_schema.processlist表中,没有发现有DDL操作,而能够产生MDL锁的操作也只剩下rename,但是根据SQL执行的状态,rename操作也是在等待MDL锁,所以rename操作应该是被阻塞的操作,而不是产生MDL锁的操作。

​ 接着我们来查看下死锁和事务的相关指标:

  • show engine innodb status;中没有任何死锁的信息
  • information_schema.innodb_trx 、information_schema.innodb_locks 、 information_schema.innodb_lock_waits 的也没有任何形式的锁信息。

​ 现在基本又排除了显示的死锁问题,那是从show full processlist中也抓不出任何请求,这里就比较疑惑了,当看了下表的结构式,发现这个表是myisam引擎的,所以上面的两种统计信息里面没有任何值就可以解释了。

img

​ 那么其实问题就集中在有未结束的事务上了,这里其实有一个误区,当时跟开发沟通存在未关闭的事务时,开发一直认为不可能,因为myisam表是不支持事务的,只有innodb支持事务。但是对于MDL锁来说,5.5之后引入MDL事务级别的锁不论对myisam还是innodb都是生效的。

③查看未提交的事务

​ 之后查看了下系统的事务自动提交的变量,autocommit的值是ON,那说明如果是事务未提交的话只可能是业务主动的开启一个事务,而没有commit。

img

​ 为了验证这个猜想,打开了general log,在log中果然发现,业务在开启事务后,把autocommit的值设为0了,导致必须要显示的commit才能提交事务。

img

img

​ 这时候我们反过头来看一下host为10.49.84.70的连接请求,由于select的执行速度很快,而且访问并不频繁,所以在抽样的show processlist中,状态值大部分时间是“Sleep”,给问题的定位带来了一些迷惑性的干扰。接着我们kill掉了这个进程,果然推积的请求瞬间就执行完成了,也之间印证了刚刚上述推论。

2、问题解决

​ 在与开发同学沟通过程中,开发同学说库中是myisam表所以不会主动开启事务,在代码里也没有设置autocommit=0的代码,那么根本原因在哪?

​ 当我们定位到这台服务器上的请求都是来自python的定时脚本,使用python 操作mysql的时候,使用了其pymysql模块,但是在进行插入操作的时候,必须使用受到提交事务。Python的pymysql模块默认是会设置autocommit=0的。

img

​ 让我们来对比一下其他同样使用python访问的正常连接请求,再断开前都会手动的commit。

img

​ 找到原因后有思考了下,是不是可以在建连后就设置autocommit=1呢?这样对于之后新变更的SQL就不要再考虑到手动commit的事情了,可以通过在初始化连接池的时候,对每一个连接进行设置,即

img

三、 延伸的一些思考

1、metadata lock

(1)MDL简述

​ 为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从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仍然会被堵住

2、myisam、innodb对事务的支持

​ Myisam是不支持事务的,innodb是支持事务的,这个概念其实没有任何问题,但是这里只的都是对于数据的事务性操作的支持,通过如下简单的实验可以很清楚的理解(关于事务的相关概念和解释就不再赘述了,只是想区别一下mysiam不支持事务,但是主动开始事务中对Myisam的操作仍然会产生MDL锁):

​ 在隔离级别为RC的情况下:

(1)myisam表

① CREATE TABLE tb2 (a int(11) DEFAULT NULL ) ENGINE=MyISAM;

② Session 1:

​ mysql> begin ;

​ mysql> insert into tb2(a) value(1);

​ (在session2的update之后)

​ mysql> select * from tb2;

​ +--------+

​ | a |

​ +--------+

​ | 3 |

​ +--------+

Session 2:

​ mysql> select * from tb2;

​ +---------+

​ | a |

​ +---------+

​ | 1 |

​ +---------+

​ mysql> update tb2 set a=3 where a=1;

​ mysql> select * from tb2;

​ +--------+

​ | a |

​ +--------+

​ | 3 |

​ +--------+

​ mysql> alter table tb2 add b int(11);

... hangs ...

(2)innodb表

①CREATE TABLE tb3 (a int(11) DEFAULT NULL ) ENGINE=INNODB;

② Session 1:

​ mysql> begin ;

​ mysql> insert into tb3(a) value(1);

​ Session 2:

​ mysql> select * from tb3;

​ Empty set (0.00 sec)

3、myisam表的另一个BUG

(1)场景

① CREATE TABLE tb2 (a int(11) DEFAULT NULL ) ENGINE=MyISAM;

② Session 1:

​ mysql> begin ;

​ mysql> select * from tb2;

​ Session 2:

​ mysql> create table if not exists tb2(a int);

​ ... hangs ...

③查看show processlist

​ Session 1:Sleep

​ Session 2:Waiting for table metadata lock

(2)解决方式

①session 1上commit或者rollback

②另外再开一个session3 ,kill掉可疑连接

此文已由作者授权腾讯云+社区发布

搜索关注公众号「云加社区」,第一时间获取技术干货,关注后回复1024 送你一份技术课程大礼包!

相关文章:

  • Innodb之全局共享内存
  • sql 开窗函数
  • 我的友情链接
  • 实现菜单下拉伸展折叠效果demo
  • Android中的树状(tree)列表
  • 基于MVC思想实现一个简单的贪吃蛇小游戏
  • Device Tree Usage 【转】
  • 从容器到云原生 – 极致弹性
  • 【NetApp】FPolicy的使用流程图
  • Android基础---获取手机硬件数据(转)
  • (十三)Java springcloud B2B2C o2o多用户商城 springcloud架构 - SSO单点登录之OAuth2.0 根据token获取用户信息(4)...
  • k8s基本概念及入门案例
  • httplib 和 httplib2区别之 gzip解压
  • 【第四期】图片轮播代码
  • 币泳金:比特现金分叉满月比特币跌去半数,比特币为何无法稳定的反弹上升...
  • [PHP内核探索]PHP中的哈希表
  • 《Java8实战》-第四章读书笔记(引入流Stream)
  • 《剑指offer》分解让复杂问题更简单
  • 【面试系列】之二:关于js原型
  • JAVA多线程机制解析-volatilesynchronized
  • Java小白进阶笔记(3)-初级面向对象
  • js
  • Making An Indicator With Pure CSS
  • Nginx 通过 Lua + Redis 实现动态封禁 IP
  • Redis提升并发能力 | 从0开始构建SpringCloud微服务(2)
  • Spark学习笔记之相关记录
  • sublime配置文件
  • Terraform入门 - 3. 变更基础设施
  • Windows Containers 大冒险: 容器网络
  • 短视频宝贝=慢?阿里巴巴工程师这样秒开短视频
  • 二维平面内的碰撞检测【一】
  • 今年的LC3大会没了?
  • 聊聊spring cloud的LoadBalancerAutoConfiguration
  • 首页查询功能的一次实现过程
  • 延迟脚本的方式
  • 一些关于Rust在2019年的思考
  • LevelDB 入门 —— 全面了解 LevelDB 的功能特性
  • ​3ds Max插件CG MAGIC图形板块为您提升线条效率!
  • ​Linux Ubuntu环境下使用docker构建spark运行环境(超级详细)
  • $var=htmlencode(“‘);alert(‘2“); 的个人理解
  • (DFS + 剪枝)【洛谷P1731】 [NOI1999] 生日蛋糕
  • (Redis使用系列) Springboot 使用redis的List数据结构实现简单的排队功能场景 九
  • (二)fiber的基本认识
  • (一)插入排序
  • (转)winform之ListView
  • .NET 简介:跨平台、开源、高性能的开发平台
  • .net 提取注释生成API文档 帮助文档
  • .Net(C#)自定义WinForm控件之小结篇
  • /etc/sudoer文件配置简析
  • @Builder用法
  • [ MSF使用实例 ] 利用永恒之蓝(MS17-010)漏洞导致windows靶机蓝屏并获取靶机权限
  • [Angularjs]asp.net mvc+angularjs+web api单页应用之CRUD操作
  • [C#]winform使用引导APSF和梯度自适应卷积增强夜间雾图像的可见性算法实现夜间雾霾图像的可见度增强
  • [Docker]十一.Docker Swarm集群raft算法,Docker Swarm Web管理工具
  • [ESP32 IDF]web server