间隙锁(Gap Lock)"/>
幻读 和 间隙锁(Gap 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);
注:InnoDB 的默认事务隔离级别是可重复读,所以本文接下来没有特殊说明的部分,都是设定在可重复读隔离级别下。
Q3 读到 id=1 这一行的现象,被称为“幻读”。也就是说,幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。而且:
- 幻读在“当前读”下才会出现。
- 幻读仅专指“新插入的行”。
因为这三个查询都是加了 for update,都是当前读。而当前读的规则,就是要能读到所有已经提交的记录的最新值。并且,session B 和 sessionC 的两条语句,执行后就会提交,所以 Q2 和 Q3 就是应该看到这两个事务的操作效果,而且也看到了,这跟事务的可见性规则并不矛盾。
幻读有什么问题
1. 语义的问题:
session A 在 T1 时刻就声明了,“我要把所有 d=5 的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。
session B 和 session C 的第二条语句都破坏了session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。
2. 数据一致性的问题:
update 的加锁语义和 select …for update 是一致的,所以这时候加上这条 update 语句也很合理。session A 声明说“要给 d=5 的语句加上锁”,就是为了要更新数据,新加的这条 update 语句就是把它认为加上了锁的这一行的 d 值修改成了 100。
我们看看数据库里会是什么结果:
- 经过 T1 时刻,id=5 这一行变成 (5,5,100),当然这个结果最终是在 T6 时刻正式提交的 ;
- 经过 T2 时刻,id=0 这一行变成 (0,5,5);
- 经过 T4 时刻,表里面多了一行 (1,5,5);
- 其他行跟这个执行序列无关,保持不变。
这样看,这些数据也没啥问题,但是我们再来看看这时候 binlog 里面的内容:
- T2 时刻,session B 事务提交,写入了两条语句;
- T4 时刻,session C 事务提交,写入了两条语句;
- T6 时刻,session A 事务提交,写入了 update t set d=100 where d=5 这条语句。
这个语句序列,不论是拿到备库去执行,还是以后用 binlog 来克隆一个库,这三行的结果,都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。也就是说,id=0 和 id=1 这两行,发生了数据不一致。这个问题很严重,是不行的。
如何解决幻读:
这里,现在你知道了,产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。
间隙锁
什么是间隙锁
顾名思义,间隙锁,锁的就是两个值之间的空隙。
当你执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。
我们都知道。行锁,分成读锁和写锁,读写排斥、写写排斥,而且是锁与锁之间的冲突。
但是间隙锁不一样,跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
session B 并不会被堵住,因为表 t 里并没有 c=7 这个记录。 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标,即:保护这个间隙,不允许插入值。但,它们之间是不冲突的。
next-key lock
间隙锁和行锁合称 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]。
间隙锁和 next-key lock 的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”。
业务逻辑这样的:任意锁住一行,如果这一行不存在的话就插入,如果存在这一行就更新它的数据。这个逻辑一旦有并发,就会碰到死锁。
- session A 执行 select … for update 语句,由于 id=9 这一行并不存在,因此会加上间隙锁 (5,10);
- session B 执行 select … for update 语句,同样会加上间隙锁 (5,10),间隙锁之间不会冲突,因此这个语句可以执行成功;
- session B 试图插入一行 (9,9,9),被 session A 的间隙锁挡住了,只好进入等待;
- session A 试图插入一行 (9,9,9),被 session B 的间隙锁挡住了。
当然,InnoDB 的死锁检测马上就发现了这对死锁关系,让 session A 的 insert 语句报错返回了。间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的。其实,这还只是一个简单的例子
注:文章一开始,我们就强调了,可重复读级别前提下,因为间隙锁是在可重复读隔离级别下才会生效的。
你如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row。如果读提交隔离级别够用,也就是说,业务不需要可重复读的保证,这样考虑到读提交下操作数据的锁范围更小(没有间隙锁),这个选择是合理的。
加锁规则
前提说明:MySQL 后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13。
这个加锁规则里面,包含了两个“原则”、两个“优化”和一个“bug”:
- 原则 1:加锁的基本单位是 next-key lock,next-key lock 是前开后闭区间。
- 原则 2:查找过程中访问到的对象才会加锁。
- 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
案例分析
以下案例都是上面说的表和数据为例。
1.
由于表 t 中没有 id=7 的记录,所以用我们上面提到的加锁规则判断一下的话:
- 根据原则 1,加锁单位是 next-key lock,session A 加锁范围就是 (5,10];
- 同时根据优化 2,这是一个等值查询 (id=7),而 id=10 不满足查询条件,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。
2.
session A 要给索引 c 上 c=5 的这一行加上读锁:
- 根据原则 1,加锁单位是 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 语句可以执行完成。
需要注意,在这个例子中,lock in share mode 只锁覆盖索引,但是如果是 for update 就不一样了。 执行 for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。另外这个例子说明,锁是加在索引上的;同时,它给我们的指导是,如果你要用 lock in share mode 来给行加读锁避免数据被更新的话,就必须得绕过覆盖索引的优化,在查询字段中加入索引中不存在的字段。
3.
左图是唯一索引范围查询:
- 开始执行的时候,要找到第一个 id=10 的行,因此本该是 next-key lock(5,10]。 根据优化 1, 主键 id 上的等值条件,退化成行锁,只加了 id=10 这一行的行锁。
- 范围查找就往后继续找,找到 id=15 这一行停下来,因此需要加 next-key lock(10,15]。
右图是非唯一索引范围查询:
- session A索引 c 上加了 (5,10]这个 next-key lock 后,由于索引 c 是非唯一索引,没有优化规则,也就是说不会蜕变为行锁,因此最终 sesion A 加的锁是,索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。
4.
session A 是一个范围查询,按照原则 1 的话,应该是索引 id 上只加 (10,15]这个 next-key lock,并且因为 id 是唯一键,所以循环判断到 id=15 这一行就应该停止了。但是实现上,InnoDB 会往前扫描到第一个不满足条件的行为止,也就是 id=20。而且由于这是个范围扫描,因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。
5. 在上面的例子,给表 t 插入一条新记录: insert into t values(30,10,30);
现在表里有两个 c=10 的行,但是它们的主键值 id 是不同的(分别是 10 和 30),因此这两个 c=10 的记录之间,也是有间隙的。这次我们用 delete 语句来验证。注意,delete 语句加锁的逻辑,其实跟 select ... for update 是类似的,也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。
- session A 在遍历的时候,先访问第一个 c=10 的记录。同样地,根据原则 1,这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
- 然后,session A 向右查找,直到碰到 (c=15,id=15) 这一行,循环才结束。根据优化 2,这是一个等值查询,向右查找到了不满足条件的行,所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。
这个蓝色区域左右两边都是虚线,表示开区间,即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。
6. 接着上边案例5
session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条,因此加不加 limit 2,删除的效果都是一样的,但是加锁的效果却不同。可以看到,session B 的 insert 语句执行通过了。因此在遍历到 (c=10, id=30) 这一行之后,满足条件的语句已经有两条,循环就结束了。
因此,索引 c 上的加锁范围就变成了从(c=5,id=5) 到(c=10,id=30) 这个前开后闭区间,如下图所示:
7. 死锁的例子
- 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 回滚。
8.
- 由于是 order by c desc,第一个要定位的是索引 c 上“最右边的”c=20 的行,所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。
- 在索引 c 上向左遍历,要扫描到 c=10 才停下来,所以 next-key lock 会加到 (5,10],这正是阻塞 session B 的 insert 语句的原因。
- 在扫描过程中,c=20、c=15、c=10 这三行都存在值,由于是 select *,所以会在主键 id 上加三个行锁。
因此,session A 的 select 语句锁的范围就是:索引 c 上 (5, 25),主键索引上 id=15、20 两个行锁。
内容来源:林晓斌《MySQL实战45讲》
更多推荐
幻读 和 间隙锁(Gap Lock)
发布评论