《MySQL实战45讲》读后感 06|全局锁和表锁：给表加个字段怎么有这么多阻碍

收获到的知识点

MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

加全局锁的方法一

flush tables with read lock (FTWRL)

当你需要整个库处于只读状态的时候，可以使用这个命令，之后其它的线程会被阻塞：比如dml（数据的增删改）、ddl（建表、修改表结构）

全局锁的使用场景

做全局逻辑备份，也就是把整个库每个表都select出来存成文本

全局锁后整库只读的风险点

如果在主库上备份，那么备份期间都不能进行更新操作，业务基本上就得罢工
如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

全局锁既然存在风险，那么备份时一定要加全局锁吗？

假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。
现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。
如果时间顺序上是先备份账户余额表 (u_account)，然后用户购买，然后备份用户课程表 (u_course)，会怎么样呢？你可以看一下这个图：

可以看到，这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。
作为用户可别觉得这样可真好啊，你可以试想一下：如果备份表的顺序反过来，先备份用户课程表再备份账户余额表，又可能会出现什么结果？
也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

官方自带的逻辑备份工具mysqldump,当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，而由于MVCC的支持，这个过程中数据是可以正常更新的。

Q: 既然官方自带的逻辑备份工具mysqldump可以解决备份时的数据一致性问题，为什么还需要flush tables with read lock (FTWRL)呢
A：这种方式虽好，但前提是引擎要支持这个隔离级别，比如MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏备份的一致性，这时就需要使用FTWRL了

加全局锁的方法二

set global readlonly=true

这种方式也可以让全库进入只读状态，但是作者还是建议大家使用FTWRL，主要有以下2个原因：

• 有些系统中，readonly的值会被用来判断其它逻辑，比如用来判断一个库是主库还是备库，因此，修改global变量的方式影响面更大
• 在异常处理机制上有差异，如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

MySQL里面表级锁有2种：表锁和元数据锁（meta data lock,MDL）

加表锁的方法

语法格式：lock tables ... read/write 
实际示例：lock tables t1 read , t2 write;

与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

加元数据锁的方法

元数据锁（meta data lock,MDL）不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
MySQL5.5版本中想入了MDL，当对一个表做增删改查的时候，加MDL读锁； 当对一个表做结构变更操作的时候，加MDL写锁。

• MDL读锁不互斥，多个线程间可以对同一个表进行增删改查
• MDL读锁与写锁之间是互斥的， MDL写锁与写锁之间也是互斥的，因此当两个线程要同时对一张表加字段时，其中一个需要等另一个执行完才能开始执行。

课堂问题

Q: 当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

A: 作者回复
假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

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 */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；
设置一个保存点，这个很重要（Q3）；
show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6。当然这部分属于“超纲”，上文正文里面都没提到。
DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：
如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

顺便分享一下林奇大师的课程，有兴趣的可以看看