MySQL全局锁、表锁、行锁

最新推荐文章于 2023-08-12 10:15:38 发布

哪里的船迷醉了夕阳

最新推荐文章于 2023-08-12 10:15:38 发布

阅读量939

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_41784433/article/details/121288381

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

学习来源： MySQL45讲

1、前言

数据库锁设计的初衷是处理并发问题的。
根据加锁范围可以分为三类：全局锁、表级锁、行锁。

2、全局锁

全局锁就是对整个数据库实例加锁。
提供加全局读锁命令：Flush tables with read lock(FTWRL)。
当需要让整个库处于只读的状态，可以使用上述命令，之后其他线程的语句将会被阻塞：数据库更新（增删改）、数据库定义（建表、修改表结构等）还有更新类事物的提交语句。
典型场景：做全库逻辑备份。即：把整库每个表都select出来存成文本。在备份过程中，整个库完全处于只读状态。

2.1、只读的危险

在主库上备份，期间不能执行更新，业务基本上停摆。
在从库上备份，期间从库不能执行主库同步过来的binlog，会导致主从延迟。

2.2、为什么备份要加锁

45讲给出的示例：现在维护的是购买系统，关注的是用户账户余额表以及用户课程表。
现在发起一个逻辑备份，期间有一个用户购买了一门课，业务逻辑就要扣掉他的余额，然后再已购买课程加上这门课。
备份顺序：先备份余额表（u_account），然后用户再购买，接着才备份用户课程表（u_course）。过程：
1. 问题：用户A的余额没有扣除，但是课程表已经多了一门课。这样后面用这个备份来恢复数据，那么用户就赚了。
2. 反过来的话：就是用户的钱扣了，但是课没了，商家赚了。
上述问题就是不加锁的情况，即：备份系统备份得到的库不是一个逻辑时间点，视图逻辑不一致。
那么如何拿到一个一致性的视图呢？在可重复读的隔离级别下开启一个事务（只要没当前用户没提交，读取的数据绝对一致）。
官方自带的逻辑备份工具是mysqldump。当使用参数-single-transaction的时候，那么在导入数据之前会启动一个事务，确保拿到一致性视图。由于MVCC的支持，这个过程中数据是可以正常更新的。
既然有这个备份工具了，为何还要FTWRL（执行该语句，会让整个库处于只读的状态）？一致性读好是好，但也得引擎支持这个隔离级别吧。像MyISAM不支持事务的引擎，如果备份过程中有更新，那么备份总是能取到最新的数据，破坏了备份的一致性。此时就需要使用FTWRL命令。即：虽然InnoDB引擎在RC、RR隔离级别是基于MVCC进行并发事务控制，但是前提是引擎要支持事务，MyISAM是不支持事务的，如果有些表使用的引擎是MyISAM的，此时进行备份加锁，就必须使用Flush tables with read lock(FTWRL)。
-single-transaction方法只适用于所有表都使用事务引擎的库。如果有表使用了不支持事务的引擎，备份只能通过FTWRL方法。
既然要全库只读，为何不适用set global readonly = true？该命令也可以让全库进入只读状态，但45讲建议还是使用FTWRL：
1. 有些系统中，readonly的值会被用来做其他逻辑，比如判断一个库是主库还是从库。因此，修改global变量的方式影响面更大。
2. 执行FTWRL之后由于客户端发生异常断开，MySQL会自动释放全局锁，整个库回到可以正常更新状态。设置readonly之后，客户端异常，那么数据库就会一直保持readonly状态，会导致整个库长时间不可写状态，风险较高。

3、表级锁

表级锁又分为：表锁和元数据锁（meta data lock，MDL）。
表锁语法：lock table .. read/write。和FTWRL类似，可以使用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。
lock tables语法除了限制别的线程读写之外，还会限制本线程接下来的操作对象。
举例：线程A执行到了lock tables t1 read, t2 write，其他线程写t1读写t2都会被阻塞。并且线程A执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不运行，也不能访问其他表。
表锁是最常用的处理并发的方式。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面太大。

3.1、元数据锁（MDL）

