Mysql幻读

最新推荐文章于 2024-07-23 13:22:23 发布

风舞红叶

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量926

点赞数 11

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_46488959/article/details/138637318

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

幻读仅专指“新插入的行”

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

当前读的规则，就是要能读到所有已经提交的记录的最新值

产生幻读的原因

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，新插入记录行还不存在，不存在也就加不上行锁。

间隙锁 (Gap Lock)

行锁，分成读锁和写锁。写锁与写锁、写锁与读锁都是冲突的，也就是说，跟行锁有冲突关系的是“另外一个行锁”

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

next-key lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。
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);
如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

因为 +∞是开区间，InnoDB 给每个索引加了一个不存在的最大值 supremum

加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。
session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的

session A 要给索引 c 上 c=5 的这一行加上读锁
加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。
c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。访问到的都要加锁，因此要给 (5,10] 加 next-key lock。
等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁， session B 的 update 语句可以执行完成。但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

找到第一个 id=10 的行，因此本该是 next-key lock(5,10]，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。
for update 锁，c=10，c=15 对应主键 10,15 加行锁

索引 id 上加 (10,15] 这个 next-key lock，id 有小于这个范围，会往前扫描到第一个不满足条件的行为止，也就是 id=20。因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。

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

session A 在遍历的时候，先访问第一个 c=10 的记录。这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。
c 上加锁范围 (5,10]、(10,10]、(10,15)—> (5,15)

session A 的 delete 语句加了 limit 2，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。
索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

间隙锁是在可重复读隔离级别下才会生效的
间隙锁和 next-key lock 的引入，解决了幻读的问题，但间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响了并发度的。

加锁规则

next-key lock ,具体执行的时候，是要分成间隙锁和行锁两段来执行的。

可重复读隔离级别 (repeatable-read)

1、原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间
2、原则 2：查找过程中访问到的对象才会加锁。
3、优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。**
4、优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5、范围查询会访问到不满足条件的第一个值为止(唯一索引也一样)。