MySQL长事务导致Metadata Lock问题分析

前几天的时候，公司的开发同事在对表添加字段的操作时，发现添加进度非常的慢。后来我通过show processlist发现当前积累了大量的Metadata lock：Waiting for table metadata lock 会话。其实mysql中MDL算是一个比较常见，并且一旦发生极易对系统造成影响的问题，当我们在会话中看到metadata lock时，就一定要小心了，因为它很可能会拖垮你的服务器。

那么Metadata lock到底是什么呢？我又是如何处理这个的问题？

今天我们就来看下MySQL中Metadata lock(MDL)吧。

MDL是什么时候引入的？

MySQL从5.5版本开始引入了MDL锁。主要来保护表的元数据信息，以解决或确保DDL和DML操作之间的一致性。

MySQL 从5.5.3版本，对Metadata lock进行了调整，主要是MDL锁持有的周期从语句变成了事务。

为什么引入MDL？

MDL的引入主要是为了解决两个问题。一是事务隔离问题，例如，在可重复隔离级别下，Session A 的两次查询过程中，Session B 修改了表结构。两次查询的结果会不一致，不能满足可重复读的要求。二是数据复制的问题，比如Session A在执行update语句的过程中，另一个是Session B改变删除了表并且先一步commit，会导致slave根据binlog进行主从复制时出现复制错误。

关于第二点，可参看官方bug库的一个bug：https://bugs.mysql.com/bug.php?id=989

MDL锁的特性

元数据锁是属于服务器层，表级锁，MDL锁适用于每个DML，DDL语句执行。DML操作需要MDL读锁，DDL操作需要MDL写锁。MySQL 从5.5.3版本，对Metadata lock进行了调整，主要是MDL锁持有的周期从语句变成了事务。所以一旦事务申请了一个 MDL 锁，在事务执行完成之前它不会释放锁。

MDL加锁过程是系统自动控制，不能直接干预，读写共享，读写互斥，写写互斥。申请MDL锁形成一个队列，写锁优先于读锁。一旦发生写锁等待，不仅当前操作会被阻塞，后续对表的所有操作也会被阻塞。

出现MDL锁的典型场景

典型的场景就是，mysql在进行一些alter table等ddl操作时，如果该表还有未提交的事务时，就会出现waiting for metadata lock。

另外SQL发生错误，事务未回滚或提交也可能会出现问题，比如事务1开启事务，对表执行语句报错，但事务未提交或未回滚，此时对表执行ddl操作，也会出现mdl。

MDL锁带来的问题

从MySQL 5.5.3开始，MDL锁的持有周期变成了事务，在autocommit=off的情况下，也大大增加了阻塞的可能性。并且一旦发生写锁等待，后续对该表的访问会阻塞等待，导致连接堆积，甚至导致cpu飙升，服务器宕机。

发现MDL锁，如何解决？

先说下思路，出现MDL锁时，我们的目标肯定是要找到最开始未提交的事务，提交事务，或者把它对应的会话kill掉的。但是因为mdl锁出现后，会阻塞后续所有对该表的访问，所以当业务处于高峰期，你使用show processlist可能会看到满屏的waiting for metadata lock 会话。会话太多以至于会干扰你定位具体哪个会话的操作没有及时提交而阻塞的DDL操作。因此当排查此类问题，我们需要查询 information_schema.innodb_trx 表中当前正在执行的事务。需要把最开始阻塞ddl的那个未提交的事务揪出来，提交事务，或把它对应的线程id kill掉才能解决问题。

至于具体操作，可参考如下的思路：

1. 查看当前存在的事务。                              
 select * from information_schema.INNODB_TRX\G;         

2.查看当前事务执行时间，如果某个事务执行了很长时间，可能就是有问题的那个。                          
select id, time from information_schema.PROCESSLIST where id in (select trx_mysql_thread_id from information_schema.INNODB_TRX )    

3.查看当前事务正在执行的语句。
#我们可以借助performance_schema.events_statements_current，该表可以看到会话对应的sql。

SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id) JOIN information_schema.processlist c ON b.processlist_id = c.id WHERE a.sql_text NOT LIKE '%performance%';


4.kill trx_mysql_thread_id;


登录后复制

5.7之后，performance_schema 库中还新增了metadata_locks表，专门记录 MDL 的相关信息。

好了，可以解答开头的问题了，我就是这么处理MDL的问题的...

如何规避MDL带来的风险？

上文我们提到，一旦发生MDL写锁等待，后续对该表的访问会阻塞等待，业务繁忙的情况会导致连接堆积，甚至导致cpu飙升，服务器宕机。

那么我们应该如何规避MDL带来的风险呢？下面是几点建议。

1. 规范使用事务。读操作不要在事务中，及时提交事务，避免使用大事务。
2. 程序上对一些错误进行捕捉，并回滚事务，否则事务脱离程序控制。
3. 设置参数lock_wait_timeout为较小值，使被阻塞端主动停止。
4. DDL操作尽量放在业务低峰期执行。
5. 增强监控告警，及时发现MDL锁。可采用一些kill机制，比如如果事务超过60s未结束，就kill掉。

总结

mysql为了解决元数据一致性的问题，引入了MDL。MDL是属于服务器层，表级锁。DML、DDL语句都需要获得DML锁，DML操作需要MDL读锁，DDL操作需要MDL写锁。锁的持有周期是事务，一旦事务申请了一个MDL锁，在事务执行完成之前它不会释放锁。

MDL加锁过程是系统自动控制，不能直接干预，读写共享，读写互斥，写写互斥。一旦发生写锁等待，不仅当前操作被阻塞，后续对表的所有操作都会被阻塞。

出现MDL锁之后，因为它会阻塞所有对表的访问，在业务繁忙的时候，可能会导致连接堆积，甚至导致cpu被打满，服务器宕机。

在发现MDL锁后，我们要找到最开始未提交的事务对应的会话，提交事务或把会话kill掉以解决问题。

因为MDL锁极易对系统造成严重伤害，所以我们也需要一些措施来规避MDL锁。比如避免大事务，增加MDL监控等等一些方法。

参考：
https://programmer.group/deep-understanding-of-mdl-metadata-locks.html
https://www.cnblogs.com/chenpingzhao/p/9642732.html
http://mysql.taobao.org/monthly/2015/10/02/
https://segmentfault.com/a/1190000022883552
https://www.modb.pro/db/45669