MDL不需要显示使用，在访问一个表的时候会被自动加上。
MySQL5.5版本引入MDL，作用：保证读写正确性。
当对一个表做增删改查的时候，加MDL读锁；当对表做结构变更操作的时候，加MDL写锁。
1. 读锁之间不互斥，可以有多个线程同时对一张表增删改查。
2. 读写、写写是互斥的，用来保证变更表结构操作的安全性。如果两个线程同时给一个表加字段，其中一个要等令一个执行完才能执行。
给一个表加字段或者修改字段或者加索引，都需要扫描全表数据。在对大表操作的时候，会很小心避免对线上服务造成影响。实际上，即便是小表，操作不当也会出错的。
举例：表t是一个小表
1. session A先启动，对表t加一个MDL读锁，session B需要的也是MDL读锁，也是可以正常执行的。
2. 但是到了session C会被阻塞，因为session A的MDL读锁还没有释放，session C需要MDL写锁，因此会被阻塞。
3. 如果只有session C自己被阻塞还没有什么问题，但之后所有要在表t上申请MDL读锁的请求，都会被session C阻塞。因为所有表的增删改查都需要先申请MDL读锁（但由于此时session C的写锁还没有释放，所以后面的读锁也都是获取不了的），这样的话都被锁住了，意味着这个表现在完全不可不写了。
4. 如果某个表上的查询语句很多，而且客户端有重试机制，即：超时之后会再起一个新的session 再请求，这样反复如此，这个库的线程很快就会爆满。
事务中的MDL锁，在语句执行开始申请，但是语句结束后并不会发生释放而是等到整个事务提交了之后再释放。就是因为有未提交的事务的时候无法修改表字段，而且在存在长事务的时候执行修改表字段命令可能阻塞其他增删改查请求，或者导致线程爆满。

3.1.1、安全给小表加字段

首先解决长事务，事务不提交，就一直占着MDL锁。长事务会严重威胁并发性，也会导致回滚段（undolog）不能回收长时间被占用空间。
场景假设：变更表是一个热点表，表不大，但是上面请求很频繁，而不得不加一个字段，如何做？
1. 理想机制：在alter table语句里面设定等待时间，如果在这个指定的等待时间内能拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

4、全局锁和表锁思考问题

备份都是在备库上执行的，在用-single-transaction方法做逻辑备份过程中，如果主库的一个小表做了表结构改变，比如给表加上了一列，此时备库会看到什么现象？
```
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
```
1. 备份开始的时候，为了确保可重复读的隔离级别，再设置一次可重复读的隔离级别（Q1）。
2. 启动事务，使用with consistent snapshot确保这个语句执行完就可以得到一个一致性视图（Q2）。
3. 设置一个保存点（Q3）。
4. show create table t1是为了拿表结构（Q4）。
5. 正式导入数据（Q5）。
6. 回滚到sp，作用是释放t1的MDL锁（Q6）。
参考答案：
1. 如果在Q4语句执行之前到达，没有影响，备份拿到的时候DDL后的表结构。（因为Q4语句是显示表结构，而在这之前，表结构已经发生变化了，所以此时得到的是修改之后的表）
2. 如果在时刻2到达，那么表结构被改过，Q5执行的时候，现象：mysqldump（mysql自带的逻辑备份）终止。（show出来是之前表的结构，时刻2到达对表进行变更，那么逻辑备份停止）
3. 如果在时刻2和时刻3之间到达，mysqldump占着t1的MDL的读锁（此时是在进行select），binlog被阻塞（因为binlog需要获MDL的写锁得），会出现主从延迟的现象，直到Q6执行完成。
4. 从时刻4开始，mysqldump释放了MDL读锁（select执行完毕），现象：没有影响。备份拿到的是DDL前的表结构（因为此时select执行完毕了，binlog得到了写锁，才是进行记录，那么记录的就是之前的表结构）。

总结：MDL锁分为读锁和写锁，读读不冲突，读写、写写冲突，前面的事务如果没有做事务的改变，那么该事务是可以拿到读锁的，想要获取写锁（针对数据表结构变更）的话，就必须等到前面的事务释放写锁才可以。

5、行锁

5.1、概述

MySQL的行锁是在引擎层由各个引擎自己实现的。但并非所有的引擎都支持行锁。
不支持行锁意味着并发控制只能使用表锁，使用表锁的话，同一张表任何时刻只能有一个更新在执行，影响业务的并发度。
InnoDB是支持行锁的。
行锁是针对数据表中行记录的锁。比如：事务A更新了一行，而此时事务B也要更新同一行，那么就必须等到事务A操作完成之后才能进行更新。

5.2、两阶段锁

