MySQL关于锁的学习

本文为阅读极客时间MySQL实战45讲后总结
锁的意义
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构

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

一、全局锁

全局锁就是对整个数据库实例加锁。

1、怎么添加全局锁

MySQL提供了一个加全局读锁的方法，命令是：Flush tables with read lock（FTWRL） 。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下命令会被阻塞：数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事物的提交语句。
全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本

2、全局锁使用案例

通过FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份，在备份过程中整个库完全处于只读状态。但是这样会产生问题：
a. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
b. 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟
如果不加锁的话：备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

解决方法：事务隔离的时候是有一个方法能够拿到一致性视图的，就是在可重复读隔离级别下开启一个事务。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数-single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

疑问1：有了上述解决方法，为什么还要使用FTWRL？
一致性读是好，但前提是引擎要支持这个隔离级别。对于MylSAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL命令了。

疑问2：既然要全库只读，为什么不适用 set global readonle = true 的方式呢？
使用readonly方式也可以让全库进入只读状态，但是建议还是使用FTWRL方式，主要由两个原因：
a. 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是从库。因此修改 global变量的方式影响面更大，不建议这样使用
b. 在异常处理机制上有差异，如果执行FTWRL 命令之后由于客户端发生异常断开，那么MYSQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly 之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

二、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁(meta data lock, MDL)

1、表级锁添加方式一

表锁的语法是 local tables … read/write. 与FTWRL类似
释放锁的条件：
a. 用unlock tables 主动释放锁
b. 可以在客户端断开的时候自动释放

lock tables 语法除了会限制别的线程的读写外，也会限定本线程接下来的操作对象
举例：在某个线程A中执行 lock tables t1 read,t2 write; 这个语句，则其他线程写t1、读t2的语句都会被阻塞。同时，线程A在执行unlock tables 之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其它表。
对于InnoDB这种支持行锁的引擎，一般不适用lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大

2、表级锁添加方式二

第二类表级的锁是MDL(metadata lock). MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。

案例：一个查询正在遍历一个表中的数据，而在执行期间另一个线程对这个表结构做变更，删除了一列，那么查询线程拿到的结果跟表结构对不上，这样做肯定是不行的

因此，在MySQL 5.5 版本中引入了MDL， 当对一个表做增删改查操作的时候，加MDL读锁； 当要对表做结构变更操作的时候，加MDL写锁
a. 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
b. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性

踩坑点：给一个小表加个字段，导致整个库挂了
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。如果操作不当，就会引起问题

步骤：
1、 sessionA 启动，会对表t添加一个MDL读锁
2、 sessionB需要的也是MDL读锁，因此可以正常执行
3、 sessionC 需要MDL写锁，因为sessionA 的MDL读锁还没有释放，因此只能被阻塞
4、 sessionD 也会被阻塞
疑问：问什么sessionD为什么也会被阻塞
首先MDL 是server层的锁，主要用于隔离DML(数据操纵语言) 和DDL (数据定义语言，如增加列等) 操作之间的干扰，没执行一条DML、DDL 语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制的，无法直接干预，读读共享，读写互斥，写写互斥）
申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，知道事务执行完才会将锁释放
特殊情况：如果事务中包含了DML和DDL ，mysql会在DDL操作执行之前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在

如何安全的给表添加字段
知道了事务中的MDL锁会在事务提交之后释放锁，那么要解决长事务，事务不提交，就会一直占着MDL锁
a.解决长事务：MySQL的 information_schema 库的innodb_trx 表中可以查看当前执行中的事务，可以kill 长事务
b.等待长事务结束
如果是热点表，请求很频繁，而又不得不加字段，应该怎么做？
这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面设定等待时间 ，如果在这个指定的时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

三、表级锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发展。InnoDB是支持行锁的

1、两阶段锁

事务A持有两个记录的行锁，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。
也就是说：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是等到事务结束时才释放，这个就是两阶段锁协议

知道了这个设定，对我们使用事务有什么帮助呢？
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
举例：在一个电影票在线交易业务中，顾客A要从影院B购买电影票：
1、从顾客A账户余额中扣除电影票价
2、给影院B的账户余额增加这张电影票价
3、记录一条交易日志
为了保证交易的原子性，我们要把这三个操作放在一个事务中，那么应该怎么安排这三个语句在事务中的顺序呢？
如果同时有一个顾客C要在电影院B买票，那么这两个事物冲突的部分就是步骤2，因为他们要更新同一个影院账户的余额，需要修改同一行数据。
根据两阶段协议，不论怎么样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以要把步骤2放到最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。（行锁只有在使用的时候才会获取，并不是事务开始时拿到的）
但是高并发下会导致死锁的产生。

2、死锁和死锁检测

死锁的产生
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，成为死锁。

这个时候,事务A在等待事务B释放 id = 2 的行锁，而事务B在等待事务A释放 id = 1的行锁。事务A和事务B都在互相等待对方的资源释放，就是进入了死锁的状态。

当出现死锁以后，有两种策略：
a. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
b. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执 行。将参数innodb_deadlock_detect 设置为on，表示开启了这个逻辑

策略一(超时释放)分析
在InnoDB中，Innodb_lock_wait_timeout 的默认值是50s，意味着如果采用第一个策略，等待时间是无法接受的
但是也不能把这个值设置成太小，因为如果不是死锁，会出现很多误伤。

策略二(死锁检测)分析
主动死锁检测是默认开启的。主动死锁检测在发生死锁的时候，能够快速发现并进行处理，但是也有额外的负担。
例如：每当一个事务被锁的时候，就要看看他所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。 如果说所有事务都要更新同一行的场景中。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，那么不论结果如何，都会消耗大量的cpu资源。

问题：死锁检测会耗费大量的CPU资源
解决方法：
1、如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。关掉死锁可能会出现大量的超时(死 锁时超时释放)
2、控制并发度。如果能搞控制住并发度，比如同一行最多只有10个线程在更新，那么死锁检测的成本很低
可以通过队列的方式，也可以将一条记录修改为多条记录，比如用10个行的值来记录影院的账户总额， 那么每次冲突概率就变成原来的十分之一