20幻读和间隙锁

Posted on 2025-03-17 Edited on 2025-05-26

20幻读和间隙锁

幻读

定义：新插入的数据导致事务前后读取到的数据不一致
幻读解决数据和binlog日志一致性问题
可重复读隔离级别下使用当前读（for update、lock in share mode）会有幻读问题

特点：

可重复读级别下自动启动间隙锁
事务A begin;select * from t where c=7 lock in share mode,
事务B begin;select * from t where c=7 for update;
都会加间隙锁但不冲突，他们都没有插入数据。
锁定行间的间隙
前开后闭
引入更大范围锁定影响并发，增加锁分析的复杂度
使用已提交读+binlog_format=row模式
读已提交不会加间隙锁
row模式解决主从同步数据不一致问题
但会有幻读问题！！

next-key lock

间隙锁和行锁合称
举例：

SELECT * FROM users WHERE id = 5 FOR UPDATE;
假设表中存在 id = 4 和 id = 6，则临键锁会锁定 (4, 5] 和 (5, 6)。 

事务A
SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;
假设表中存在 id = 4、id = 5、id = 10 和 id = 11，则临键锁会锁定 (4, 5]、(5, 10] 和 (10, 11)

INSERT INTO users (id) VALUES (6); -- 阻塞，直到事务 A 提交或回滚
DELETE FROM users WHERE id = 5; -- 阻塞，直到事务 A 提交或回滚

间隙锁加锁规则

隔离级别：重复读

规则
两个“原则”、两个“优化”和一个“bug”。

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

1 CREATE TABLE `t` (
2 `id` int(11) NOT NULL,
3 `c` int(11) DEFAULT NULL,
4 `d` int(11) DEFAULT NULL,
5 PRIMARY KEY (`id`),
6 KEY `c` (`c`)
7 ) ENGINE=InnoDB;
8
9 insert into t values
(0,0,0),(5,5,5),
(10,10,10),(15,15,15),
(20,20,20),(25,25,25);

等值查询间隙锁

sessionA	sessionB	sessionC
begin;update t set d=d+1 where id=7;
	insert into t values(8.8.8);(blocked)
		update t set d=d+1 where id=10;(Query ok)

没有id=7的记录，
根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key
lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

非唯一索引等值锁

sessionA	sessionB	sessionC
begin;select id from t where c=5 lock in share mode;
	update t set d=d+1 where id=5;(Query ok)
		insert into t values(7,7,7),(blocked)

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

lock in share mode 只锁覆盖索引，如果要用lock in sharemode 来给行加读锁避免数据被更新的话，得绕过覆盖索引优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t wherec=5 lock in share mode。

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

主键索引范围锁

sessionA	sessionB	sessionC
begin;select * from t whereid>=10 and id<11 for update;
	insert into t values(8,8,8):(Query ok) insert into tvalues(13,13,13);(blocked)
		update t set d=d+1 whereid=15;(blocked)

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

非唯一索引范围锁

sessionA	sessionB	sessionC
begin;select * from t where c>=10and c<11 for update;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 wherec=15;(blocked)

在第一次用 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，才知道不需要继续往后找了。

唯一索引范围锁 bug

sessionA	sessionB	sessionC
select * from t where id>10and id<=15 for update;
	update t set d=d+1 whereid=20;(blocked)
		insert into tvalues(16,16,16);(blocked)

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

非唯一索引上存在”等值”的例子

limit 语句加锁

一个死锁的例子

sessionA	sessionB
begin;select id from t where c=10 lock in sharemode;
	update t set d=d+1 where c=10.(blocked)
insert into t values(8,8,8):
	ERROR 1213(40001):Deadlock found when trying toget lock; try restartingtransaction

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 的行锁，这时候才被锁住的。