笔记记录自林晓斌（丁奇）老师的《MySQL实战45讲》
6） --全局锁和表锁：给表加个字段怎么有这么多阻碍
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要结构。根据加锁的范围，MySQL里面的锁大致可以分为全局锁，表级锁和行锁三类。这篇笔记主要包含全局锁和表级锁。行锁的内容会在之后再进行分享。
全局锁：
全局锁就是对整个数据库实例加锁。可以通过命令 Flush tables with read lock (FTWRL)对全局进行加锁。使用全局锁后，其他线程的以下语句会被阻塞：数据更新语句（增删改），数据定义语句（包括建表，修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。但如果你直接用上面的FTWRL去进行备份，会让整库都处于只读状态，这光想想就觉得危险。如果你在主库上备份，那么在备份期间都不能执行更新，业务基本停摆；如果你在从库上备份，那么备份期间从库不能执行从主库同步过来的binlog，会导致主从延迟。那么不加锁备份数据库可以吗？答案是不可以的，比如你有两张表，余额表和商品表。如果不加锁，在备份期间时先备份余额表，再备份商品表会出现什么情况呢？假设在这两张表备份的间隔时间内进行了数据更新，则余额表数据在备份表中没有减少，但是商品表在备份表中的数据却减少了，造成了逻辑上的数据不一致问题。当然这两张表的备份顺序颠倒过来也是一样会有这个问题。
官方自带的逻辑备份工具是mysqldump.当mysqldump使用参数-single-transaction时，备份数据之前会启动一个事务，来确保拿到一致性视图。而由于MVCC（多版本并发控制）的支持，这个过程中数据是可以正常更新的。
至此，你可能会有个疑问，为什么有了上述这个功能，还需要FTWRL呢？此处有个细节是 可重复读是很好，但并不是所有的MySQL引擎支持这个隔离级别的。（如MyISAM这种不支持事务的引擎，就需要执行FTWRL命令了。）所以，single-transaction方法只适用于所有的表使用事务引擎的库。
既然要保证备份时全库只读，为什么不使用set global readonly = true的方式呢？ 不建议使用这种方式有以下两方面原因：
在有些系统中，readonly的值会被用来处理其他逻辑。
在异常处理机制上的差异。FTWRL命令后如果客户端发生异常断开连接，那么MySQL会自动释放这个全局锁。而readonly=true会保持数据库长时间处于只读状态。
表级锁：
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）
表锁的语法是 lock tables ... read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是，lock tables语法除了会限制别的现成的读写外，也限定了本线程接下来的操作对象。在没有更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
另一类表级锁MDL不要显示使用，在访问一个表的时候会自动加上。它的作用是保证读写的正确性。如一个查询正在遍历一个表中的数据，而同时另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果就会和表结构对不上，肯定是不行的。因此，在MySQL5.5引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表结构变更时，加MDL写锁。
读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
读写锁之间，写锁之间是互斥的。以此来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
另外，MDL会知道事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。
表级锁一般是在数据库引擎不支持行锁的时候才会被用到。如果你发现你的应用程序里有lock tables这样的语句，你需要追查一下，比较可能的情况是：
你的系统还在使用MyISAM这类不支持事务的引擎，需要安排升级更换引擎了
你的引擎升级了，但是代码还没有升级。这样把lock tables和unlock tables更换成begin和commit，问题就解决了。
上篇问题答案：
表结构如下所示：
CREATE TABLE `geek` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `c` (`c`), KEY `ca` (`c`,`a`), KEY `cb` (`c`,`b`) ) ENGINE=InnoDB;
由于历史原因， a和b需要做联合主键。那么既然主键包括了a,b这两个字段，又单独在c上创建了一个索引，索引就已经包含了三个字段了，为什么还要创建ca，cb索引呢？有人给出的理由是业务里有这样两个查询：
select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1;
这个理由对吗？为了这两个查询，这两个索引是否都必须呢？为什么呢？
我们以一组记录来回答这个问题。
| a | b | c | d |
| 1 | 2 | 3 | d |
| 1 | 3 | 2 | d |
| 1 | 4 | 3 | d |
| 2 | 1 | 3 | d |
| 2 | 2 | 2 | d |
| 2 | 3 | 4 | d |
主键a,b的聚簇索引组织顺序相当于order by a,b。也就是先按a排序，再按b排序，c无序。
索引ca的组织顺序如下（先按c排序，再按a排序），同时记录主键部分b的值
| c | a | b |
| 2 | 1 | 3 |
| 2 | 2 | 2 |
| 3 | 1 | 2 |
| 3 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
你可能会发现，ca的组织顺序和索引c的顺序是一模一样的。
索引cb的组织顺序如下（先按c排序，再按b排序，同时记录主键a）
| c | b | a |
| 2 | 2 | 2 |
| 2 | 3 | 1 |
| 3 | 1 | 2 |
| 3 | 2 | 1 |
| 3 | 4 | 1 |
| 4 | 3 | 2 |
所以，结论是ca可以去掉，cb索引仍要保留。ca去掉的原因是组织顺序与索引c相比完全相同。
问题：
备份一般都会在备库上执行，你在用-single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？
作者：DogTwo
原文链接：https://www.cnblogs.com/dogtwo0214/p/10457717.html
随时随地看视频
