“Waiting for table metadata lock”，你有没有遇到过等待这个锁？MetaData Lock是个啥锁？
本文中描述的相关知识点，都是基于InnoDB引擎下。
本文中涉及到的MySQL例子中，左侧MySQL连接为事务A，右侧MySQL连接为事务B。
名词解释：
DML：Data Manipulation Language，数据操控语言。用于操作数据库对象中包含的数据，比如Insert、Delete、Update。
DDL：Data Definition Language，数据定义语言。用于操作对象和对象的属性，比如create、drop、alter。DDL不会对具体的数据进行操作。
MDL：MetaData Lock。
什么是MDL
MDL，全称MetaData Lock，是在MySQL 5.5.3引入的，出现的初衷是为了保护一个处于事务中的表结构不被修改，这里的事务包括显式事务和AC-NL-RO（auto-commit non-locking read-only）事务，其中：
- 显式事务
- 关闭AutoCommit下的操作
- 以begain或start transaction开始的操作
- AC-NL-RO
- AutoCommit开启下的select操作
例如：
- RR隔离模式下，会话A开启事务，然后执行SQL查询，查询得到2条记录；
- 会话B使用alter修改表结构，增加一列；
- 会话A使用同样的SQL查询，此时会发现得到0条数据，与可重复读相冲突。
有了MDL的出现，会导致步骤二的时候被阻塞，直到会话A完成事务。
MDL类型
锁一般划分为读锁(S，共享锁)和写锁(X，排它锁)，为了进一步提高并发性，还会加入意向共享锁和意向排它锁。MySQL在MDL上，为了进一步提高并发，设计得比较复杂。
- 按照锁住的对象划分
| 属性 | 含义 | 范围/对象 | 对应的等待信息 |
|---|---|---|---|
| GLOBAL | 全局锁 | 范围 | Waiting for global read lock |
| COMMIT | 提交保护锁 | 范围 | Waiting for commit lock |
| SCHEMA | 库锁 | 对象 | Waiting for schema metadata lock |
| TABLE | 表锁 | 对象 | Waiting for table metadata lock |
| FUNCTION | 函数锁 | 对象 | Waiting for stored function metadata lock |
| PROCEDURE | 存储过程锁 | 对象 | Waiting for stored procedure metadata lock |
| TRIGGER | 触发器锁 | 对象 | Waiting for trigger metadata lock |
| EVENT | 事件锁 | 对象 | Waiting for event metadata lock |
- 按照操作的对象划分
| 锁名称 | 说明 | 适用语句 |
|---|---|---|
| MDL_INTENTION_EXCLUSIVE(IX) | 意向锁 | 任何语句都会获取MDL意向锁，然后再获取更强级别的MDL锁。 |
| MDL_SHARED(S) | 共享锁，表示只访问表结构，不访问数据 | |
| MDL_SHARED_HIGH_PRIO(SH) | 共享锁，用于访问information_scheam |
show create table等只访问INFORMATION_SCHEMA的语句 |
| MDL_SHARED_READ(SR) | 访问表结构并且读表数据 | select语句LOCK TABLE ... READ |
| MDL_SHARED_WRITE(SW) | 访问表结构并且写表数据 | SELECT ... FOR UPDATEDML语句 |
| MDL_SHARED_UPGRADABLE(SU) | 是mysql5.6引入的新的metadata lock，为了online ddl才引入的。特点是允许DML，防止DDL； | alter语句中间过程会使用 |
| MDL_SHARED_NO_WRITE(SNW) | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。 | alter语句中间过程会使用 |
| MDL_SHARED_NO_READ_WRITE(SNRW) | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。 | LOCK TABLES ... WRITE |
| MDL_EXCLUSIVE(X) | 独占锁，禁止其它事务读写。 | CREATE/DROP/RENAME TABLE等DDL语句。 |
其中，锁之间的互斥关系如下（Y为不互斥，N为互斥）：
| S | SH | SR | SW | SU | SNW | SNRW | X | |
|---|---|---|---|---|---|---|---|---|
| S | Y | Y | Y | Y | Y | Y | Y | N |
| SH | Y | Y | Y | Y | Y | Y | Y | N |
| SR | Y | Y | Y | Y | Y | Y | N | N |
| SW | Y | Y | Y | Y | Y | N | N | N |
| SU | Y | Y | Y | Y | N | N | N | N |
| SNW | Y | Y | Y | N | N | N | N | N |
| SNRW | Y | Y | N | N | N | N | N | N |
| X | N | N | N | N | N | N | N | N |
上面的锁类型比较复杂，针对日常使用，我们分析几种典型语句的加/释放锁流程：
- select语句操作MDL锁流程
- opening tables阶段，加共享锁
- 加对象级别的MDL_SHARED_READ锁
- 事务提交阶段，释放MDL锁
- 释放MDL_SHARED_READ锁
- opening tables阶段，加共享锁
DML语句操作MDL锁流程
- opening tables阶段，加共享锁
- 加global类型的MDL_INTENTION_EXCLUSIVE锁
- 加对象级别MDL_SHARED_WRITE锁
- 事务提交阶段，释放MDL锁
- 释放MDL_INTENTION_EXCLUSIVE锁
- 释放MDL_SHARED_WRITE锁
- opening tables阶段，加共享锁
alter操作MDL锁流程
- opening tables阶段，加共享锁
- 加global类型的MDL_INTENTION_EXCLUSIVE锁
- 加对象级别的MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE锁
- 操作数据，copy data，流程如下：
- 创建临时表tmp，重定义tmp为修改后的表结构
- 从原表读取数据插入到tmp表
- rename阶段，将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
- 删除原表，将tmp重命名为原表名
- 事务提交阶段，释放MDL锁
- 释放MDL_INTENTION_EXCLUSIVE锁
- 释放MDL_EXCLUSIVE锁
- opening tables阶段，加共享锁
如上几种典型的语句加锁流程，引出以下几种常见的场景：
select与alter是否会冲突
首先，select语句，全程加的都是S锁，alter语句，在第三步的rename阶段，才会加X锁，因此只要select的执行过程中，不与alter的rename阶段冲突，则不会产生事务执行的冲突，否则会产生锁等待。
开篇中的问题：
有了MDL的出现，会导致步骤二的时候被阻塞，直到会话A完成事务。，是因为开启事务，执行select，事务提交之前执行alter的话，会阻塞在rename阶段，当事务提交之后，则alter语句才能继续执行。DML与alter是否会冲突
DML会在opening tables阶段加MDL_SHARED_WRITE锁，而alter在opening tables阶段，会将锁升级到MDL_SHARED_NO_WRITE锁，而这个锁跟前者的MDL_SHARED_WRITE锁是冲突的，因此DML跟alter会在opening tables阶段冲突。
select与DML是否会冲突
select上的MDL_SHARED_READ锁与MDL上的MDL_SHARED_WRITE锁并不冲突，因此二者不会冲突。
出现MDL的常见原因
长事物运行，阻塞DDL，继而阻塞所有同表的后续操作
长事务的执行，会导致一直持有MDL_SHARED_READ，会阻塞DDL的执行。这种情况应该优化业务，避免长事务的出现。
未提交事物，阻塞DDL，继而阻塞所有同表的后续操作
常见的场景为，在autocommit=0的配置下，某个事务没有显示的执行commit/rollback，导致事务一直未提交，从而导致DDL获取不到独占锁。可以通过
select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。
MDL会等待lock_wait_timeout设置好超时时间，此参数默认值是31536000，即是365天，建议在生产环境修改成较短的时间。不过即使这样，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。