1
最近遇到一次metadata lock导致大量线程卡住的问题，比较有代表性，撰文一篇，希望对广大DBA们有帮助。
同事反馈说在使用pt-online-schema-change添加索引的时候，一直卡住，而且会卡住很久。
登录服务器，使用show full processlist命令查看线程状态，发现大量的Waiting for table metadata lock，如下图：
找到等待时间最长Waiting for table metadata lock的SQL，正是同事添加索引的时候创建触发器的SQL，如下图：
初步确定原因是创建触发器的时候，有某个事务持有metadata lock没有释放，导致创建触发器的SQL被阻塞，进而导致后面关于t_log_sp_detail_10的所有操作都被阻塞。
解决办法，终止添加索引的操作，kill掉创建触发器的线程，业务恢复。
2
这里面引申出2个问题：
1、是什么SQL持有metadata lock那么长的时间一直不释放？
2、为什么metadata lock那么长的时间都不超时？
下面就来寻根问底：
我们的环境是5.7，在MySQL 5.7中，可以通过查看performance_schema.metadata_locks来查看对应metadata lock的情况，开启方式如下：
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
不过在performance_schema.metadata_locks表中并没有记录对应的线程ID，需要结合performance_schema.INNODB_TRX来进一步分析。
但是，那台机器关闭了performance_schema，如果要启用的话必须重启实例，因此只能使用笨一点的办法。
只要抓取对应的信息就能快速定位，这些信息包括：
（1）、开启general日志（短暂开启）
（2）、抓取show full processlist的信息（每隔1秒）
（3）、抓取performance_schema.INNODB_TRX的信息（每隔1秒）
通知同事，继续使用pt添加索引，重现问题。
问题重现后，查看抓取到的performance_schema.INNODB_TRX信息，找到对应事件正在进行的事务，截图如下：
查看general日志对应线程做了什么事情：
查看抓取到proocesslist信息，发现这个线程是sleep的状态，问题很明显是这个线程没有提交导致。
和业务沟通后，kill掉该线程，索引正常添加。
第1个问题答案是select线程没有提交导致。
再来看第2个问题
之所以等待那么久都没有超时，是因为metadata lock不是InnoDB引擎层的锁，而是server层的锁，控制锁超时的参数不一样，如下：
上面的截图中innodb_lock_wait_timeout是控制InnoDB引擎层的锁超时，而lock_wait_timeout是控制server层的锁超时。
而上图中，lock_wait_timeout设置得很大，可以调小。
3
回答完上面的2个问题，我们再来深入一点了解一下metadata lock这个东东。主要从3个方面来看：
一、为什么要引入metadata lock
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
上面的描述可能不是特别好理解，来举一个之前姜老师说过的例子，这个例子就能很形象地说明为什么要引入metadata lock。看如下两个session
上面的例子中，如果没有metadata lock的保护，会导致SESSION 2顺利执行，SESSION 1出错。
在5.5.3版本加入metadata lock以后，由于SESSION 1先查询了TABLE1，持有TABLE1的metadata lock，会导致SESSION 2等待，直到SESSION 1提交。
正因为如此，MySQL在5.5.3版本后引入了Metadata lock锁，事务释放后才会释放Metadata lock，因此，在事务完成之前，DDL是无法执行的。
有兴趣的还可以看看这个典型的bug。
二、什么场景下会引起metadata lock
有如下几种场景会引起metadata lock锁等待问题
1、有长时间运行的DML语句
这种情况会导致metadata lock等待，在工作中如果要对某个表进行DDL操作之前，需要先用show processlist看看是否有长时间运行的SQL，防止出现线上故障。
2、有未提交的事务
这个就是我们开篇遇到的问题，不管是select还是update，如果没有提交都会造成metadata lock等待。
3、在执行期间失败的语句，不会立即释放metadata lock
官网是这么说的：
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
举个例子就很容易理解了，比如我开启一个事务，查询一个不存在的事务，索然语句执行失败，但是持有的metadata lock并没有释放：
session1执行如下操作：
session 2执行添加字段的DDL，session2就一直在等待中，直到session1提交
三、如何快速解决metadata lock引发的锁等待问题
从上面原理和场景中，我们可以看到基本就两种情况。
一种是有长时间运行的DML语句的时候，这种情况要么kill掉DDL语句，要么就kill掉长时间运行的DML。
另外一种情况是未提交的事务（正常的语句和失败的语句），这种情况要解决稍微复杂一点，需要找到对应没有提交的线程进行kill，或者kill掉DDL语句。最快速的解决办法就是将所有sleep的线程都杀掉。
备注：将所有sleep的线程都杀掉这个操作会导致没有提交的事务回滚，是有风险的，请根据业务场景进行操作。
延展阅读：https://www.cnblogs.com/zengkefu/p/5690385.html 【姜老师的文章】
四、参考资料：
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
网友评论