全局锁和表级锁

1. 全局锁

顾名思义，全局锁就是对整个数据库实例加锁。

MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock（FTWRL）。当使用了这个命令，整个数据库都处于只读状态，下列几种线程会被阻塞：

1. 数据更新语句（增删改）
2. 数据定义语句（创建表，修改表结构等）、
3. 更新类事务的提交语句

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

但是让数据库只读，听上去很危险：

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

但是在实际中，备份是必须要加锁的。如果不加锁会出现什么情况呢？

例子：
    假设现在维护了一个网课系统，主要有用户账户余额表和课程表。现在发起一个逻辑备份
，假设在进行备份的期间，用户购买了一门课程，业务逻辑上是要扣掉他的余额，同时在课程
表上增加一门课。
1
2
3
4

如果我们先备份了用户账户余额表，然后用户购买，再备份课程表，就会出现余额表没扣钱，但是课程表多了一门课的情况。如下图：

同理，如果我们先备份了课程表，然后用户购买，再备份余额表，就会出现余额表扣了钱，但是课程表没有新增课程的情况。

因此，如果不加锁的话，系统备份的库不是一个逻辑时间点，这个视图是逻辑不一致的。

而对于支持事务的存储引擎，例如InnoDB，其实也可以通过开启可重复读隔离级别的事务拿到一致性视图。
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数 -single-transaction的时候，导数据之前就会启动一个事务，来拿到一致性的视图。由于MVCC的支持，这个过程中是可以正常更新的。

还有个问题，既然只要全库只读，那**==为什么不使用set global readonly=true的方式**==呢？主要有下面两个原因：

1. 有些系统中，readonly的值会被用来判断是主库还是备库，所以不建议修改。
2. 在异常处理中，如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局读锁。而使用readonly=true，则数据库会一直处于只读状态，导致数据库长期不可写。

2. 表级锁

在MySQL中，表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

表锁的语法是lock tables … read/write。需要注意的是，lock tables语法除了会限制其他线程的读写外，也限定了本线程接下来的操作对象。例如：

如果某个线程A执行lock tables t1 read,t2 write;这个语句。则其他线程写t1，读写t2都会被阻塞。
同时，在线程A执行unlock前，也只能执行读t1，读写t2的操作。
1
2

在没有出现更细粒度的锁之前，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，因为锁住整个表还是影响太大。

另一种表级锁是元数据锁MDL。MDL不需要显式使用，在访问一个表的时候就会被自动加上。MDL的作用是保证读写正确性，针对的是表结构的修改。例如一个线程在查询期间，另外一个线程删除了其中一列，这肯定是不行的。

在MySQL5.5版本之后引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构做更改操作时，加MDL写锁。

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

但是MDL锁虽然是系统默认加的，但是不能忽略一些细节。有时候对于小表加一个字段，操作不慎也会出现问题，导致整个库挂掉。例如下图这个例子：

• 我们可以看到，session A先启动，这时会对表加一个读锁。由于session需要的也是MDL读锁，所以都可以正常运行。
• 之后，因为session C是修改表结构，所以需要的是MDL写锁，而由于session A和session B的读锁没有释放，所以session C会被阻塞。
• 由于session C被阻塞，而且写锁的优先级更高。这就导致之后申请MDL读锁的线程，都会被阻塞，这个表就编程完全不可写了。

那么如何给小表安全地加字段呢？
首先要解决长事务，如果有正在运行地长事务，可以进行kill掉。

但如果是一个热点表，访问很频繁，但是又必须增加字段呢？
这时候kill未必管用，因为新的请求马上就来了。比较理想地方式是，在alter table里面设置等待时间，如果在指定地等待时间里拿到MDL写锁，那当然最好，拿不到也不要阻塞后面地语句，先放弃。之后再重试这个命令。

来源：自己整理的MySQL实战45讲笔记