注册 登录
  • 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL Waiting for table metadata lock故障分析

MySQL FAQ 彭东稳 8490次浏览 已收录 0个评论

源起

线上执行ALTER时,通过show processlist查看到出现Waiting for table metadata lock ,导致后面的查询都无法执行。

由于当时直接把ALTER给kill了,所以基本没有现场,所以下面模拟什么情况MySQL产生这种事故。

MySQL元数据锁

MySQL DBA对于Waiting for table metadata lock肯定不会陌生,一般都是进行alter操作时被堵住了,导致了我们在show processlist 时,看到线程的状态是在等metadata lock。

为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁(metadata lock),来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。对于引入MDL,其主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。

所以在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在Metadata lock wait 。例如下面的这种情形:

MySQL Waiting for table metadata lock故障分析

若没有MDL锁的保护,则事务2可以直接执行DDL操作,并且导致事务1出错,5.1版本即是如此。5.5版本加入MDL锁就在于保护这种情况的发生,由于事务1开启了查询,那么获得了MDL锁,锁的模式为SHARED_READ,事务2要执行DDL,则需获得EXCLUSIVE锁,两者互斥,所以事务2需要等待。

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

引起Metadata lock wait出现的场景:Waiting for table metadata lock

场景一:当前有执行DML操作时执行ALTRE操作。

从上述例子可以看出,我们在执行DDL语句(包括添加字段、添加索引、更改数据类型等)的时候得事先看一下,进程中是否已经存在某些DML语句占用了表的元数据锁,这样会导致DDL语句处于锁等待状态。一旦出现Waiting for table metadata lock等待现象,后续所有对该表的访问都会阻塞在该等待上,包括读操作,导致连接堆积,业务受影响。

场景二:当前有对表的长时间查询或使用mysqldump/mysqlpump时,使用alter会被堵住。

场景三:显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,使用alter会被堵住。

场景四:表上有失败的查询事务,比如查询不存在的列,语句失败返回,但是事务没有提交,此时alter仍然会被堵住。

其实SESSION A中的事务并未开启,但是由于select获取表元数据的语句,语法上是有效的,虽然执行失败了,但是任然不会释放元数据锁,故而导致SESSION B的alter动作被阻塞。

通过SESSION D查看当前打开事务时,你会发现没有,从而找不到原因。所以当出现这种场景时,如何判断是哪个进程导致的呢,我们可以尝试查看表performance_schema. events_statements_current,分析进程状态来进行判断。

然后找到其sid, kill掉该session,也可以kill掉DDL所在的session解决可以解决此问题。

另外,测试时SESSION A要显式开启一个事务,否则查询会隐式回滚结束,无法重现上面的场景。SESSION B执行alter后,没有立即阻塞住,而是立马开始copy to tmp table,这个过程结束后,才进行了MDL锁等待。这怎么解释呢,应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤,在这种情况下,到最后一步才需要MDL锁,所以copy过程中不会阻塞。由于没有查询在进行,而且查询也没有进入innodb层 (失败返回),所以show processlist和information_schema.innodb_trx没有可以参考的信息。

出现以上几种情况时,这个时候如果进行如下操作就会引起MDL:

  • 创建、删除索引。
  • 修改表结构。
  • 表维护操作(optimize table、repair table等)。
  • 删除表。
  • 获取表上表级写锁 (lock table tab_name write)。

使用Profile分析场景三:显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,使用alter会被堵住

然后回滚SESSION A,等待SESSION B和SESSION C执行完,查看profile。

查看SESSION A

查看SESSION C

从上述测试可以看出,SESSION C需要打开表时碰到了元数据锁。MySQL不论SESSION A执行的是select还是delete,此时alter table语句无法获取到metadata独占锁,会进行等待;所以会影响SESSION C的读取。

这是最基本的一种情形,这个和MySQL 5.6中的online ddl并不冲突。一般alter table的操作过程中,在after create步骤会获取metadata独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入,当然并不是所有ALTER语句都支持online ddl。

总之,alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的),在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

参考

理解MySQL的MDL元数据锁

MySQL使用profile分析SQL语句执行过程


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!