转载-修订MySQL Waiting for table metadata lock

MySQL元数据锁（after mysql5.53）

 

 

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时就会出现复制错误的现象。

在了解这2个作用之前，不得不了解在5.53以前的mysql Metadata LOCK 机制：

  5.5.3版本之前,MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度的：即语句执行完成后，不管事务是否可以完成，其表结构就可以被其他会话更新掉！
    而引入Metadata lock后，表结构元数据(Metadata)的锁定变成了事务(transaction)粒度的,即只有事务结束时才会释放Metadata lock。

 

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

 

 

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

事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。

 

如程序或者脚本显式开启事务（start transaction），该事务内的query语句(包含select)会占用相关表的metadata lock(profile:Opening tables阶段)。导致后续的所有DDL操作语句全部被阻塞，原因就是获取不到metadata lock

 

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

 

解决办法：

思考，既然是事物级别的，那么需要查询事物级别的相关视图，找出僵尸事物 （存在一直running( trx_started开始很久)的事物）

select * from information_schema.innodb_trx;

　　查找相关running 的事物 找到 trx_mysql_thread_id 事物号，与information_schema.PROCESSLIST进行关联，判断该查询 并进行kill

select * from information_schema.`PROCESSLIST` where id =(select trx_mysql_thread_id from information_schema.innodb_trx )