51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

手机号码,快捷登录

查看: 992|回复: 0

这些SQL 题大部分人答不出来,我保证!

[复制链接]
  • TA的每日心情
    无聊
    昨天 09:04
  • 签到天数: 1030 天

    连续签到: 1 天

    [LV.10]测试总司令

    发表于 2023-6-12 10:38:55 | 显示全部楼层 |阅读模式
    周末的时候,一个读者问了我一个很有意思的问题,是关于 MySQL 中 update 加锁的问题。
      他用下面这张数据库表,做了个 MySQL 实验的时候。

      发现事务 B 的 update 不会阻塞,而事务 C 的 update 会阻塞,都是对 id = 10 这条记录进行 update, 为什么一个会阻塞,一个不会阻塞?

      首先,我们先来分析下,事务 A 这条 SQL 加了什么锁。
      // 事务 A
      select * from t_person where id < 10 for update;


      我直接说结论,事务 A  加了这三个行级锁:
      在 id 为 1 的主键索引上,加了 X 型的 next-key 锁,范围是 (-∞,1]。意味着,其他事务无法对 id = 1 的记录进行删除和更新操作,同时无法插入 id 小于 1 的新记录。
      在 id 为 5 的主键索引上,加了 X 型的 next-key 锁,范围是 (1, 5]。意味着,其他事务无法对 id = 5 的记录进行删除和更新操作,同时无法插入 id 为 2、3、4 的新记录。
      在 id 为 10 的主键索引上,加了 X 型的间隙锁,范围是 (5, 10)。意味着,其他事务无法插入 id 为 6、7、8、9 的新纪录。
      PS:如果你不清楚什么是 MySQL 这些行级锁(记录锁、间隙锁、next-key 锁),以及不清楚行级锁的加锁规则。。
      事务 B 的 update 语句为什么不会阻塞?
      事务 B 的 update 语句是对 id = 10 的行记录的 name 字段进行更新。
      // 事务 B
      update t_person set name = "小林" where id = 10;


      事务 B 会在 id = 10 的主键索引上加 X 型记录锁,仅锁住这一行。因为当我们用唯一索引进行等值查询的时候,查询的记录是「存在」的,在索引树上定位到这一条记录后,该记录的索引中的 next-key 锁会退化成「记录锁」。
      事务 A 并没有对 id = 10 的主键索引上加 X 型记录锁,而是对 id = 10 的主键索引上加 X 型间隙锁。间隙锁和记录锁之间是没有互斥关系的,所以事务 B 的 update 语句不会阻塞。
      事务 C 的 update 语句为什么会阻塞?
      事务 C 的 update 语句是将 id = 10 的行记录的 id 更新为 2。
      // 事务 C
      update t_person set id = 2 where id = 10;


      这条 update 很特殊,特殊之处在于更新了主键索引。你以为它只是一个更新操作,实际上它在背后执行了两个操作:
      操作 1:delete from t_person where id = 10;
      操作 2:insert into t_person (2, 陈某,  30, 广州市海珠区);
      也就是先删除 id = 10 的记录,然后再插入 id = 2 的新纪录。
      为什么当 update 语句更新了索引值,会被拆分成删除和插入操作?
      要回答这个问题,我们先要清楚 B+ 树的特点。
      Innodb(MySQL 存储引擎)在实现索引的时候,采用的数据结构是 B+ 树。B+ 树是基于二分查找树演变过来的,所以 B+ 树在存储索引的时候,是按顺序存储的,因为这样才能利用二分查找快速检索到索引。
      现在有一颗这样的  B+ 树,可以看到叶子节点的索引值是从小到大的顺序。

      假设这时候需要将索引值为 25 更新为 3,如果直接索引值为 25 的位置上,将值改为 3 的话。

      这时候你就会发现这棵 B+ 树不满足顺序性了!
      所以更新索引的值,不能只是修改一个索引值就完事,而是还要保证更新后的索引值能继续满足  B+ 树的顺序性。
      解决的方法就是,先删除索引值为 25 的节点,再插入索引值为 3 的节点,这样,这颗 B+ 树才能满足顺序性。

      事务 C 的 update  语句具体阻塞在哪个「操作」?
      现在我们知道,事务 C 的 update 特殊语句背后执行了两个操作,分别是删除和插入操作,那具体是阻塞在哪个「操作 」?
      「操作 1 」是删除 id = 10 的记录,事务 C 是会在 id = 10 的主键索引上加 X 型记录锁,而事务 A 并没有对 id = 10 的主键索引上加 X 型记录锁,而是对 id = 10 的主键索引上加 X 型间隙锁。间隙锁和记录锁之间是没有互斥关系的,所以「操作 1 」不会阻塞。
      根据排除法,既然 「操作 1 」不会阻塞,那事务 C 的 update 语句阻塞的原因就是因为 「操作 2」发生了阻塞。
      为什么「操作2」会发生阻塞呢?
      我们先要知道,插入操作什么时候会发生阻塞:插入语句在插入一条新记录之前,需要先定位到该记录在 B+树的位置,如果插入的位置的下一条记录的索引上有间隙锁,此时会生成一个插入意向锁,然后锁的状态设置为等待状态,现象就是插入语句会被阻塞。
      「操作 2」插入的是 id = 2 的新记录,在主键索引的 B+树定位到插入的位置如下图。

      插入位置的下一条记录是 id = 5 的记录,而事务 A 在 id 为 5 的主键索引上已经加了 X 型的 next-key 锁,这里面包含了间隙锁。所以「操作 2」的插入操作会发生阻塞,这就是事务 C 的 update 语句阻塞的原因。
      从这我们也可以知道间隙锁的作用,就是阻止其他事务在间隙锁的范围内插入新记录,从而避免可重复读隔离级别下幻读的现象。
      我们也可以通过 select * from performance_schema.data_locks\G; 这条语句,查看事务 C 在加什么锁的时候导致阻塞。

      从上面的输出信息,可以看到事务 C 在加「插入意向锁」的时候,发生了阻塞。
      插入意向锁是插入操作才会有的锁,而事务 C 只是执行 update 语句,却出现了插入意向锁,从这里也可以证明,事务 C 这条特殊的 update 语句运行的时候,被拆分成了两个操作,一个是删除,另一个是插入。
      总之,如果 update 语句更新的是普通字段的值,就会对发生更新的记录加 X 型记录锁。
      但是,如果 update 语句更新的是索引的值,那么在运行的时候会被拆分成删除和插入操作,这时候分析锁的时候,要从这两个操作的角度去分析。
      完啦!
      怎么样,够不够细节?

    回复

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /2 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-10-12 01:20 , Processed in 0.088382 second(s), 24 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表