上一篇《mysql metadata lock(一)》介绍了为什么引入MDL，MDL作用以及MDL锁导致阻塞的几种典型场景，文章的最后还留下了一个小小的疑问。本文将更详细的介绍MDL，主要侧重介绍MDL的原理和实现。一般而言，商业数据库系统实现锁，一般将锁划分为读锁(共享锁)和写锁(排它锁)，为了进一步提高并发性，还会加入意向共享锁和意向排它锁。但是偏偏mysql的MDL搞地比较复杂，但目的也是为了提高并发度。MDL包含有9种类型，详细参考表1。主要其实也是两大类，只是对共享锁做了进一步细分。
一、MDL的锁类型
锁名称 | 锁类型 | 说明 | 适用语句 |
MDL_INTENTION_EXCLUSIVE | 共享锁 | 意向锁，锁住一个范围 | 任何语句都会获取MDL意向锁， 然后再获取更强级别的MDL锁。 |
MDL_SHARED | 共享锁，表示只访问表结构 |
| |
MDL_SHARED_HIGH_PRIO | 共享锁，只访问表结构 | show create table 等 只访问INFORMATION_SCHEMA的语句 | |
MDL_SHARED_READ | 访问表结构并且读表数据 | select语句 LOCK TABLE ... READ | |
MDL_SHARED_WRITE | 访问表结构并且写表数据 | SELECT ... FOR UPDATE DML语句 | |
MDL_SHARED_UPGRADABLE | 可升级锁，访问表结构并且读写表数据 | Alter语句中间过程会使用 | |
MDL_SHARED_NO_WRITE | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。 | Alter语句中间过程会使用 | |
MDL_SHARED_NO_READ_WRITE | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。 | LOCK TABLES ... WRITE | |
MDL_EXCLUSIVE | 写锁 | 禁止其它事务读写。 | CREATE/DROP/RENAME TABLE等DDL语句。 |
表1
二、MDL的兼容性矩阵
| IX | S | SH | SR | SW | SU | SNW | SNRW | X |
IX | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
S | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
SH | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
SR | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
SW | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
SU | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
SNW | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
SNRW | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
X | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
说明：横向表示其它事务已经持有的锁，纵向表示事务想加的锁
三、几种典型语句的加(释放)锁流程
1.select语句操作MDL锁流程
1）Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_READ锁
2）事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_SHARED_READ锁
2. DML语句操作MDL锁流程
1）Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_WRITE锁
2）事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_SHARED_WRITE锁
3. alter操作MDL锁流程
1）Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE锁
2）操作数据，copy data，流程如下：
a) 创建临时表tmp，重定义tmp为修改后的表结构
b) 从原表读取数据插入到tmp表
3）将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
a) 删除原表，将tmp重命名为原表名
4）事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_EXCLUSIVE锁
四、典型问题分析。
一般而言，我们关注MDL锁，大部分情况都是线上出现异常了。那么出现异常后，我们如何去判断是MDL锁导致的呢。监视MDL锁主要有两种方法，一种是通过show processlist命令，判断是否有事务处于“Waiting for table metadata lock”状态，另外就是通过mysql的profile，分析特定语句在每个阶段的耗时时间。
抛出几个问题：
结合第三节几种语句的上锁流程，我们很容易得到这三个问题的答案。语句会在阻塞在具体某个环节，可以通过profile来验证我们的答案是否正确。
第一个问题，当执行select语句时，只要select语句在获取MDL_SHARED_READ锁之前，alter没有执行到rename阶段，那么select获取MDL_SHARED_READ锁成功，后续有alter执行到rename阶段，请求MDL_EXCLUSIVE锁时，就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁，但由于这个过程时间非常短(大头都在copy数据阶段)，并且是alter的最后一个阶段，所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放，若线上存在大查询，或者存在未提交的事务，则会出现ddl卡住的现象。这里要注意的是，ddl卡住后，若再有select查询或DML进来，都会被堵住，就会出现threadrunning飙高的情况。
第二个问题，alter在opening阶段会将锁升级到MDL_SHARED_NO_WRITE，rename阶段再将升级为MDL_EXCLUSIVE，由于MDL_SHARED_NO_WRITE与MDL_SHARED_WRITE互斥，所以先执行alter或先执行DML语句，都会导致语句阻塞在opening tables阶段。结合第一个和第二个问题，就可以回答《mysql metadata lock(一)》的疑问了。
第三个问题，显然，由于MDL_SHARED_WRITE与MDL_SHARED_READ兼容，所以它们不会因为MDL而导致等待的情况。具体例子和profile分析可以参考《mysql metadata lock(一)》。这里我们要考虑一个问题，LOCK TABLE ... READ上的MDL锁是MDL_SHARED_READ，而DML操作上的是MDL_SHARED_WRITE，那么前者和后者如何互斥？其实这个MDL是无能为力的，需要通过SERVER层的table lock来解，可以简单做一个实验，一个会话执行LOCK TABLE test READ，另外一个会话执行update test set xxx where id=xxx，会发现update语句堵塞，通过show processlist查看，状态为:"Waiting for table level lock"。但是如果使用LOCK TABLE test WRITE，则状态变为"Waiting for table metadata lock"，其实此时table lock也是堵住的，只不过MDL在之前挡住了，这说明SERVER层的table lock和MDL在同时起作用。