前言

笔者在前几个月前经历了一次因为执行DDL而导致业务疯狂打印error日志的场景，虽然不是笔者执行但是这一次经历也警惕了笔者需要敬畏生产、明确原理后再操作的习惯。希望这个亲身经历能够帮助初入职场的同学们避免因为这种问题而导转正失败的问题😊

背景

阐明背景

• 表：大表
• DDL语句：添加覆盖索引
• 执行时间：晚上低峰期（企业中对一些操作有严格的时间把控，必须要到业务低峰期才能执行）

直接影响

• 上层业务疯狂打印error日志（日志在企业是非常重要的）
• 告警电话疯狂diss你
• 表对外展示出来就是不可用，类似宕机（读和写统统被超时拒绝）

可能你会说只有大表才会有这种问题吧，我如果只是几十万、几百万数据的表执行这个应该没有问题吧。那我们得分清楚场景才行，在最后的复盘中发现这个问题的最终元凶是MDL锁，和数据量有关系但不能说是因为数据量大才会发生。

基础知识

先来说一下MySql常见的锁有哪些？

• 全局锁
• 表锁：普通表锁和MDL锁
• 行锁（InnoDB引擎支持）

这里我们着重说明MDL锁，又叫做元数据锁。其目的是保证表结构发生变更时不能影响执行执行中的读取的事务，总不能我开启一个事务读着读着发现表没了，这样我相信会有别的数据库来替代MySql，成为大家的新欢。

所以在开启事务执行MDL、DDL语句时都需要先获取MDL锁（读锁或者写锁）

读锁之间互不互斥、读写锁之间互斥、写锁之间互斥

能否获取到读锁需要看前面的操作是否存在写锁，如果存在则不能获取只能进入等待状态；而能否获取到写锁需要看前面有没有锁的存在（这一点和ZooKeeper实现分布式锁非常类似，有兴趣可以看ZooKeeper是如何实现分布式锁的）

动手模拟

基于这个原理我们可以自己mock一下笔者的场景

create table t_user
(
    id   bigint(255) unsigned auto_increment comment '主键'
        primary key,
    name varchar(100) null comment '姓名',
    age  int unsigned null comment '年龄'
);

事务 1:读取表

事务 2:给表添加索引

事务 3:再次读取表数据

如果将事务 A 提交或者回滚后，事务 B 将不会被阻塞，但是事务 C 会因为前面的事务 B（写锁）而被阻塞

如果将事务 B 提交或者回滚后，事务 C 将正常执行

锁只有在事务提交或者回滚后才会释放

这种场景很像使用 ZooKeeper 来实现分布式锁的场景，有兴趣可以看看（需要注意 ZooKeeper 是如何避免惊群效应的）

发现一个有意思的点：对索引的操作是没办法 rollback 的，原因是开销太大了

所以 MDL 导致的事务被阻塞与表的大小是没有关系的，而是与执行顺序和并发程度有关系的。

如果想用这种方式安全的去操作索引又该怎么做呢？开启一个连接，并对这个连接设置超时等待时间

-- 1. 开启一个事务并设置超时时间
SET SESSION lock_wait_timeout = 50;  -- 设置50秒超时
BEGIN;

-- 2. 尝试获取MDL写锁
ALTER TABLE users ADD INDEX idx_name(name);

-- 可能的结果：
-- a. 50秒内获得锁：继续执行
-- b. 50秒后超时：
--    ERROR 1205 (HY000): Lock wait timeout exceeded

-- 3. 如果成功获得锁，继续执行
-- 如果失败，事务会自动回滚

-- 4. 最后提交或回滚
COMMIT;  -- 或 ROLLBACK;

上面这个其实和我们现在的分布式锁思路一致，不能一直加锁，一定要设置超时时间。换句话说这个就是用轮训去替代阻塞在 Java 的并发编程中也有这样的思想（CAS、CompletableFuture...）

如何安全的执行DDL

除了上面说的设置超时等待时间之外，MySql5.6 之后还支持 online DDL

MySQL 5.6+ Online DDL 原理总结

关键机制

• Row Log（变更日志）：记录DDL期间的MDL变更，保证数据一致性。
• INPLACE算法：原地变更，避免全表重建。
• 短暂加锁：只在DDL开始和结束时短暂加排他锁，大部分时间允许并发操作。

支持的典型操作

• 添加/删除索引
• 修改列默认值
• 重命名列
• 部分字段变更

不支持的操作

• 修改主键
• 修改字符集
• 某些复杂字段类型变更

太难了我不允许 online DDL

示例

ALTER TABLE t_user ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

优势

• 极大减少业务阻塞时间
• 提高表结构变更的可用性和安全性
• 支持大部分常用DDL的在线变更

总结与收获

1. MDL锁的原理与目的
2. 如何安全的执行DDL锁

期望

下面大家可以发布在评论区，集思广益扩展知识面

1. ZooKeeper的分布式锁和MDL判断是否能加锁的逻辑非常类似，可以看一下ZooKeeper如何实现分布式锁加深对MDL排队的一个理解
2. 超时等待是一个很不错的思想，在分布式锁和Java的并发包中有很多地方在使用
3. 用轮训去替代阻塞是一个不错的点子，在现代计算机中已经有很多地方在使用

絮絮念

笔者的文笔着实烂，希望大家能嘴下留情，如果有错误点或者待补充点大家都可以发表。笔者的抗压能力MAX，纸上得来终觉浅，笔者也没有补充上述的mock场景如何验证，希望大家可以自己验证一下😂

后续补充

2025-06-04 InnoDB在RC和RR的事务隔离级别下支持快照读和当前读，在上面笔者mock的场景中使用的应该是快照读。既然是快照读还需要加MDL读锁吗？是需要的，因为可以理解为快照读只是说不用加行锁，但是依旧会受到全局锁和表锁的影响，假设它不受上述MDL锁的影响也就不会发生线上问题了