想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，导致了我们在show processlist 时，看到线程的状态是在等metadata lock。本文会对mysql 的metadata lock做一个小小的总结，希望对大家有所帮助。
MDL是在5.5才引入到mysql，之前也有类似保护元数据的机制，只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是，5.1对于元数据的保护是语句级别的，5.5对于metadata的保护是事务级别的。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放MDL。
引入MDL后，主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
5.5以后，什么情况下会碰到MDL锁，我结合实际情况举3个会出现MDL的场景，来分析MDL加锁时机。下文的.测试都是以mysql 5.5这个版本为基准，没有考虑到online ddl，下一篇博文会详细介绍5.6的online ddl。
1.大查询或mysqldump导致alter等待MDL
时间点 | 会话A | 会话B | 会话C |
1 | Select count(*) from t; |
|
|
2 |
| alter table t add column c3 int; |
|
3 |
|
| Show processlist； B：copy to tmp table |
4 |
| 阻塞 | Show processlist; B：Waiting for table metadata lock
|
5 | A：执行完毕 |
|
|
6 |
|
| Show processlist; B：rename table |
7 | Select count(*) from t; |
|
|
8 |
| B：执行完毕 |
|
9 |
|
| Show processlist;
A: Sending data
|
10 | A:执行完毕 |
表1
从表1可以看到，会话A先执行select ,B后执行alter,在会话A执行完毕前，会话B拿不到MDL锁，从表格上面来看，主要阻塞在rename阶段。A会话在时间点5执行完毕后，会话B拿到MDL锁，变为rename table状态，这个操作持续时间非常短，时间点7，A会话再次执行查询，当B执行完后，此时A正常执行。这说明对于MDL锁而言，select会阻塞alter，而alter不会阻塞select。在rename的瞬间，alter是会阻塞select的，详细请参考《mysql metadata lock(二)》
2.表上存在未提交的事务，导致alter等待MDL
时间点 | 会话A | 会话B | 会话C |
1 | set autocommit=0; update t set c2='9999' where c1=4; |
|
|
2 |
| alter table t drop column c3; |
|
3 |
|
| Show processlist; B：Waiting for table metadata lock |
4 | A：提交事务 commit |
|
|
5 |
|
| Show processlist； B：copy to tmp table |
6 |
| B：继续执行 |
|
7 | update t set c2='9999' where c1=4;阻塞 |
|
|
8 |
|
| Show processlist; A: Waiting for table metadata lock B: copy to tmp table |
9 |
| B执行完毕 |
|
10 | A执行完毕 |
|
|
表2
从表2可以看到，会话A第一次执行update语句后，未提交，导致后面会话B执行alter语句时需要等待MDL锁；时间点4，A会话提交事务，此时会话B获取MDL锁，开始执行；时间点7，A会话再次发起update操作，此时A会话被阻塞住，这说明对于MDL锁而言，update会阻塞alter，同样alter也会阻塞update。
PS：时间点3由于通过show processlist只看到alter被阻塞了，但不清楚被谁阻塞，可以通过查看information_schema.innodb_trx可以找到活动的事务。
3.这种情况是第1种情况的特例，存在一个查询失败的语句，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。
时间点 | 会话A | 会话B | 会话C |
1 | Start transaction； Select c99 from t； Unknown column 'c99' in 'field list' |
|
|
2 |
| alter table t drop column c3; |
|
3 |
|
| Show processlist； B：copy to tmp table |
4 |
|
| Show processlist; B：Waiting for table metadata lock |
5 | A：提交事务 commit |
|
|
6 |
| 执行完毕 |
|
表3
这里注意时间1，会话A要显示开启一个事务，否则查询会隐式回滚结束，无法重现上面的场景。会话B执行alter后，没有立即阻塞住，而是立马开始copy to tmp table，这个过程结束后，才进行了MDL锁等待。这怎么解释呢，应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤，在这种情况下，到最后一步才需要MDL锁，所以copy过程中不会阻塞。由于没有查询在进行，而且查询也没有进入innodb层 (失败返回)，所以show processlist和information_schema.innodb_trx没有可以参考的信息。
这里有一个小疑点，对于第二种情况，alter在开始时就立马堵住了，第一种和第三种情况是copy结束后，才堵住。通过多次实验，确实发现第二种情况在opening tables就堵住了。为什么要这样具体原因还没弄清楚，有兴趣的同学可以去debug源码看看究竟。
root@chuck 11:57:41>show profile for query 4;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000050 |
| checking permissions | 0.000004 |
| checking permissions | 0.000005 |
| init | 0.000007 |
| Opening tables | 19.068828 |
| System lock | 0.000011 |
| setup | 0.000034 |
| creating table | 0.005047 |
| After create | 0.000056 |
| copy to tmp table | 89.574539 |
| rename result table | 1.101672 |
| end | 0.000040 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000021 |
| logging slow query | 0.000002 |
| logging slow query | 0.000090 |
| cleaning up | 0.000004 |
+----------------------+-----------+
参考：
http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/
http://ctripmysqldba.iteye.com/blog/1938150