没有合适的资源？快使用搜索试试~ 我知道了~

文库首页安全技术网络安全21.为什么我只改一行的语句锁这么多？1

21.为什么我只改一行的语句锁这么多？1

mysql

需积分: 0 0 下载量 123 浏览量 2022-08-03 16:03:42 上传评论收藏 981KB PDF 举报

温馨提示

试读

22页

1. MySQL后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x系列<=5.7.24，8.0系列 <=8.0.13 2. 如果大

资源详情

资源评论

资源推荐

21 | 为什么我只改一行的语句，锁这么多？

2018-12-31 林晓斌

在上一篇文章中，我和你介绍了间隙锁和next-key lock的概念，但是并没有说明加锁规则。间隙

锁的概念理解起来确实有点儿难，尤其在配合上行锁以后，很容易在判断是否会出现锁等待的问

题上犯错。

所以今天，我们就先从这个加锁规则开始吧。

首先说明一下，这些加锁规则我没在别的地方看到过有类似的总结，以前我自己判断的时候都是

想着代码里面的实现来脑补的。这次为了总结成不看代码的同学也能理解的规则，是我又重新刷

了代码临时总结出来的。所以，这个规则有以下两条前提说明：这个规则有以下两条前提说明：

1. MySQL后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即

5.x系列<=5.7.24，8.0系列 <=8.0.13。

2. 如果大家在验证中有发现bad case的话，请提出来，我会再补充进这篇文章，使得一起学习

本专栏的所有同学都能受益。

因为间隙锁在可重复读隔离级别下才有效，所以本篇文章接下来的描述，若没有特殊说明，默认

是可重复读隔离级别。

我总结的加锁规则里面，包含了两个我总结的加锁规则里面，包含了两个““原则原则””、两个、两个 ““优化优化””和一个和一个 “bug”“bug”。。

1. 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

2. 原则2：查找过程中访问到的对象才会加锁。

3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key

lock退化为间隙锁。

5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

我还是以上篇文章的表t为例，和你解释一下这些规则。表t的建表语句和初始化语句如下。

接下来的例子基本都是配合着图片说明的，所以我建议你可以对照着文稿看，有些例子可能

会“毁三观”，也建议你读完文章后亲手实践一下。

案例一：等值查询间隙锁案例一：等值查询间隙锁

第一个例子是关于等值条件操作间隙：

图1 等值查询的间隙锁

由于表t中没有id=7的记录，所以用我们上面提到的加锁规则判断一下的话：

CREATE TABLE `t` (

`id` int(11) NOT NULL,

`c` int(11) DEFAULT NULL,

`d` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `c` (`c`)

) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),

(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1. 根据原则1，加锁单位是next-key lock，session A加锁范围就是(5,10]；

2. 同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间

隙锁，因此最终加锁的范围是(5,10)。

所以，session B要往这个间隙里面插入id=8的记录会被锁住，但是session C修改id=10这行是可

以的。

案例二：非唯一索引等值锁案例二：非唯一索引等值锁

第二个例子是关于覆盖索引上的锁：

图2 只加在非唯一索引上的锁

看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

这里session A要给索引c上c=5的这一行加上读锁。

1. 根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。

2. 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到

c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。

3. 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此

退化成间隙锁(5,10)。

4. 根据原则2 ，只有访问到的对象才会加锁只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索

引，所以主键索引上没有加任何锁，这就是为什么session B的update语句可以执行完成。

但session C要插入一个(7,7,7)的记录，就会被session A的间隙锁(5,10)锁住。

需要注意，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样

了。执行 for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的

行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用lock in share mode

来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不

存在的字段。比如，将session A的查询语句改成select d from t where c=5 lock in share mode。

你可以自己验证一下效果。

案例三：主键索引范围锁案例三：主键索引范围锁

第三个例子是关于范围查询的。

举例之前，你可以先思考一下这个问题：对于我们这个表t，下面这两条查询语句，加锁范围相

同吗？

你可能会想，id定义为int类型，这两个语句就是等价的吧？其实，它们并不完全等价。

在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样。现在，我们就让

session A执行第二个查询语句，来看看加锁效果。

图3 主键索引上范围查询的锁

现在我们就用前面提到的加锁规则，来分析一下session A 会加什么锁呢？

mysql> select * from t where id=10 for update;

mysql> select * from t where id>=10 and id<11 for update;

1. 开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，

主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。

2. 范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。

所以，session A这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]。这

样，session B和session C的结果你就能理解了。

这里你需要注意一点，首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而

向右扫描到id=15的时候，用的是范围查询判断。

案例四：非唯一索引范围锁案例四：非唯一索引范围锁

接下来，我们再看两个范围查询加锁的例子，你可以对照着案例三来看。

需要注意的是，与案例三不同的是，案例四中查询语句的where部分用的是字段c。

图4 非唯一索引范围锁

这次session A用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时

候，索引c上加了(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是

说不会蜕变为行锁，因此最终sesion A加的锁是，索引c上的(5,10] 和(10,15] 这两个next-key

lock。

所以从结果上来看，sesson B要插入（8,8,8)的这个insert语句时就被堵住了。

这里需要扫描到c=15才停止扫描，是合理的，因为InnoDB要扫到c=15，才知道不需要继续往后

找了。

案例五：唯一索引范围锁案例五：唯一索引范围锁bugbug

前面的四个案例，我们已经用到了加锁规则中的两个原则和两个优化，接下来再看一个关于加锁

剩余21页未读，继续阅读

内容反馈

蟹蛛

粉丝: 22
资源: 323

上传资源快速赚钱

我的内容管理展开

我的资源快来上传第一个资源

我的收益

登录查看自己的收益

我的积分登录查看自己的积分

我的C币登录后查看C币余额

我的收藏

我的下载

下载帮助

前往需求广场，查看用户热搜

21.为什么我只改一行的语句锁这么多？1

评论0

最新资源

21.为什么我只改一行的语句锁这么多？1

评论0

21为什么我只改一行的语句，锁这么多？.pptx

40.insert语句的锁为什么这么多？1

19.为什么我只查一行的语句，也执行这么慢？1

19.为什么我只查一行的语句也执行这么慢？1

mysql45讲 19.为什么我只查一行的语句，也执行这么慢？1

Oracle编程艺术

史上最强全面解析SQLite

Sybase ASE 15.7 开发文档：系统管理指南（卷二）

Oracle从入门到精通

40insert语句的锁为什么这么多？.pptx

19为什么我只查一行的语句，也执行这么慢？.pptx

MySQL45讲读书笔记 40讲insert语句的锁为什么这么多

19 为什么我只查一行的语句，也执行这么慢？.pdf

【MySQL数据库】一条SQL语句为什么执行这么慢？

Mysql实战深层原理讲解

ORACLE锁深入分析

达梦数据库_SQL语言手册

精通sql结构化查询语句

oracle数据库经典题目

mysql高级视频教程百度云(2019).txt

python简明教程.chm

1350多个精品易语言模块

Oracle数据库学习指南

Java面试宝典2020修订版V1.0.1.doc

1345个易语言模块

Linux与unix shell编程指南1.rar

ZendFramework中文文档

最新资源