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

06|全局锁和表锁：给表加个字段怎么有这么多阻碍

请支持正版：MySQL实战45讲

根据锁的范围，MySQL里的锁大致可以分为全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是：

Flush tables with read lock(FTWRL)

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句(增删改)、数据定义语句(包括建表、修改表结构)、和更新类事务的提交语句

全局锁的典型使用场景是：做全库逻辑备份，也就是把整个库每个表都select出来存成文本

以前有一种做法：通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份，注意，在备份的过程中整个库完全处于只读状态

但是让整个库都只读，听上去就很危险：

• 如果在主库上备份，那么备份期间都不能执行更新，业务基本暂停
• 从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

看来加全局锁并不太好，但是细想一下，备份为什么要加锁呢？

我们来看看不加锁会有什么问题

假设你现在要维护“某电影院”的购买系统，关注的是用户账户余额表和用户订单表

现在发起一个逻辑备份，假设备份期间，有一个用户，它购买了一个电影，业务逻辑里就要扣除掉他的余额，然后往已订电影里加上一项

如果时间顺序上是先备份账户余额表，然后用户购买，然后备份用户订单表，结果会导致，余额没有变化，然是订单却多了一项，相当于白嫖了。如果用户发现了，那么它会认为它赚了，但是万一备份表的顺序反过来，那可就是余额扣了，但是单子却没有

也就是说，不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

这么一说，你可能就会想到可重复读的隔离级别下开启一个事务，如果对可重复读不熟悉，可以看看事务隔离：为什么你改了我还看不见？

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

这时候你可能会疑惑，既然有了这个功能，为啥还要FTWRL呢？答案是：一致性读是好，但是前提是引擎要能够支持这个隔离级别。

比如说MyISAM这种不支持事务的引擎，如果备份的过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL命令了

所以，single-transaction方法只能用于所有的表使用支持事务的引擎。否则只能用FTWRL

这里还有一个小问题，既然效果是全库只读，那为啥不用set global readonly=true呢？

确实，readonly也可以让全库进入只读状态，但是还是建议使用FTWRL。原因如下：

• 在一些系统中，readonly会被用来做其他的逻辑，比如判断一个库是主库还是备库
• 在异常处理机制上有差异，如果是FTWRL，那么如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库会回到正常更新的状态，但是如果是设置readonly，那么数据库就会一直保持readonly，这样会导致整个库长时间处于不可写的状态，风险很高

表级锁

MySQL表级锁有两种：表锁和元数据锁

和FTWRL类似，可以显式释放锁，也可以客户端断开的时候自动释放，需要注意的是，加锁后，除了会限制别的线程的读写，本线程的操作对象也被限制了

举个例子：线程A对t1加了写锁，t2加了读锁，那么其他线程写t1会被阻塞，读t2会被阻塞，同时，在线程A释放锁之前，也只能读t1，读写t2，写t1都不行，也不能访问其他的表

对于InnoDB这种支持行锁的引擎来说，一般不用表锁，毕竟锁住整个表的影响还是太大

另一个表级锁是MDL，不需要显式使用，在访问一个表的时候会被自动加上，MDL的作用是，保证读写的正确性，你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删除了一列，那么查询线程拿到的结果和表的结构对不上是肯定不行的

在MySQL5.5的版本中引入了MDL，对一个表做增删改查的时候，会加上MDL读锁，当要对表做结构变更的时候，加MDL写锁

• 读锁直接不互斥，因此可以有多个线程同时对一个线程增删改查
• 读写锁、写锁之间是互斥的，用来保证变更表的结构操作的安全性，因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完毕才能开始执行

虽然MDL是系统默认会加的，但是你不能忽略一个机制，比如说下面这个例子：

session A	session B	session C	session D
begin; select * from t limit 1;
	select * from t limit 1;
		alter table t add f int;(blocked)
			sekect * from t limit 1(blocked)

可以看到会话A先启动，因为是select，所以加的是读锁，会话B也是如此，所以两个会话之间互不影响

但是会话C是更改表的结构，是加写锁，所以C会被阻塞住。只有等A、B释放了锁才行

但是问题就在于，只有C被阻塞了还没啥关系，C之后所有要在表t上加新的读锁也会被C阻塞住，那么现在相当于读锁和写锁都不能加，也就是这个表现在完全不可读写了

如果这个表的查询频繁，并且客户端有重试机制，也就是超时后会有一个新的会话，那么这个库的线程就会爆满

对于MDL的锁，在语句开始执行的时候申请，但是语句结束后并不会马上释放，而是等到整个事务提交后才会释放

现在，我们来深入讨论一下，如何安全的给小表加字段

首先我们要解决的就是长事务，事务不提交，那么就会一直占着MDL锁。你可以在MySQL的infomation_schema库里的innodb_trx表中查到当前执行中的事务，如果你要做DDL变更的表刚好有长事务，你要么先考虑暂停DDL，或者先kill掉这个长事务

但是考虑一下这个场景，如果你要变更的表是一个热点表，数据量不大，但是上面的请求非常的频繁，而你又不得不加一个字段，怎么办？

这个时候kill命令可能不管用，因为新的请求马上到来。比较理想的办法是：在alter table语句里设定等待时间，如果这个指定的等待时间里能够拿到MDL的写锁最好，拿不到也不要阻塞后面的业务，先放弃，之后再说