MySQL系统学习（06）：MySQL的全局锁、表级锁以及行锁
image.png
原文： MySQL系统学习（06）：MySQL的全局锁、表级锁以及行锁
前言
数据库锁的设计初衷是为了处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库要合理的控制资源的访问规则。而锁就是为了实现这些访问规则的重要数据结构。
image.png
根据锁的范围，MySQL里的锁大致可以分为三类：全局锁、表级锁和行锁三类。这里需要说明的是，因为锁的设计比较复杂，文章不会设计锁的具体实现细节，主要说明的是碰到锁时的现象及其背后的原理。
全局锁
顾名思义，全局锁就是对整个数据库实例进行加锁。MySQL提供了一个加全局锁的方法，命令是Flush tables with read lock（FTWRL）。当我们需要让整个库处于只读状态时，可以执行这个命令，之后其他的线程的这些行为将被阻塞：数据更新语句（数据的增删改）、数据定义语句（包含建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，作全库的数据备份。也就是把数据库每张表都select出来存成文本。
以前有一种常见的做法便是执行FTWRL命令，让全库处于只读状态，确保不会有其他线程对数据库做更新操作，然后对整个库做备份。这种场景适用于小业务，什么叫小业务？在我看来就是调用量比价少，最好是有规律性的，比如夜间是没有调用量，即更不会有变更操作的这种情况下使用没有问题。但是，对于大型的业务而言，备份期间数据库只有只读状态，这是不现实的，很危险：
1.如果你在主库上备份，那么在备份期间都不能更新，业务基本上就要停了。
2.如果你在从库上备份，那备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
看来加全局锁是不太好的。但是有没想过备份这个场景它为什么一定需要加锁呢？
我们来举个例子，假设某电商平台的订单和用户账户余额分别对应A、B两张表。现在需要对用户订单和用户账户余额两张表做备份：首先，我们需要发起一个逻辑备份。假设在备份期间突然有一个用户下单，购买了一个手机，正常的流程应该是订单表中添加一个用户购买手机的记录，同时扣除用的账户余额表的应扣除的金额，看着似乎没什么问题，但是你别忘记了我们现在正在干嘛，我们正在做账户余额表和订单记录表的数据备份呢。
假设这个过程是这样子的：
1.我们先备份的账户余额表，然后用户在购买了手机，然后在备份了用户订单信息表。那会发生这样的事情，我们发现用户余额表的余额没有扣除，但是订单信息表中确多了一条成功下单的订单记录。可能作为用户你会跟高兴，心想自己赚了。别着急，那如果这时DBA的数据备份顺序是反过来的呢，又会怎样？
2.我们先备份用户订单信息表，在发生用户购买手机的动作，然后在备份用户账户余额信息表，相信这时候作为用户，你可能已经坐不住了。因为你的余额被扣除了，但是订单确没有下成功，我想着一定是一件糟糕的事情。
也就是说，不加锁的话，备份系统备份得到的库不是一个逻辑时间点上，这个视图是逻辑不一致的。说到视图，我的上一篇笔记MySQL系统学习（05）：事务隔离性与隔离级别中记录事务隔离级别的时候，有提到视图逻辑一致性问题，可以看下那部分。
官方自带的备份工具是mysqldump。当mysqldump使用参数-single-transaction时候，在导数据之前，就会开启一个事务，来确保拿到一致性视图。而由于MVCC（英文全称为Multi-Version Concurrency Control,翻译为中文即 多版本并发控制。在小编看来，他无非就是乐观锁的一种实现方式。在Java编程中，如果把乐观锁看成一个接口，MVCC便是这个接口的一个实现类而已。）的支持，这个数据是可以正常被更新的。
这时候我们可能会有疑问，既然都有了这个东西了，为什么还需要FTWRL呢？一致性读是很好，但前台是引擎要支持这个隔离级别。比如，对于MyISAM引擎这类不支持事务的引擎来说，如果备份过程中有更新，那总是取得最新的结果，这样就破坏了备份的一致性。这种情况我们就需要用到 FTWRL 了。
所以， -single-transaction命令只适用于所有表使用事务引擎的库。如果有的表使用的不支持事务的引擎，那备份就只能使用 FTWRL 了。这也往往是很多公司的DBA要求开发人员在提交建表语句的使用，强制约束必须使用InnoDB引擎的一个重要原因之一。
你也许会问，既然全库只读，那为什么不使用 set global readonly=true的方式呢？ 确实，readonly方式也可以让全库处于只读状态，但还是建议使用FTWRL，主要有两个原因：
1.一个系统中，readonly的值可能会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global的方式影响面更大，不建议使用它来控制备份的只读。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生了异常断开，那么MySQL会释放这个全局Lock，整个库会立马恢复到可更新状态。而如果使用readonly，那如果客户端发生异常，整个库可能很长一段时间会处于只读状态，风险太高。
业务的更新不只是增删改数据（DML） ，还有可能是加字段等修改表结构的操作（DDL）。不论哪种方式，一个库被全局锁上之后，你要对里面任意一张表加字段操作，都会被锁住。
但是，如果没有全局锁，加字段也不是就能一帆风顺的，因为还有另外一个重要表级锁。
表级锁
MySQL里面表级别的锁有两种：一种是表锁，另一种是元数据锁（meta data lock， MDL）。表锁的语法是 ** lock tables … read/write **。与FTWRL类似，可以用 unlock tables主动释放锁。需要注意的是lock tables 语法除了会限制其他线程读写外，也会限制本线程在接下来的操作对象。
举个例子，如果某个线程A执行了 lock tables t1 read, t2 write; 这个语句，则其他线程写t1和读t2的语句都会被阻塞。同时，现在A在执行unlock执行，也只能执行read t1 和 write t2的操作，连写t1都不允许，自然也不能访问其他表。
在还没有出现更细类度锁的时候，表锁时最常用的处理并行方式。而对于InnoDB这种支持行锁的引擎，一般不适用lock tables命令来控制并发，毕竟锁住整张表的影响面或者说风险还是很大。
另一类表级锁是MDL（meta data lock）。MDL不需要显示的使用，在访问一张表的时候会被自动的加上。MDL的作用是保证读写的正确性。 我们可以想象一个，如果一个查询正在遍历表中的全部数据，而执行期间另一个线程对表结构做了变更，删了一列，那查询得到的结构跟表结构对不上，这肯定是不行的。
因此，在MySQL5.5中引入了MDL，当对一个表做增删改查的时候，加MDL读锁，当要对表结构做变更的时候，加MDL写锁。
- 读锁之间不互斥，因为我们可以有多个线程同时对一张表做增删改查。
- 读写锁之间、写锁之间是互斥的，用来保证变更变结构操作的安全性。因此，如果有两个线程要同时对一张表加字段，其中一个要等另外一个执行完毕才能继续执行变更。
虽然MDL是默认会加的，但确是我们不能忽略的一个机制。比如下面这个例子，对一个小表加一个字段，确导致整个库都挂了。
我们知道，当给一个表加字段、修改字段或者加索引的时候，需要扫描全表数据。在对大表操作的时候我们肯定会特别小心，以免对线上服务造成影响。而实际上即使是小表，操作不当也会引发大问题。我们来看这个例子，假设表t是一个小表，然后我们执行这面的操作：
image.png
我们看到Session A先启动，会对表t加一个MDL读锁，由于Session B也是读锁，所以可以正常执行。
之后Session C会被阻塞，是因为Session A的读锁还没有释放，而Session C的写操作只能阻塞。
如果只有Session C被阻塞到也没什么关系，但是之后所有要在t表上新申请MDL读锁的请求也都会被Session C阻塞。前面我们说了所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，相当于想在这张表完全不可读写了。
如果某张表上的查询语句频繁，而且客户端有重试机制，即超时会发起一个新的session请求，这样的话，很快这个库的线程就会爆满，最终的结果就是导致珍整个库down掉。
所以，现在我们应该事务的MDL锁，是在语句开始时申请，但在语句结束后并不会立马释放，而会等整个事务提交之后再释放。
基于上面的问题，我们来讨论如何安全的给小表加字段？
首先我们要解决长事务问题，事务不提交，就会一直占着MDL锁。在MySQL的information_schema库的innodb_trx表中可以查到正在执行的事务。如果我们要做DDL变更的表正好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。
还要另外一个场景：如果我们要执行DDL操作的这张表是一个热点表，它的数据不多，但是操作确非常的频繁，这时候我们要如何安全的给这张表加一个字段呢？
这时候kill可能未必管用，因为新的请求马上就会又来了。最理智的机制是，在DDL语句中加等待时间，即如果在这个指定的等待时间里能够拿到MDL写锁最好，但是如果拿不到也不要阻塞后面的业务，先放弃。之后开发人员或者DBA再通过重试命令来重试这个过程。
MariaDB已经合并了AliSQL的这个功能，所以这俩个开源分支目前都支持DDL NOWAIT/WAIT n这个语法：
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
行锁
MySQL的行锁是在引擎层由各个执行引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM就不支持行锁。不支持行锁以为着并发控制只能使用表级锁。对于这种引擎的表，同一张表上的任何时刻只能有一个更新在执行，这就会影响业务的并发性能。InnoDB是支持行锁的，这也是MyISAM被InnoDB取代的重要原因之一。
所以，下面我们主要来聊聊InnoDB中的行锁，以及如何减少锁冲突来提升业务的并发度。
顾名思义，行锁就是对MySQL表中的某行数据记录进行加锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新这一行，则必须等事务A的操作完成后才能执行。
当然，数据库中还有一些没那么一目了然的概念和设计，这些概念如果理解和使用不当，容易导致程序出现非预期行为，比如两阶段锁。
从两阶段锁说起
我先举个例子，在下面操作中，事务B的update语句执行会是什么现象？假设字段id是表t的主键。
image.png
这个问题的结论在于事务A在执行完两条update语句后持有哪些锁，以及在什么时候释放。我们可以验证一下：实际上事务B的update语句会被阻塞，知道事务的commit执行后，事务B的update才会继续执行。
知道了这个答案，一定知道了事务A持有两条记录的行锁，都是在执行了commit的时候才释放的。
也就是说，在InnoDB引擎中，行锁是需要的时候才加上的，但也并不是不需要的时候才释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
知道了这个设定，对我们使用事务有哪些帮助呢？那就是，如果你的事务需要锁多个行，要尽可能把最可能造成锁冲突、最可能影响并发度的锁往后面放。在来举个例子：
假设你负责一个电影票的在线交易业务，顾客曹操要在影院B购买电影票。我们简化一下，这个业务需要一下几个操作：
1.从顾客A账户余额扣除电影票价；
2.给影院B的电影票余额中增加这个票价；
3.记录一条交易日志
也就是说要完成这个交易，我们需要update两条语句，并insert一条记录。当然为了保证交易的原子性，我们要把这三个操作放到一个事务里。那么你会怎样安排这三个语句在事务中的顺序呢？
试下，这时候如果有另外顾客刘备也要在影院B购票，那么这两个事务冲突的部分就是语句2了，因为他们要修改同一条影院余额记录 / 同一行数据。
image.png
根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务commit的时候才释放的。所以，如果你把语句2安排在最后，比如3、1、2这样的顺序，那么影院B余额这一行自己的行锁占用的时间就会很少。这就最大成都减少了事务之间的锁等待时间，提高了并发度。
好了，现在由于我们的正确设计，影院B余额记录记录的对应的行锁在事务中不会停留太长的时间。但是，这可能并没有解决我们的困扰。
如果这个影院做活动，票价3折，活动只有一天时间。于是从活动时间开始，我们的告警平台就一直给我发送告警信息，“192.168.xxx.xxx机器CPU占用率过高！”， “192.168.xxx.xxx机器CPU占用率超过80%”， “192.168.xxx.xxx机器CPU占用率超过90%”……，然后紧接着我们MySQL就挂了。我们登上服务器
一看，CPU占用率98%，接近100%。但是整个数据库每秒的执行的事务可能也就一两百个，是不应该出现这情况的，好郁闷，什么原因，无从下手？？？一系列的问题困扰这我们。
这里我就要说到死锁和死锁检查了。
死锁和死锁检查
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源的时候，就会导致这几个线程进入无限等待的状态，成为死锁。
这里拿数据库行锁举个例子：
image.png
如图中说示，事务的执行顺序会遇到这样的问题：事务A在等待事务B释放ID=2的行锁，而事务B同时也在等待事务A释放ID=1对应的行锁。事务A和事务B在互相等待对方释放锁资源，就会进入死锁状态。
当出现死锁时，有两种策略:
1.一种策略是直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置;
2.另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以正常执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
在InnoDB中，innodb_lock_wait_timeout默认设置为50s，意味着如果采用第一个策略的话，第一个被锁住的事务需要等到50s后才能超时退出释放锁，其他事务也才能继续执行。对于在线服务来说，这个等待时间往往是不能接受的。
但是我们又不能直接把这个参数设置为一个很小的值，因为我们不确定会不会有正常的长事务，如果是正常的所等待，会出现很多误伤。
所以，正常情况还是采用策略二，即：主动检查死锁。而且innodb_deadlock_detect的默认值本身就是on。主动死锁监测在发生死锁的时候，是能够快速发现并处理的，但是他也是有额外负担的。
这个带来的负担其实很容易理解，我们试想一下，每一个事务被锁的时候，就要看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现循环等待，也就是死锁。这个过程是很耗CPU资源的。从时间复杂度从层近一步分析：每一个新来的被堵住的线程，都要判断是否由于自己的加入导致了死锁，这是一个时间复杂度O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检查就是100万个量级，虽然可能最终检查的结果是没有出现死锁，但是这个过程确消耗了大量的CPU资源。这也就是促销活动开始的时候，为什么一秒中执行的事务只有一两百个，但是CPU占用很高的原因。
那么要怎么解决由热点行更新导致的性能问题呢？ 问题的根因在于死锁检查会消耗大量的CPU资源。
一种头痛医头的方法，如果你能确保你的业务不会出现死锁，那么可以临时把这个死锁检测（innodb_deadlock_detect）关掉。但是这种操作本身就会有很大的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁就回滚，业务重试一般就没问题了，这个对业务是无损的。而关掉死锁检测可能出现大量超时，这个对业务是有损的。
另外一种思路是控制并发度。通过上面我们的一些分析，其实可以发现如果能够控制并发量，比如同一行同时最多只能有10个线程在更新，同时死锁检测的成本本身也很低，那就不会出现这个问题了。一个直接的想法就是在客户端做并发控制。但是很快我们会发现这个方法并不可行，因为客户端很多，有的服务客户端可能高达几百上千个的，这样即使每个客户端控制到只有5个并发线程，汇总到数据库服务端的时候也可能高达3000个线程数。
因此这个并发控制要作到数据库服务端。如果我们有中间件，可以考虑在中间件实现；如果你的团队有能够修改MySQL源码的人，也可以直接做到MySQL里面。基本思想就是，对于相同行的更新，在进入引擎之前排队。这样在InnoDB的内部就不会有大量的死锁检测工作了。
可能你会问，如果团队没有数据库方面的专家，不能实现这种方案，能不能从设计上优化这个性能。你可以考虑将一行改为逻辑上的多行来减少锁冲突。还是以上面影院订票设计为例，我们可以考虑将每个影院的账户余额记录拆分成多行，比如10条记录，影院B余额的总和等于 = 影院B1余额+影院B2余额+……+影院B10月总和。这样每次要给影院B余额加值的时候只需要随机选中其中一条操作即可。这样每次冲突概率变为原来的 1/10,可以减少锁等待个数，也就较少了死锁检测的CPU消耗。
这个方案看上去对业务是无损的，但仍需要花时间结合自己的业务逻辑去做详细设计。比如：如果账务余额可能会减少，退票业务。那么这时候可能需要考虑当一部分记录编程0时候需要特殊处理等。
总结
我的这篇笔记中记录了什么是全局锁、表级锁以及行锁。
全局锁主要用在逻辑备份的过程中，对于全部是InnoDB引擎的库建议使用-single-transaction参数，对应用会更友好。
表锁一般是对数据库引擎不支持行锁的情况下用到的。如果发现我们应用程序中存在locl tables这样的语句需要追查一下，可能的情况是：
1.要么是我们的系统还在使用不支持事务引擎类似于MyISAM，那建议安排更换引擎。
2.要么是我们的代码升级了，但是代码还没升级。需要将lock tables和unlock语句改为begin和commit。
MDL会自动对表进行操作的时候自动添加，同时MDL会在事务提交的时候才释放。所以在做表结构变更的时候，一定要小心，不要导致锁住线上的查询和更新。
行锁涉及了两阶段锁协议、死锁和死锁检测两大部分。其中以两阶段协议起点，记录了如果安排一个事务内读个操作的执行顺序，我建议：要把最有可能导致锁冲突、最有可能影响并发度的锁的申请时机尽量往后放，减少线程对锁的持有时间，可以减小死锁的发生概念。但是调整操作顺序并不能完全避免死锁的发生，所以又引入的死锁和死锁检测的概念，以及提供了三个方案，来减少死锁对数据库的影响。其中减少死锁的主要方向是：控制访问相同资源的并发事务量。
个人博客网站： RelaxHeart网
