当前位置:首页>新闻详情

服务热线

400-888-2837

MySQL DDL 出现长时间等待 MD问题分


作者:008贵宾厅-008贵宾厅手机版-008贵宾厅官网      发布时间:2020-05-05 09:40:17


  给表新增字段时,发现锁表了,查看进程,提示Waiting for table metadata lock,等待锁释放;然而蛋疼的是几分钟过去了,依然没有任何的进展,特此记录下这个问题的定位过程以及MDL的相关背景知识

  首先需要确认什么地方加锁,从mysql出发,应该怎么定位?

  对于mysql而言,一般来讲上锁和事物时伴生关系,所以我们的直观出发点就是查找db当前正在执行的事物

  然后登陆到目标机器,查看端口号对应的进程,通过lsof命令查看

  从图中可以看出,是一个python进程的mysql连接开启的事物,进程id为5436

  这个脚本正是测试aiomysql的python脚本,内容比较简单

  对python不太熟,直接借助google查一下,发现有同样的问题

  这个问题抛出,在通过with打开连接获取游标后,执行mysql,但是没有commit之前,会锁表,这个期间修改表都会出现等待

  下面近给出了解答,并没有看到更多的深层次的说明,先记录下,解决办法就是在创建连接池的时候,选择自动提交方式,然后就不会有这个问题了

  找到一篇文章说MDL的,推荐详细阅读MySQL表结构变更你不可不知的Metadata Lock详解

  抓一下核心的要点,简单说一下看完这篇文章之后的朴素理解

  MetaData Lock 简称为MDL,简单来说就是表的元数据锁;当修改表结构的时候,就需要持有这个锁

  MDL的主要作用只有一点,保护一个正在执行的事物表结构不被修改

  有一个原则,MDL是事物级别的,只有事物结束之后才会释放,而这里面说的事物分为两类

  直接看上面的说明,不太直观,一个经典的case如下

  session1 开启了一个事物,执行查询操作;但是现在session2 要删除表,如果执行成功,那么session1的第二次查询就跪了,这样就违背了事物的原则,所有在5.5版本引入了MDL,来保证在事物执行期间,表结构不被修改

  当我们出现修改表结构,就需要获取MDL的排他锁,因此只有这个表没有事物在执行时,才能获取成功;当持有独占锁之后,这个表的其他操作将被阻塞(即不能插入数据,修改数据,也不能开启事物操作)

  因此在执行DDL时,一直出现等待MDL的时候,常见的原因有下面三个

  通过 show processlist看到表上有正在进行的操作(包括读),此时修改表时也会等待获取MDL,这种时候解决办法要么就是等待执行完毕,要么就是直接kill掉进程

  通过 show processlist没有找到表上的操作,但是通过odb_trx发现有未提交的事物,

  通过 show processlist 和事物查询都没有的情况下,可能的场景是一个显示的事物中,对表的操作出现了异常,虽然事物失败,但是持有的锁还没有释放,也会导致这个原因

  前面两小节,分别说明什么是MDL(朴素理解为表的元数据锁),以及当修改表时出现长时间的等待MDL的原因分析;正常看完之后,应该会有下面的疑惑

  上面的内容,可能信息量比较大,特别是MDL的锁分类情况,很难抓住重点,针对我们日常接触中,简单给出小结

  MDL是为了保证事物执行过程中,表结构不被修改引入的;因此修改表结构的前提是这个表上没有事物(没有正在执行,失败,或者未提交的事物)

  DDL执行,一般来讲是需要获取排他的MDL

  DML都会开启事物,因此会获取MDL_SW锁

  DDL: 数据定义语言,可以简单理解为表的操作,如创建,修改,删除表、视图等,新增索引、字段等操作

  DQL: 数据查询语言,常见的select语句

  MDL读锁是互相兼容的,可以有多个增删查改

  MDL读写锁之间是互斥的,所以如果DDL卡住,就证明有事务在执行,不能申请MDL写锁

  当事务本身执行的时候理论上是不能容忍表结构在中途发生改变的

  一灰灰的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

  尽信书则不如,以上内容,纯属一家之言,因个人能力有限,难免有疏漏和错误之处,如发现bug或者有更好的建议,欢迎批评指正,不吝感激

008贵宾厅-008贵宾厅手机版-008贵宾厅官网