一. 简介

DQL：指数据库中的查询（select）操作。
DML：指数据库中的插入（insert）、更新（update）、删除（delete）等 行数据变更 操作。
DDL：指数据库中加列（add column）、修改列（change column）、创建索引（create index）、删除索引（drop index）、删除表（drop table）、清理表（truncate table）等 表结构定义 操作。

经常有同学会碰到索引加不上，或者drop table卡住等DDL执行问题，很想和他们解释背后原理，但是三言两语解释不通，所以写了这篇文档。

本篇主要介绍下MySQL中DDL操作背后的锁原理，希望通过阅读这篇文档，你可以掌握如下两点：

了解MySQL中的DDL锁机制，理解 不要在业务期间进行DDL操作 这句话的背后原理。

从MySQL5.5版本开始引入了 MDL锁 ，全称为metadata lock，即 元数据锁 。MDL锁的主要作用是维护表元数据的数据一致性，当表上有 活动事务（注意MDL锁伴随事务提交而释放，而不是SQL结束而释放） 的时候，不可以对 元数据（即表结构） 进行任何修改操作。

PS： MDL共享锁 = MDL读锁 = MDL S锁 ， MDL排他锁 = MDL写锁 = MDL X锁 ，下文中叫法不同，但是含义一致。

粗略画了下DDL与DML（这里其实也包括DQL）的锁申请过程，大家可以对照着这幅图来理解MDL锁，简单来说， DDL操作 会申请对应表上的的 MDL X锁 ，这把锁是 排他锁 ，一旦申请成功，该表上的其他所有操作都无法进行（包括DDL、DML、DQL），因为无法再申请到该表上的MDL锁，直到DDL操作申请的MDL X锁释放为止。

而 DML或DQL操作 都只会申请 MDL S锁 ，而S锁为 共享锁 ，可以支持并发访问，因此大量相同表上的增删改查操作是可以并发执行的。

这里还需要了解的一点是，MDL锁申请遵循一个 队列机制 ，即先到先得，因此如果一个DDL操作一直无法得到MDL X锁，那么后续所有该表上的SQL都会等待这个DDL操作拿到MDL X锁并且释放为止，这也是为啥我们经常听到 不要在业务期间进行DDL操作 的原因之一，DDL操作很容易因为某个慢SQL导致后续所有的SQL都被卡住（等待MDL锁）。

MySQL 5.6 Online DDL推出以前，执行DDL主要有两种方式 copy方式 和 inplace方式（只支持添加、删除索引） ，DDL执行期间会全程锁表，无法同时进行DML，实用性很低。

copy：建一张新表结构的临时表，锁原表禁止DML，然后拷贝数据到临时表，升级字典锁，禁止原表读写，进行rename操作，完成DDL。

inplace ：在进行DDL操作时， MDL写锁会降级为MDL读锁 ，这样就可以 支持并发DML ，然后通过row_log记录原表上的DML增量操作，最后通过回放增量数据保证数据一致性。

rebuild table：部分DDL操作类型在inplace模式下，需要进行 重建表（原表基础上进行更新） ，往往表越大越 费时 。

整个Online的实现主要依赖于row_log记录DDL期间的DML增量日志，这样就可以不用一直占用MDL X锁，而只需要占用一瞬间，期间主要持有MDL S锁即可。

值得注意的是，Online DDL前后需要申请两次MDL X锁，虽然持有时间非常短，如果存在慢SQL的话，还是会引起大量MDL锁等待的问题。

为了方便大家更好的理解MDL锁，我在dbeaver中进行了如下几个MDL锁模拟实验。

模拟过程： 模拟执行一个慢查询（通过sleep函数），然后进行表上的加列操作，查看DDL状态 。

通过dbeaver中的会话管理窗口可以很方便的看到MySQL中的SQL运行状态。

我们先后对 emp表 执行了慢查询与加列操作，通过会话窗口可以看到加列的DDL操作处于 Waiting for table metadata lock 状态，这个状态其实就是处于MDL锁等待，DDL操作因为没有申请到MDL X锁（因为慢查询占了MDL S锁并且一直没释放所致），所以一直处于等待状态，并不在真正运行。

过了一会当DDL操作完成后，想要从MDL读锁升级到MDL写锁进行COMMIT时，发现无法申请到，因为这时候SELECT查询还占用着MDL读锁，所以处于 Waiting for table metadata lock 状态。这时候如果没有外界干预，要么等待120秒后DDL超时回滚，要么等待SELECT在超时期间内结束，释放MDL锁，这样就可以成功完成DDL操作。

MySQL中DDL操作通过MDL这把锁来保证了表结构与表数据的一致性，而Online DDL特性则使得DDL使用更加方便与轻量。

大家在进行DDL操作后，一定要确认DDL是否处于真正的ALTER状态，还是等待MDL锁的状态，如果是等待MDL锁，则需要找到对应占用MDL锁的会话（ 通常都是一个或多个对应表上执行很慢的SELECT查询 ），这时候可以判断是否进行KILL来让DDL正常执行。

最后提醒大家， 业务高峰期千万不要进行核心业务表的DDL操作 ，保不齐MySQL里就存在该表上的一条运行比较慢的SQL或挂起的事务，那么就非常容易引起连锁的MDL锁争用问题，对应表上的业务均会瘫痪掉，连接数暴涨，最后连接池连接达到上限，整个系统也会瘫痪掉。

【科普】系列文章主要内容为 总结整理数据库方面的常用知识及背后原理 ，面向所有开发与运维人员，希望以此来增加数据库的知识网络，更好的在项目上使用与管理好数据库。