讨论：事务B的更新语句执行的时候是什么现象？
1. 要想知道结果，就需要知道事务A在执行完两条update语句后，拥有哪些锁，以及持有的锁什么时候释放。
2. 实际上事务B的更新语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。
3. 那么结果就是事务A持有了两个记录的行锁，并且是在commit的时候才释放。
结论：InnoDB事务中，行锁是在需要的时候加上的，但是并非不需要就立刻释放，而是等到事务结束的时候才释放。这就是两阶段锁协议。
如果事务中需要锁多行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
场景举例：负责的是一个电影票在线交易业务，顾客A要在影院B买电影票，此时的业务操作：
1. 从顾客A的账户余额扣除电影票价。
2. 给影院的账户余额加上这张电影票价。
3. 记录多一条交易日志。
分析完业务操作之后，完成交易需要更新两条记录，同时插入一条记录。同时为了保证交易的原子性，要把这三个操作放在一个事务中。如何安排这三个语句在事务中的顺序？
如果此时同时有另外一个顾客C也在影院B买票，那么这两个事务冲突的部分就是给影院B的账户余额增加票价。因为是更新同一个影院账户的余额，修改同一行记录。
根据二阶段锁协议，不管怎么安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。如果把这条更新语句放在最后，按照312顺序，那么影院账户余额这一行的锁时间最少，就最大程度减少了事务之间的锁等待，提高了并发程度。
上面这样的设计，影院余额这一行的行锁在一个事务中不会停留很长时间，但这并未完全解决。
场景翻新：假如这个影院做活动，可以低价预售一年所有电影票，且活动只做一天。那么活动开始的时候，你的MySQL就挂了，进行查看的时候，CPU消耗近100%，但数据库每秒执行不到100个事务，原因就是出现死锁了。

总结两阶段锁：其实就是一个事务在对某一行进行更新的时候，只有commit之后，其他的事务才能进行更新，这就是两阶段锁。并且MVCC也使用到行锁，如果只是单纯靠MVCC，那么在事务B在更新前后得到的readview是不一致的，但是此时隔离级别又是RR的，所以产生冲突，个人理解是因为此时事务A还没有进行commit，那么事务B在更新之后是会被阻塞的，更别说什么select了，所以前后的readview还是一致的。

5.3、死锁和死锁检测

死锁：并发系统中不同线程出现循环资源依赖，设计的线程都在等待别的线程释放资源的时候，就会导致几个线程都进入无线等待的状态。
举例：
1. 事务A在等待事务B释放id=2的行锁，而事务B在等事务A释放id=1的行锁，此时双方都在等待对方进行资源释放，此时就进入了死锁状态。
出现死锁的策略：
1. 直接进入等待，直至超时。可以通过设置参数，在InnoDB中，innodb_lock_wait_timeout默认值是50s，即：出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才可能继续执行。但对在线服务来说，50s无法接受。但如果时间设置很短，会出现很多误伤
2. 发起死锁检测，出现死锁之后，主动回滚死锁链条中的某一个事务，让其他事务可以继续执行。大多数都选择这个策略。并且innodb_deadlock_detect默认值就是on。主动死锁检测发生死锁的时候是能够快速发现并且进行处理的，但也有额外负担。
  1. 场景假设：每当一个事务被锁的时候，就要看它所依赖的线程有没有被别人锁住，反复如此，最后判断是否出现了死锁。
  2. 但如果所有事务都是更新同一行呢？
  3. 每一个新来被堵住的线程，都要判断会不会由于自己的加入导致死锁，死锁检测的时间复杂度是 $O(n^2)$ 的，加入有100个并发线程同时更新同一行，那么死锁检测就是100w量级的。如果检测完是没有死锁的，此时CPU被大量消耗，被大量消耗的时候每秒却执行不了几个事务。
3. 如何解决由这种热点行（经常被更新的行）更新导致的性能问题？因为死锁检测是要花费大量CPU资源的。
  1. 方法1：当能确保业务一定不会出现死锁，那么就可以临时把死锁检测关掉。
  2. 方法2：控制并发度。如果同一行同时只有10个线程更新，那么此时死锁检测的成本就很低，不会出现上述问题。那么最直接的想法就是客户端做并发控制，但这好像不太行，因为客户端太多了。那么只能做在数据库服务端。如果有中间件，可以考虑在中间件实现；如果能修改MySQL源码，也可以在里面做。思路就是：对于同行更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测了。
  3. 可否从设计上去优化？可以考虑通过将一行改成逻辑上的多行从而减少锁冲突。
  4. 场景举例：同样是影院，可以考虑放在多条记录上，比如10个记录，影院账户总额等于这10个记录的值总和，在每次要个影院账户增加金额的时候，随机选其中一条记录加。这样使得冲突几率变为原来的1/10，也减少锁等待个数，即：减少了死锁检测的CPU消耗。