metadata lock为何物？好吃吗？

metadata lock这家伙中文名叫元数据锁，在MySQL5.5中引入。我们都知道MySQL中有表锁，行锁，页锁，各自的级别分别是表，行，页。而元数据锁只对表的元数据启用。当有线程开始使用表的时候，就会对该表所有元数据上锁，即metadata lock。

来个实际的例子：

--session 1开启一个查询test表的事务，不提交
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
|   1 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
| 200 |
| 202 |
| 203 |
| 300 |
| 301 |
+-----+
14 rows in set (0.00 sec)

--session 2在session 1未提交的情况下对test表增加一列id2，hang住
mysql> alter table test add id2 int;

--session 3在session 2 hang住的情况下查询test表，hang住
mysql> select * from test;

乍一看，大吃一惊，session 2 hang住还可以理解，session 3也hang住，查询都不行了？
看一下这三个会话之间到底在做什么：

+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+------------------------------+
| Id | User            | Host      | db                 | Command | Time  | State                           | Info                         |
+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+------------------------------+
|  4 | event_scheduler | localhost | NULL               | Daemon  | 19991 | Waiting on empty queue          | NULL                         |
| 13 | root            | localhost | test               | Sleep   |   260 |                                 | NULL                         |
| 14 | root            | localhost | test               | Query   |   251 | Waiting for table metadata lock | alter table test add id2 int |
| 15 | root            | localhost | performance_schema | Query   |     0 | starting                        | show full processlist        |
| 16 | root            | localhost | test               | Query   |   243 | Waiting for table metadata lock | select * from test           |
+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+------------------------------+

可以看到session 2和session 3都在Waiting for table metadata lock，原来session 3发起查询也是需要申请metadata lock的。那么会话之间的关系可以大概估计是session 2是被session 1阻塞肯定没错，session 3被session 2阻塞也是肯定没错的，因为查询之间是不可能互相阻塞的：

--session 2取消操作
mysql> alter table test add id2 int;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

--session 3马上显示查询结果，证明session 3是被session 2阻塞的
mysql> select * from test;
+-----+
| id  |
+-----+
|   1 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
| 200 |
| 202 |
| 203 |
| 300 |
| 301 |
+-----+
14 rows in set (7 min 29.22 sec)

那么问题又来了，为什么session 2申请metadata lock会被session 1阻塞，而session 3申请metadata lock却那么顺利呢？

答案是metadata lock分为shared metadata lock和exclusive metadata lock，从兼容上跟S和X锁之间的关系是一样的，shared metadata lock和shared metadata lock之间兼容，exclusive metadata排斥任何metadata lock。

那么很明显的，session 1发起查询，在test表上了S metadata lock，而session 2需要修改元数据，需要上的X metadata lock被阻塞了，只有session 1的S metadata lock释放了才能申请得到，而这时session 3发起查询需要在test表上S metadata lock，因为此时test表上已有session 2在等待X metadata lock，所以session 3被session 2阻塞了。那么为什么不让session 2直接上读锁呢？其实如果有源源不断的读锁，DDL就永远无法执行了。

关于metadata lock的信息，还可以在performance_schema.metadata_locks表查看，例如上述三个会话的等待关系如下：

mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS from metadata_locks where object_name='test';
+-------------+---------------+-------------+-------------------+---------------+-------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS |
+-------------+---------------+-------------+-------------------+---------------+-------------+
| TABLE       | test          | test        | SHARED_READ       | TRANSACTION   | GRANTED     |
| TABLE       | test          | test        | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     |
| TABLE       | test          | test        | EXCLUSIVE         | TRANSACTION   | PENDING     |
| TABLE       | test          | test        | SHARED_READ       | TRANSACTION   | PENDING     |
+-------------+---------------+-------------+-------------------+---------------+-------------+
4 rows in set (0.00 sec)

从LOCK_TYPE和LOCK_STATUS可以看出，第一行即是session 1，第三行是session 2，第四行是session 3。

同时发现一种新类型SHARED_UPGRADABLE。这个metadata lock类型得从online DDL说起。简单地说，online DDL分为三个阶段：
1.初始化阶段，会在表上一个shared upgradeable metadata lock；
2.执行阶段；
3.提交阶段，此阶段会将shared upgradeable metadata lock升级为X metadata lock。

这样可以看出，metadata lock之间的兼容性，因为session 2已经申请得到了shared upgradeable metadata lock，但在等待X metadata lock，即S与S兼容，X排斥任何类型锁。

那么开头的例子，整个锁等待的过程是这样的：

--session 1开启一个查询test表的事务，不提交。此时session 1持有S metadata lock
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
|   1 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
| 200 |
| 202 |
| 203 |
| 300 |
| 301 |
+-----+
14 rows in set (0.00 sec)

--session 2在session 1未提交的情况下对test表增加一列id2，hang住。此时session 2已经申请了shared upgradeable metadata lock，但在申请X metadata lock时被session 1阻塞。
mysql> alter table test add id2 int;

--session 3在session 2 hang住的情况下查询test表，hang住。此时session 3想要申请S metada lock，但被session 2阻塞。
mysql> select * from test;

最后，想要session 2和session 3的事务都能正常执行，只要将session 1提交即可。也是提醒了各位写代码的时候，别忘了commit哦。