MYSQL45讲学习笔记

最新推荐文章于 2024-06-16 05:34:45 发布

ruirui_6

最新推荐文章于 2024-06-16 05:34:45 发布

阅读量96

点赞数

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

本文链接：https://blog.csdn.net/ruirui_6/article/details/119788209

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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

加锁规则

这个加锁规则是老师阅读源码自己总结出来的，包括两个“原则”，两个“优化”和一个“bug”

原则一：加锁的基本单位是 next-key lock

原则二：被扫描到的行才会加锁

优化一：索引上的等值查询时，在给唯一索引加锁时，next-key lock 会退化成行锁。

优化二：索引上的等值查询时，如果不存在满足条件的数据，next-key lock 会退化成间隙锁（Gap lock）。

bug：唯一索引范围查询时，会继续向后查找，直到第一个不满足条件的记录为止。

为了验证上面的加锁规则，我们还是新建一张表，基于该表做说明。

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);

案例一：等值查询间隙锁

根据原则一，加锁单位为 next-key lock,session A 的加锁范围为（5,10],

因为没有id=7的数据，所以next- key lock 退化为间隙锁，加锁范围为（5,10）

用到了原则一和优化二

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

根据原则一，加锁单位为 next-key lock,session A 加锁范围为（0,5]（5,10]

因为id = 10 的数据不满足条件，所以（5,10] 退化为间隙锁，为（5，10）

所以最终加锁范围为索引C上（0，10）。

根据原则二，被扫描到的数据才会上锁，session A 使用覆盖索引，所以主键索引上没有任何锁，所以sessionB 可以执行成功，而session C 不能执行成功。

（用到了原则一、原则二和优化二）

但是需要注意，这里 session A 使用的是 lock in share mode,如果使用的是 for update ,那么情况就不一样了，MYSQL 会认为你要更新数据，索引在为索引C 加锁之后，会顺便给主键索引加锁。

同时它也指导我们，如果我们要利用 lock in share mode 来使数据不被更新，那么就必须绕过覆盖索引的优化，也就是需要加上覆盖索引上不存在的字段。

案例三：主键索引范围锁

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

上面这两条 SQL 语句在逻辑上等价的，但是在加锁规则上却不太相同，下面让我们来验证一下。

session A加锁范围：

1.开始的时候，找到第一个 id =10 的行，因此锁本该是 (5,10],根据优化一，退化为行锁。

2.范围查询，要找到第一个不满足条件的数据为止，因此索范围是（10,15]，所以，最终的锁范围是 id=5的行锁和(10,15]。

这样 session B 和 session C 的操作被阻塞就可以理解了。

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

与案例三相比，案例四的不同之处在于查询条件由 id 变为了字段 C。

session A 加锁规则：

1.条件查询首先找到 c=10的记录，加锁范围(5,10]

2.范围查询要找到第一个不满足条件的数据，加锁范围为 (10,15]

所以最终加锁范围为 (5,15] ,所以 session B 和 session C 会被阻塞。

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

在上面的案例中，我们已经介绍了加锁规则中的两个“原则”和两个“优化”，接下来我们来看下一个 “bug”

session A 加锁规则

主键索引范围查询，首先找到 ID =15 的行，加锁范围是(10,15], 因为是主键索引，所以扫描到 id=15的行应该就结束了，但是实际情况却不是这样。

实际会继续向后扫描，扫描到 id=20的行，发现不满足条件，结束，加锁范围是(15,20]。

所以最终加锁范围是 (10,15] 和 (15,20] 这两个 next-key lock。

这个bug在MYSQL 低版本中存在，本人使用的 MYSQL 版本是 8.0.25，亲测已经不存在这个问题。

案例六：非唯一索引上存在"等值"的例子

这个例子是为了更好的说明“间隙”这个概念。

这里我们给表插入一条数据

insert into t values(30,10,30);

于是索引C 变成了这个样子：

这里我们采用了delete 语句，加锁规则和 select .... for update 是一样的。

接下来我们来分析一下 session A 的加锁规则。

1.首先在索引C上扫描到 c=10 的行，加锁范围为 (5,10] 和(10,15]

2.根据优化二，(10,15] 退化为间隙锁，(10,15)

最终加锁范围为 (5,10] 和(10,15)

图中虚线代表开区间，表示 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。

案例七：limit 语句加锁

案例六也有一个对照案例，如图所示：

区别是加上了 limit 2，导致加锁范围产生了变化。

session A 加锁范围：

因为我们只删除两条数据，所以在扫描到 (c=10,id=30)时就停止了，

所以，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

由此我们可知，在删除数据的时候我们尽量加上 limit。这样不仅可以控制删除的条数，让操作更安全，同时可以减小加锁范围。

案例八：一个死锁的例子

这个例子是为了说明 next-key lock 是由间隙锁和行锁组成的，以及 next-key lock 的加锁顺序。

1.session A 查询 C=10 的主键id，在覆盖索引上加锁 (5,10] 和 (10,15)

2.session B 操作 c=10 的记录，被阻塞，同时 session B 需要在索引C上加 (5,10] 这个next-key lock, 因为加 next-key lock 被阻塞，所以无法加间隙锁(10,15)

3.session A 插入（8，8，8）的记录，被session B 的间隙锁阻塞，系统检测到死锁，让 session B 回滚。

这里因为 next-key lock 的加锁顺序是，先对(5,10) 加间隙锁，再加 c=10 的行锁，到行锁这里卡住了。

也就是说，next-key lock 加锁的时候是按照间隙锁和行锁两阶段来加锁的。

小结

本文介绍了MYSQL 在可重复读隔离级别下的加锁规则，同时，所有的锁资源遵循两阶段锁协议，在事务回滚或提交的时候释放。

一般情况下，只有在可重复读隔离级别下，才会存在间隙锁。

但是在读提交隔离级别中，有外键的情况下，也可能存在间隙锁。

同时读提交隔离级别下还有一个优化，即：在语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件数据”的行锁释放掉，不需要等到事务提交或者回滚。

ruirui_6

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL45讲学习笔记

20.幻读是什么，幻读有什么问题？本节我们还是建一张表，依据这张表来说明问题。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,1
复制链接

扫一扫