MySQL 只改一条数据我这么难的吗

   “我一直觉得在休息的同时,也是人和人拉开差距的时候。我之前喜欢说一句话,生时何必久睡,死后自然长眠。现在我觉得早睡早起身体好。

Laravel

本篇文章是学习极客专栏幻读后所写的,文章中一些总结来源于作者。

开篇

如果没有特别说明,本篇的内容都是在可重复读级别事务下进行,因为为了解决幻读,InnoDB 引入了间隙锁🔐,而间隙锁是在可重复读的隔离级别下才会生效的,所以如果你把隔离级别设置成读提交的情况下,就不存在间隙锁了,当然你需要面对的是其他出现的问题了,也不在本篇文章的讨论之中,本篇文章主要是以实践为主。

开始之前运行一下命令查看自己 InnoDB 事务隔离级别。

show variables like 'transaction_isolation'

Laravel

幻读的定义

为了便于分析,我们开始在数据库中创建如下表,并初始化插入一些数据。把id设置为主键索引,字段c设置为普通索引,并插入六条数据。

Laravel

接着我们来看下面的一条语句,会咋么进行加锁。

begin;
select * from t where d=5 for update;
commit;

比较好理解是,这条语句会查询到 d=5 这一行,也就是记录(5,5,5),对应的主键 id =5,这里的 for update 会在查询完成之后,給当前主键 id 等于5的这条记录加一个写锁,由于两阶段锁的协议,这个写锁会在事务 commit 之后才释放。

现在的问题在于,如果只是在 id =5 这一行上加锁,而其他行不加锁的情况下会发生什么?下面模拟多个事务同时进行的情况。

Laravel

从上面可以看到事务A执行了三次查询,并且都是 for update,表示当前读,按照我们说的,如果只是给 id=5 这一行加上写锁,这时候事务 B 的 T2 时刻在编辑 id=0 这一条记录的时候并没有被锁住,所以事务 B 的的更新语句可以执行,那么 id=0 的当前的记录变成(0,0,5),事务 B 并没有像 A 一样显式的开启事务,代表的这一条更新就是事务,更新完毕事务提交,事务 A 的 T3 时刻当前读,条件语句还是 where,所以可以读取事务 B 提交的修改。T4 时刻 C 事务插入一条记录,所以 T5 时刻 A 事务查询的结果是3条。接着 T6 时刻事务 A 提交事务,释放锁。

这里需要说明的是,在 T5 时刻读到 id=1 这一行数据才称之为幻读,幻读指的是在一次事务中前后两次查询同一范围数据的时候,后一次查询看到了前一次查询没有看到的行,事务 A 的 T3 时刻查询 where d=5 的时候,此时并没有 id=1 这一行数据,等到事务 A 的 T5 时刻查询的时候由于事务 C 的 T4 时刻插入了一条 id=1 的数据,导致了 id=1 这一行的幻读,所以你可以知道,幻读仅仅指新插入的行,所以对于 id=0 的修改不能算是幻读。**

那么按照我们想的有什么问题吗?当然有问题。首先是事务 A 在 T1 时刻就声明要把所有 d=5 的行锁住,但是现在这个语义已经被破坏了。让我们来稍微修改下列子。

Laravel

由于事务 A 的 T1 时刻只是锁住 id=5 这一条数据加了行数,但是并没有给 id=0 的数据加上行锁,导致此时即便它的 d=5 也可以进行更新操作,至于事务 C 在事务 A 加锁的时候记录都还没生成,导致它也能进行修改操作。这两条语句都破坏了事务A 在 T1 时刻的加锁声明。

另一点在于数据一致性的问题。如果我们再在事务 A 那稍微改动一下。

Laravel

update 加锁的语义和 select ..... for update 的语义是一致的,所以这时候加上 update 也很合理。如果按照这个流程走过来,会发生什么,注意事务 A的事务要到 T6 时刻才提交。

当然有问题了,等到 T6 时候事务 A 提交事务的时候,会将所有 d=5 记录的 d 值修改成 1000,这就造成了本来正常情况下事务 B 的记录是(0,5,5),事务 C 的记录是 (1,5,5)。这时候三行 d=5 的数据变成了 (0,5,100),(1,5,100)和(5,5,100)。这时候就发生了数据不一致。

所以综上所述,如果在 select * from t where d=5 for update 的时候只是给 id=5 这一行加上行锁是不行的。如果我们把 select 扫描到的行全部加上行锁呢?那么事务 B 确实没问题,因为在它要更新的时候,事务 A 的写锁导致它被锁住,需要等到事务 A 提交事务才得以进行,所以没问题。但是事务 C 就不一样了,因为在事务 A 锁住扫描行的时候,id=1 这条记录还没诞生呢,所以也就不存在被锁住了,这也就是幻读的问题还是没解决

如何解决幻读

幻读产生的原因就是行锁只能锁住行, 但是对于新插入的记录,更新的是行之间的间隙,InnoDB 为了在可重复读的情况下解决幻读问题,引入了间隙锁(Gap Lock)。

这样当你再次执行 select * from t where d =5 for update 的时候,就不仅仅只是给6条记录加上行锁,还同时加上7个间隙锁。这样能确保的是在一个事务锁的期间,没有新的记录能 insert 进来,就解决了幻读的问题。

Laravel

间隙锁之前不互锁。比如说。

Laravel

