解决mysql线上发生锁表问题Waiting for table metadata lock(原创)

解决mysql线上发生锁表问题(原创)

问题描述

最近,有同事用navicat打开mysql数据库,给一张表在线添加索引后, 该表一直卡主，打不开, 总在提示"正在加载"。

问题复现

初步断定应该表被锁了。复现当时的情况：
session1：对user表进行修改操作，事务不要commit。

session2：对user表添加索引。

这时打开user表，复现了当时情况，一直卡主，打不开, 总在提示"正在加载"。

排查过程

1 通过show processlist命令，显示用户正在运行的线程情况，此命令可帮助查看当前有问题的查询语句。查看 “stats”字段,出现了"Waiting for table metadata lock"提示表被锁住了。“info”字段显示的是正在执行的sql。

2 这时候需要查看未提交的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx命令，找到有问题的线程id。

3 kill掉trx_mysql_thread_id，将有问题的id或在session 1上commit或者rollback。
再次通过show processlist命令，发现一切正常，user表可以正常打开。

分析原因

如果当前正在执行的sql，是长事务或事务未提交, 同时又进行DDL(CREATE、DROP、ALTER 等)操作。会发生锁表现象。
详情可参考msql的官方文档
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html

延伸思考

参考转自:https://cloud.tencent.com/developer/article/1369229
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等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
常见MDL锁场景
①当前有执行DML操作时执行DDL操作
② 当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住
③ 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL会被堵住
④ 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住
myisam、innodb对事务的支持
Myisam是不支持事务的，innodb是支持事务的，这个概念其实没有任何问题，但是这里只的都是对于数据的事务性操作的支持，通过如下简单的实验可以很清楚的理解（关于事务的相关概念和解释就不再赘述了，只是想区别一下mysiam不支持事务，但是主动开始事务中对Myisam的操作仍然会产生MDL锁）