MySQL（3.1）行锁、死锁原理及分析

最新推荐文章于 2024-05-09 22:38:32 发布

Miaoshuowen

最新推荐文章于 2024-05-09 22:38:32 发布

阅读量309

点赞数 2

分类专栏： # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Miaoshuowen/article/details/104882798

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

行锁原理分析

一条简单SQL的加锁分析

下面两个SQL加什么锁？

#SQL1
select * from t1 where id = 10;

#SQL2
delete from t1 where id = 10;

针对这个问题，该怎么回答？能想象到的一个答案是：

SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。
SQL2：对id = 10的记录加写锁 (走主键索引)。

这样的回答准确吗？在前提条件不明的时候，前提不同给出的答案也不一样，那么要回答这个问题需要哪些前提条件呢？

前提一：id列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：id列如果不是主键，id列上有没有索引？
前提四：如果id列上有次要索引，那么这个索引是唯一索引吗？
前提五：这两个SQL执行计划是什么？索引扫描还是全表扫描？

没有这些前提，直接就给定一条SQL，然后问这个SQL会加什么锁，都是很业余的表现。而当这些问题有了明确的答案之后，给定的SQL会加什么锁，也就一目了然。下面，我们将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁

注：下面的这些组合，需要做一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤 (索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。

组合一：id列是主键，RC隔离级别 
组合二：id列是二级唯一索引，RC隔离级别 
组合三：id列是二级非唯一索引，RC隔离级别 
组合四：id列上没有索引，RC隔离级别 
组合五：id列是主键，RR隔离级别 
组合六：id列是二级唯一索引，RR隔离级别 
组合七：id列是二级非唯一索引，RR隔离级别 
组合八：id列上没有索引，RR隔离级别 
组合九：Serializable隔离级别

注：在前面八种组合下，也就是RC，RR隔离级别下
SQL1的select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了

主要讨论SQL2：delete操作的加锁。

组合一：id主键+RC

id是主键
RC隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

组合二：id唯一索引+RC

id不是主键，是唯一索引。主键是name列。
RC隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
分析过程：

此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁。
同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。

为什么聚簇索引上的记录也要加锁？

试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name= ‘d’;
此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：

若id列是unique列，其上有unique索引，那么SQL需要加两个X锁。一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的【name=’d’,id=10】的记录。

组合三：id非唯一索引+RC

id列不再唯一，只有一个普通的索引。主键是name列。
RC隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
分析过程：

首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：

若id列上有非唯一索引，那么对应的所有满足SQL查询条件的索引，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

组合四：id无索引+RC

id列上没有索引
RC隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
由于id列上没有索引，因此只能走聚簇索引，进行全表扫描。

分析过程：

从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。
有人可能会问？为什么不是只在满足条件的记录上加锁呢？
这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。
但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id主键+RR

id列是主键列
RR隔离级别
delete from t1 where id = 10;
结论：

与组合一：[id主键+RC]一致。

组合六：id唯一索引+RR

id列是唯一索引列
RR隔离级别
delete from t1 where id = 10;
结论：

与组合二：[id唯一索引+RC]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

组合七：id非唯一索引+RR

RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？

id列是非唯一索引列
RR隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
此图，相对于组合三：[id列上非唯一锁+RR]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？间隙锁详见此条博客链接

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。
所谓幻读，就是同一个事务，连续做两次当前读 (例如： select * from t1 where id = 10 for update; )，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

分析过程：

如图中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。
记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。
Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是
RR隔离级别，却不需要加GAP锁呢？

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用

结论：

首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；
然后读取下一条，重复进行。
直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id无索引+RR

id无索引。主键是name列。
RR隔离级别
delete from t1 where id = 10;
分析过程如下图所示：
在这里插入图片描述
分析过程：

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？
在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。
当然，跟组合四：[id无索引, Read Committed]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数。

结论：

在RR隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。
当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。
对于SQL2来说，Serializable隔离级别与RR隔离级别完全一致，因此不做介绍。
Serializable隔离级别，影响的是SQL1这条SQL：在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也
就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

结论：

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

一条复杂SQL的加锁分析

SQL用例如下：
在这里插入图片描述
如图中的SQL，会加什么锁？假定在RR隔离级别下，同时，假设SQL走的是idx_t1_pu索引。

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？ICP详解博客链接

Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter：userid = ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。
Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：在这里插入图片描述
从图中可以看出，在RR隔离级别下，由Index Key所确定的范围，被加上了GAP锁；

Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤，视MySQL的版本而定，在MySQL5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。
若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁；若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；
而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。
最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

结论：

在RR隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；
Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；
Table Filter过滤条件，无论是否满足，都需要加X锁，加锁的数量，要远远大于满足条件的记录数量。

死锁原理与分析

深入理解MySQL如何加锁，有两个比较重要的作用：

可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

死锁情况一
在这里插入图片描述
这个死锁非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

死锁情况二
在这里插入图片描述

第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。
要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。
针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。
而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了

结论：

死锁的发生与否，并不在于事务中有多少条SQL语句，【死锁的关键在于】：
两个(或以上)的Session【加锁的顺序】不一致。
而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因

如何解决死锁呢？

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

如何避免死锁

1、注意程序的逻辑
根本的原因是程序逻辑的顺序，最常见的是交替更新
Transaction 1: 更新表A -> 更新表B
Transaction 2:更新表B -> 更新表A
Transaction获得两个资源
2、保持事务的轻量
越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
3、提高运行的速度
避免使用子查询，尽量使用主键等等
4、尽量快提交事务，减少持有锁的时间
越早提交事务，锁就越早释放

Miaoshuowen

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL（3.1）行锁、死锁原理及分析

行锁原理分析一条简单SQL的加锁分析下面两个SQL加什么锁？#SQL1select * from t1 where id = 10;#SQL2delete from t1 where id = 10;针对这个问题，该怎么回答？能想象到的一个答案是：SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。SQL2：对id = 10的记录加写锁 (走主键索引)。这...
复制链接

扫一扫

专栏目录