MDL锁与ONLINE DDL

前言

最近在进行数据库变更（扩展VARCHAR）的时候锁表了，原本以为ONLINE DDL不会锁表，没想到还有很多细节；

MySQL为了在并发环境下维护表元数据的数据一致性，在表上执行事务（显式或隐式）的时候，不可以对表元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

MDL (metadata lock) 是表级锁，在访问一个表时会自动被加上：当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

一个事务中的MDL，在语句执行时申请，在事务提交时释放。所以要避免长事务一直占着MDL，如果长事务A加了读锁，然后B是个DDL需要写锁，B就会被A阻塞，再然后所有读锁都会被B阻塞，等于直到A结束前这个表就完全不可读写了。

ONLINE DDL：在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问。

那就奇怪了，alter这种要加MDL写锁的，咋还能增删改呢，增删改不是要MDL读锁吗，这俩互斥啊。原来为了实现Online DDL，这个MDL在真正拷贝数据之前就退化成读锁了。最耗时的拷贝数据阶段可以接受增删改，真正锁住的时间很短，可以认为是Online的。

ONLINE DDL全过程：

排他-MDL锁就是写锁，可以看到Online DDL过程中占用exclusive MDL的步骤很少很快，几乎不会阻塞DML语句。
在DDL执行前或执行时，其他事务可以获取MDL。由于需要用到写锁，所以必须要等到其它占有MDL的事务提交或回滚后才能执行上面两个涉及到 MDL的地方，注意这里就会出现锁表的情况。

ONLINE DDL的用法：

alter table …. , ALGORITHM [=] { INPLACE | COPY | DEFAULT}, LOCK [=] { DEFAULT | NONE | SHARED | EXCLUSIVE }

ALGORITHM：
- INPLACE：DDL的过程中不发生表拷贝，过程中允许并发执行DML；
- COPY：采用表拷贝的方式进行，过程中会阻塞所有的DML；
- DEFAULT：让MySQL以尽量保证DML并发操作的原则选择执行方式；
LOCK：
- NONE：不添加锁，既允许查询操作，也支持数据库变更操作，该模式下并发最好；
- EXCLUSIVE：对整个表格添加X锁，不允许查询和修改，适用于需要尽快完成DDL或者服务库空闲的场景；
- SHARED：对整个表格添加S锁，允许查询操作，但是不支持修改；
- DEFAULT：让MySQL根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值；

并不是所有的DDL都能用INPLACE，具体的支持情况可以在官方文档中查看；要注意版本不同有时会有很大不同，比如扩展VARCHAR长度：

在5.6只能用COPY；
在5.7上，VARCHAR列所需的长度字节数必须保持相同，对于VARCHAR大小为0到255个字节的列，需要一个长度的字节来编码该值。对于VARCHAR 大小为256字节或更大的列，需要两个长度的字节：
- 从<256个字节到>=256个字节，所需的长度字节数从1更改为2，仅支持COPY；
- 从>=256字节变更大，可以INPLACE；

场景一：长事务运行，阻塞DDL，继而阻塞所有同表的后续操作：
- show processlist看到表上有操作；可以找到Waiting for table metadata lock的DDL session，并kill掉；
场景二：事务未提交/回滚(比如查询完成后未提交或者回滚），阻塞DDL，继而阻塞所有同表的后续操作：
- show processlist看不到表上有操作，但实际上存在有未提交的事务；在information_schema.innodb_trx中找到未提交的事务并kill掉；
场景三：表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住：
- show processlist看不到表上有操作，在information_schema.innodb_trx中也没有任何进行中的事务，可能是语句失败了但是任获取到了MDL锁，可以在performance_schema.events_statements_current中找到sid并kill掉；

总之，alter table的语句是很危险的：