上面的语句事务 B 并不会被锁住,因为表中没有 c=7 这条记录,所以事务 A 加的是间隙锁(5,10),而事务 B 也是加的(5,10) 的间隙锁,他们保护的是共同的目标,就是保护这个间隙不被插入新值,所以他们并不冲突。

间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间。也就是说,我们的表 t 初始化以后,如果用 select * from t for update 要把整个表所有记录锁起来,就形成了 7 个 next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

这里的 supremum 的值是多少呢。实际上 InnoDb 给每一个索引加了一个不存在的最大值 supremum ,这样就符合前开后闭的准则了。

间隙锁在解决幻读的同时,也会带来新的问题,比如下面的。

Laravel

我们来实验一下,看发生了什么。事务 B 在插入的时候会被锁住。

Laravel

当我在事务 A 中执行同样的操作时,事务 A 此时也被锁住了,这时候已经造成了死锁,两个事务互相等待对方释放锁的资源,形成了死锁,当然 InnoDB 马上监测到死锁,让事务 A 的 insert 语句直接报错返回并且释放锁,所以此时可以看到事务 B 获取到锁马上执行插入成功。

Laravel

这两个事务的执行语句加锁过程又是则么样的呢?

  1. 事务 A 执行 select * from t where id =9 ,因为 id=9 并不存在,所以语句加上间隙锁(5,10)。

  2. 事务 B 执行 select * from t where id =9, 因为 此时 id=9 不存在,所以事务 B 也加上间隙锁(5,10)。

  3. 当事务 B 执行 insert (9,9,9) 的时候 ,被事务 A 的间隙锁锁住了。

  4. 当事务 A 执行 insert (9,9,9) 的时候,被事务 B 的间隙锁锁住了。

  5. 因此产生了死锁。

在引入间隙锁之后,可能会导致同样的语句锁住更大的范围。影响了并发性。为了解决幻读,我们竟然引入了这么多东西,还能咋么搞?开篇就已经说过了,间隙锁是在可重复读的隔离级别下才会出现的,如果改为读已提交的情况下,就没有这些问题了,当然出现的问题就是读已提交情况下能出现的问题了😃。

上面介绍的只是间隙锁和 next-key lock 的概念,还没有实际开始加锁的规则,下面开始一些实践。MySQL 在后续版本中可能会修改加锁的规则,所以以下的内容版本应该在 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13。

首先上面提到过间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间,这也是加锁的基本单位。接下来我们来看第一个例子。等值查询的间隙锁。

Laravel

思考下结果是什么?我们来简单分析一下,事务 A 在 T1 时刻进行加锁的操作,由于当前记录中并不存在 id=7 这条记录,所以加锁的单位就是 next-key lock,所以加锁的范围就是(5,10],同时这是一个等值的查询 id=7, 而 id=10显然不满足条件,所以锁退化成间隙锁即(5,10)。所以最终的结果应该是事务 B 被挂起,事务 C 执行成功,让我们来验证一下。

Laravel

同时开启三个窗口,事务 B 插入被锁住了,而事务 C 可以执行。

刚才是在唯一索引上进行的操作,接下来是非唯一索引等值锁。

Laravel

我们先来复现一下结果。千万别惊呆😮

Laravel

什么情况❓你可以看到 事务 B 竟然没有被锁住,反而是事务 C 被锁住了。首先加锁会给(0,5]加上 next-key lock,接着因为 c 只是普通索引,所以在查询 c=5 的时候并不会立即停下。而是会继续向右查询,此时查询到下一条 c=10 (不等于5),根据规则,访问到的都必须加锁,所以给(5,10]加上 next-key lock。但是同时是等值判断最后一个 c=10 明显不等于5,所以又退化为间隙锁(5,10)。之前说的只有访问到的对象会加上锁。你再看事务 A ,这个查询明显会使用到覆盖索引,并不需要去访问主键索引,换句话说,这时候主键索引没有被访问,那就不会给他加锁。所以事务 B 并没有被锁住,事务 C 被事务 A 间隙锁(5,10)锁住了。

上面的例子不同点在于 lock in share mode,这个语句只会锁住覆盖索引,如果你想让他锁住事务 B 很简单 改为 for update ,系统会默认你接下来要修改数据,会顺便给主键索引上满足条件的记录加上行锁。我们来看一下。

Laravel

现在就可以看到两个事务都被事务 A 锁住了。

再来看一个主键索引范围锁。

Laravel

我们来分析一下,开始的时候要找到第一行 id=10,本来应该是 next-key lock(5,10],因为是主键上的等值查询,所以退化成行锁,所以只锁住 id=10 这一行。接下来范围查找的时候,找到 id=15 这一行即停下,所以此时需要加上间隙锁(10,15],所以综合下来事务 A 锁的范围是主键索引上,行锁 id=10 以及间隙锁(10,15],这时候你再看下面这张图,你就知道为什么了。

Laravel

上面的几个例子展示了一些间隙锁的加锁规则,当前实际的场景还有很多很多,比如如果带上 limit 的时候又是咋么加锁的呢,这些都是可以自己动手做实践的,实践才能检验真理。

其实在专栏里,作者已经总结了间隙锁的加锁原则,我这里直接照搬过来,间隙锁规则包含了两个“原则”、两个“优化”和一个“bug”。

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

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

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

优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

吴亲库里

本帖由系统于 5小时前 自动加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 1
Summer

我之前喜欢说一句话,生时何必久睡,死后自然长眠。现在我觉得早睡早起身体好。

不能同意更多哈,睡觉是最重要的事情

2天前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!