MySQL深入05-全局锁、表锁、行锁
# 全局锁
全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，就可以使用Flush tables with read lock(FTWRL)，之后无论是数据更新语句（数据的增删改）、数据定义语句（建表、修改表结构）和更新类事务的提交语句都会被阻塞。
全局锁的典型使用场景是：做全库逻辑备份。
通过 FTWRL 确保不会有其他线程对数据库做更新,也就是在备份过程中让整个库完全处于只读状态，然后对整个库做备份。
但是让整个库只读存在两个问题：
- 如果在主库做备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
- 如果在从库做备份，那么从库就不能执行主库同步过来的binlog，会导致主从延迟。
现在再来研究一个问题，如果不加锁，去备份会出现什么问题：
假设现在备份网课系统的用户余额表和用户课程表，在备份期间用户购买了网课，那么购买网课的业务逻辑是扣掉余额、然后在已买课程里面加上一门课。
现在我们的备份顺序是先备份了余额、然后用户购买、然后备份用户课程表：
那么使用这份备份来恢复数据会出现一个问题：用户余额没变，但是课程增加了。
现在又假设如果备份顺序是先备份用户课程表、然后用户购买、再备份用户余额表，那么就会出现用户课程表没变，但是用户的余额减少了。
也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。
为什么不使用set global readonly = true的方式
- 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。
- 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。
# 表级锁
表级锁分为两种：
- 表锁
- 元数据锁（meta data lock MDL）
# 表锁
**表锁的语法是 lock tables … read/write。**可以用unlock tables主动释放锁，或者在客户端断开的时候自动释放。
如果某个线程A执行lock tables t1 read, t2 write;,相当于线程A给t1加了读锁，给t2加了写锁。则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
# 元数据锁
MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。如果此刻线程A正在查询表T，而执行期间线程B对表T结构做变更，那么线程A拿到的结果跟表结构对不上。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
现有下面的例子：
sessionA先启动，会对表t加一个MDL读锁，由于sessionB需要的也是MDL读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
严重的是session C之后的session如果要在表t上申请MDL读锁的请求也会被session C阻塞，相当于这个表已经变得不可读了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
# 如何给小表加字段
由于MDL 会直到事务提交才释放，所以我们要优先解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
如果是一个热点表，数据量不大，但是查询比较频繁。kill掉这个事务就不太实用了，因为kill掉马上就会来一个新的请求。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
# 行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。行锁就是针对数据表中记录的锁。比如事务A更新了一行，而这时候事务B也要更新一行，则必须等事务 A 的操作完成后才能进行更新。
# 两阶段锁
在下面的操作序列中，事务B的update语句执行会发生什么?
事务A在开始的时候，是不会持有id=1和id=2这两条数据行的行锁，只有当执行两条update语句的时候，行锁才会加上。那么事务B由于id=1的行锁现在被事务A持有，所以事务B的update语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
通过上面例子我们要注意一个细节：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
比如此刻有一个线上购物业务，顾客A去商店B购买球鞋，大概业务逻辑如下：
1、扣除用户A的余额。
2、增加商店B用户的余额。
3、记录一条交易记录。
这样一个线上购物业务需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么现在如何怎样安排这三个语句在事务中的顺序呢？
设想现在又有一个用户C去商店B购买球鞋，那么最有可能发生锁冲突的是第几条语句呢？由于更新商店B用户是同一行数据，也就是修改同一行数据，所以语句2最可能锁冲突。
根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么商店账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。
# 死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
在InnDB中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是实际情况也存在锁等待的问题，所以把这个时间设置成一个很小的值也是不太好的。
所以正常情况下，我们采用第二种策略：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。
因为每当一个事务被锁的时候，就要查看他所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
对于多个事务同时操作一行数据的情况
每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万（1000 * 1000）这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
如何解决热点行更新导致的性能问题
主要是降低死锁检测耗费的CPU资源
- 如果确保业务一定不会出现死锁，可以临时把死锁检测关掉。
- 控制并发度，比如同一行同时最多只有10个线程在更新。