看完MySQL全局锁和表锁，你废了吗？
根据加锁粒度，MySQL的锁：
- 全局锁
- 表级锁
- 行锁
全局锁和表锁都实现在Server层。
1 全局锁
对整个DB实例加锁。
1.1 加全局的读锁
执行命令：
Flush tables with read lock (FTWRL)
让整个库只读，之后其他线程的如下语句会被阻塞：
- 数据更新语句（数据的增删改）
- 数据定义语句（建表、修改表结构等）
- 更新类事务的提交语句
若FTWRL前有读写，FTWRL都会等待读写执行完毕后再执行。
FTWRL执行时，要刷脏页数据到磁盘，因为要保持数据一致性，所以执行FTWRL的时机是所有事务都提交完毕后。
1.2 适用场景 - 全库逻辑备份
把整库的每个表都select出来存成文本。历史有做法通过FTWRL确保不会有其他线程对DB更新，然后对整库备份。
备份过程中，整库都只读。但让整库只读：
- 若你在主库备份，则备份期间，主库都不能再执行更新，业务直接停摆
- 若你在从库备份，则备份期间，从库不能执行主库同步过来的binlog，主从延迟加剧
看来加全局锁不太好。不妨反思：
1.3 备份为何要加锁？
即不加锁会导致什么问题？假设现在维护购课系统，关注用户的：
- 账户余额表(u_account)
- 用户课程表(u_course)
现在发起一个逻辑备份。假设备份期间，有一用户，他购买了一课程，业务逻辑里就要扣掉他的余额，然后往已购课程里加上一本书。
若时间顺序：
- 先备份 u_account
- 然后用户购买
- 然后备份 u_course
用户A的数据状态就是u_account没扣，但u_course里多了一门课。若后面用这个备份恢复数据，用户A就发现自己血赚！
不加锁时，备份系统备份得到的库不是同一逻辑时间点，这视图是逻辑不一致的。
1.4 mysqldump
有个办法能拿到一致性视图：在RR下开启一个事务。
官方的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction，导数据前会启动一个事务，确保得到一致性视图。
由于MVCC，该过程中数据依旧能正常更新。
若mysqldump备份的是整个schema，某个小表t1只是该schema上其中有一张表
① 情况1
master对小表t1的DDL传输到slave去应用时，mysqldump已备份完t1表数据，此时slave同步正常，没问题
② 情况2
master对小表t1的DDL传输到slave去应用时，mysqldump正在备份t1表数据，此时会发生MDL锁，从库上t1表的所有操作都会Hang住
③ 情况3
master对小表t1的DDL传输到slave去应用时，mysqldump还没对t1表进行备份，该DDL会在slave的t1表应用成功，但当导出到t1表时，会报“ERROR 1412 (HY000): Table definition has changed, please retry transaction” 错误，导致导出失败！
1.5 有这功能，何需FTWRL？
一致性读是好，那但也得引擎支持这隔离级别。
MyISAM不支持事务，备份过程中若有更新，只能取到最新数据，破坏了备份的一致性，就需要使用FTWRL。
所以single-transaction只适于所有的表使用支持事务引擎的库。一旦有表使用了不支持事务的引擎，则备份只能通过FTWRL。
这也是使用InnoDB替代MyISAM的主因之一。
1.6 那readonly不就够了？
既然要全库只读，何不使用：
set global readonly=true
readonly可让全库只读，但还是推荐FTWRL：
1.6.1 影响面太大
有些系统的readonly值会被用来做其他逻辑，比如判断一个库是主库or备库。因此，修改global变量的方式影响面太大！
1.6.2 异常处理差异
执行FTWRL后，由于客户端异常断开，MySQL会自动释放该全局锁，整库回到可正常更新的状态。
而将整库设为readonly后，若客户端异常，则数据库就一直保持readonly，导致整库长时间不可写。
业务的更新不只是增删改数据（DML，data manipulation language)，还有可能是加字段等修改表结构的操作（DDL，data definition language）。无论哪种方法，一个库被加了全局锁后，你要对里面任何一个表做加字段操作，都会被锁住。
即使没有被全局锁锁住，加字段也不是一帆风顺，还会碰到表级锁。
2 表级锁
表级锁有两种：
2.1 表锁
2.1.1 语法
lock tables … read/write
类似FTWRL：
- 可用
unlock tables主动释放锁 - 也可在客户端断开时自动释放
lock tables语法除了会限制别的线程读写，也限定了本线程接下来的操作对象。
表级别的write锁，对本线程可读、写。
若在线程A执行：
lock tables t1 read, t2 write;
则其他线程写t1、读写t2的语句都会被阻塞。
线程A在执行unlock tables前，也只能执行读t1、读写t2的操作。写t1不允许，也不能访问其它表。
对InnoDB这支持行锁引擎，一般不推荐使用lock tables命令控制并发，毕竟锁的粒度过大。
2.2 元数据锁（meta data lock，MDL)
访问一个表时会被【自动加上】，以保证读写的正确性。
若一个查询正在遍历一个表数据，执行期间另一个线程在变更该表结构，删了一列，则查询线程拿到的结果就跟表结构对不上了，这肯定不行啊！
2.2.1 加锁规则
于是MySQL 5.5引入MDL：
- 对一个表做CRUD（DML），加MDL读锁
- 对表做结构变更操作（DDL），加MDL写锁
2.2.2 互斥规则
读锁之间不互斥，因此可多线程同时对一张表CRUD。
读、写锁之间，写锁之间互斥，以保证变更表结构操作的安全性。
所以MDL是为防止DDL和DML的并发冲突，而非解决select和update间的并发。
虽然MDL锁默认会加，但也不能轻视。比如给一个表加字段或修改字段或加索引，需扫描全表数据。即使是个小表，操作不慎也有问题。
2.2.3 案例
假设表t是个小表。
MySQL 5.6。
| S1 | S2 | S3 | S4 |
|---|---|---|---|
| begin select * from t limit 1 | |||
| select * from t limit 1 | |||
| alter table t add f int; (blocked) | |||
| select * from t limit 1; (blocked) |
- S1先启动，对表t加个MDL读锁
- S2加的也是MDL读锁，可正常执行
- S3会被阻塞，因S1的MDL读锁还没释放，而S3需MDL写锁
若只有S3被阻塞还没啥，但之后所有要在表t上新申请MDL读锁的请求也会被S3阻塞。
所有对表的CRUD操作都要先申请MDL读锁，就都被锁住，等于该表此时完全不可读写！
为何被未完成执行的S3阻塞
为确保事务的可序列化，MySQL不允许一个会话对在另一会话中未完成的显式/隐式启动的事务中使用的表执行DDL。
服务器通过获取事务中使用的表上的MDL，并将这些锁的释放推迟到事务结束之前来实现。表上的MDL可防止更改表的结构。这种锁定方法的含义是，一个会话中事务正在使用的表在事务结束前不能被其他会话在DDL语句中使用。
MySQL对申请MDL锁的操作会形成一个队列，队列中的写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。当事务一旦申请到MDL后，直到事务执行完才会将锁释放，当长事务或未提交的事务未提交完成时，执行DDL语句会等待MDL写锁而阻塞，继而阻塞该表的后续其他操作。
若某表的查询语句频繁，且客户端还有重试机制，即超时后会再起一个新session再请求，该库的线程很快就会爆开（全部都在等待）。
事务中的MDL锁，在语句执行开始时申请，会一直等到整个事务提交了再释放。
2.3 Online DDL
MySQL5.6支持Online DDL，对表操作增加字段等功能时，不会阻塞读写，那为啥还会出现上述案例的结果？
2.3.1 Online DDL过程
- 拿MDL写锁
- 降级成MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL锁
1、2、4、5若无锁冲突，执行时间很短。第3步占用了DDL绝大部分时间，这期间该表可正常读写数据，因此称为Online。
但案例中，第1步就阻塞了。所以必须等待之前的事务全部提交后，才能开始执行。
2.4 如何正确加字段？
先要解决长事务，事务不提交，就会一直占MDL锁。
在MySQL的information_schema 库的 innodb_trx 表中，可查到当前执行中的事务。
若你要做DDL变更的表刚好有长事务在执行，要考虑：
- 原地等待，先暂停DDL
- 或kill掉这长事务
但若变更的表是一个热点表，虽数据量不大，但对表请求频繁，又不得不加字段，咋办？
在alter table语句设定等待时间，若在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n语法。
ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...
主库上的一个小表做了一个 DDL，同步给slave，由于这时有了先前的single-transaction，所以slave就会出现该表的锁等待，并且slave出现延迟。
2.5 哪些操作会加表级锁？
- 执行增删改时，默认加行锁
- 然后执行DDL语句时，如alter table，会默认在表级别加表锁
这么说也不太正确，但也有一定道理，因为确实你执行DDL时，会阻塞所有增删改操作； 执行增删改时，会阻塞DDL操作。
InnoDB提供了自己的表级锁，跟这里DDL语句用的元数据锁不是一个概念。只不过DDL语句和增删改操作，确实互斥。
2.6 再谈表锁
MySQL的表锁很鸡肋，几乎很少用，表锁分为：
- 表锁
- 表级的意向锁
2.6.1 语法
# 加表级共享锁 LOCK TABLES xxx READ # 加表级独占锁 LOCK TABLES xxx WRITE
几乎没人会用这两个语法加表锁。
另外两个情况会加表级锁
- 若有事务在表里执行增删改操作，那在行级会加独占锁，此时同时会在表级加一个意向独占锁
- 若有事务在表执行查询操作，会在表级加一个意向共享锁
平时操作DB常见的两种表锁：
- 更新操作加的意向独占锁
- 查询操作加的和意向共享锁
但这意向独占锁和意向共享锁暂时可当是透明的，因为两种意向锁不互斥。
因为假设有个事务在表里更新id=10的一行数据，在表上加个意向独占锁，此时另外一个事务要在表里更新id=20的一行数据，也会在表上加一个意向独占锁，明显不应互斥，因为他们俩更新表里不同数据，让他们俩在表上加的意向独占锁互斥有何意义？ 所以意向锁间不会互斥。
同理，假设一个事务要更新表里的数据，在表级加个意向独占锁，另外一个事务要在表里读数据，在表级加个意向共享锁，此时表级的意向独占锁和意向共享锁应互斥吗？不应该！一个人要更新数据，一个人要读取数据，俩人在表上加的意向锁，凭什么互斥？
因此，所谓的表级的意向独占锁和意向共享锁，似乎是脱了裤子放屁？
但：
- 手动加表级【共享锁】和【独占锁】
- 及更新和查询时自动在表级加的【意向共享锁】和【意向独占锁】
之间反而有一定互斥关系：
| 锁类型 | 独占锁 | 意向独占锁 | 共享锁 | 意向共享锁 |
|---|---|---|---|---|
| 独占锁 | 互斥 | 互斥 | 互斥 | 互斥 |
| 意向独占锁 | 互斥 | 不互斥 | 互斥 | 不互斥 |
| 共享锁 | 互斥 | 互斥 | 不互斥 | 不互斥 |
| 意向共享锁 | 互斥 | 不互斥 | 不互斥 | 不互斥 |
在表上面：
- 手动加的独占锁、共享锁
- 更新数据和查询返数回据默认自动加的意向独占锁、意向共享锁
锁是互斥的，所以更新数据自动加的表级【意向独占锁】，和你用
# 手动加了表级独占锁 lock tables xxx write
此时任何人都不能执行更新操作。
或你用
# 手动加【表级共享锁】 lock tables xxx read
则任何人也不能执行更新操作，因为更新就会默认加意向独占锁，和手动的表级共享锁互斥。
【手动】加表级的共享锁或独占锁，此时会阻塞其他事务的一些正常读、写操作，因为和他们自动加的【意向锁】都互斥。然而一般都不会去手动加表级锁，所以一般读写操作自动加的表级意向锁，互相之间不会因为互斥而导致阻塞。
一般都是：
- 对同一行数据的更新操作加的行级独占锁之间互斥
- 跟读操作都不互斥，读操作默认都走MVCC读快照版本
3 总结
全局锁主要用于全库逻辑备份。对于全部是InnoDB引擎的库，推荐–single-transaction参数。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables这样的语句，你需要追查一下，比较可能的情况是：
- 要么是你的系统现在还在用MyISAM这类不支持事务的引擎，那要安排升级换引擎
- 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了
MDL会直到事务提交才释放。
参考
