如果要给一个大表加字段，你一般都会非常谨慎小心，以免对线上业务造成影响，但实际上给一个小表加字段不慎操作也会导致线上业务出问题，这篇文章主要学习一下 MySQL 中 MDL 锁对性能的影响，防止采坑。
1.笔记图
2.全局锁
- 全局锁就是对整个数据库实例加锁
- 命令：
Flush tables with read lock (FTWRL)
- 全局锁现象：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句会被阻塞
3.数据逻辑备份的方式
- 加全局锁(MyISAM)：
- 假设现在要维护购买系统，用户账户余额表和用户课程表
- 发起一个逻辑备份，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点
- 通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份
- 如果在主库上备份，备份期间都不能执行更新
- 如果在从库上备份，备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟
- 备份为什么要加锁？：
- 事务(InnoDB)：
- 方自带的逻辑备份工具是 mysqldump
- mysqldump 使用 single-transaction 参数，导数据之前就会启动一个事务，拿到一致性视图
- 由于 MVCC 的支持，这个过程中数据是可以正常更新的
4.表级锁
- 表锁：
- 如果在某个线程 A 中执行 lock tables t1 read, t2 write 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞
- 同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作，不能访问其他表
- 对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发
- 可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放
- 加表锁的语法是 lock tables … read/write
- 元数据锁（meta data lock，MDL)
- 如果一个查询正在遍历一个表中的数据，执行期间另一个线程对这个表结构做变更，删了一列，查询线程拿到的结果跟表结构对不上，肯定是不行的
- 在访问一个表的时候会被自动加上 MDL 锁，其作用是保证读写的正确性
- 当对一个表做增删改查操作的时候，加 MDL 读锁，读锁之间不互斥，可以有多个线程同时对一张表增删改查
- 当要对表做结构变更操作的时候，加 MDL 写锁，读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
5.给一个小表加个字段，导致整个库挂了
- session A 先启动，这时候会对表 t 加一个 MDL 读锁
- session B 需要的也是 MDL 读锁，因此可以正常执行
- session A 的 MDL 读锁还没有释放，session C 需要 MDL 写锁，因此只能被阻塞
- 之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞，所有对表的增删改查操作都需要先申请 MDL 读锁，这个表此时完全不可读写
6.如何安全地给小表加字段？
- 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放
- 首先要解决长事务，事务不提交，会一直占着 MDL 锁，如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务
- 热点表请求很频繁，不得不加个字段
- 在 alter table 语句里面设定等待时间，在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃
- 再通过重试命令重复这个过程
- MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法
ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...
7.课后问题
当备库用 single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？
- 从库备份过程中几个关键的语句：
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT； /* other tables */ Q3:SAVEPOINT sp; /* 时刻 1 */ Q4:show create table `t1`; /* 时刻 2 */ Q5:SELECT * FROM `t1`; /* 时刻 3 */ Q6:ROLLBACK TO SAVEPOINT sp; /* 时刻 4 */ /* other tables */
Tips：Q1 表示为了确保 RR（可重复读）隔离级别，设置 RR 隔离级别 (Q1)，Q2 表示 WITH CONSISTENT SNAPSHOT 这个语句执行完就可以得到一个一致性视图 （Q2)，Q3 表示设置一个保存点，Q4 表示show create 是为了拿到表结构，Q5 表示正式导数据，Q6 表示回滚到 SAVEPOINT sp，作用是释放 t1 的 MDL 锁。
- 如果主库 DDL 命令在 Q4 之前到达从库，没有影响
- 如果主库 DDL 在 时刻2 到达从库，此时会有 MDL写锁，会造成 Q5 执行失败，备份过程被终止
- 如果主库 DDL 在 时刻3 到达从库，由于备份过程占用读锁，会导致 binlog 复制被阻塞，会造成主从延迟，直到备份完成 Q6 执行之后从库才能继续复制主库 binlog
- 如果主库 DDL 在 时刻4 到达从库，没有影响
