Scott's world.

MySQL-幻读与间隙锁问题笔记

Word count: 1.9kReading time: 7 min
2020/02/15 Share

MySQL-幻读与间隙锁问题笔记

我们回到上一篇文章中提到的问题,下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?

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

我们在上篇也提到过答案,那就是这个语句会命中d=5这一行,对应的主键id=5,因此在此条语句执行完成后会给id=这一行会加一个写锁,而且由于两阶段协议,这个写锁会在commit语句时释放。

由于字段d没有索引,因此这条查询语句会做全表扫描,那么其他被扫描到的行,但是不满足条件的5行记录上会不会被加锁?

我们知道,InnoDB的默认事务隔离级别是可重复读,所以本文接下来没有特殊说明的部分,都是设定在可重复读隔离级别下。

幻读是什么

现在,我们就来分析一下,如果只在id=5这一行加锁,而其他行的不加锁的话,会怎么样。比如下面这个场景。

上面session A中的语句使用的三当前读,并且加上写锁。

其中Q3读到id=1这一行的现象,被称为“幻读”。即幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。

“幻读”:

  1. 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。
  2. 上面session B的修改结果,被session A之后的select语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。

幻读出现的问题

语义问题

session A在T1时刻为d=5的行加锁,不准其他事务进行读写操作。实际上这个语义就被破坏了。

上面在T1时刻只为id=5这一行加了行锁,而在session B的第一条语句d=5,id=0这一行并没有加上锁,所以是可以执行其第二条更新语句。session C同样也是这一个情况。

数据一致性问题

我们知道,锁的设计是为了保证数据的一致性。而这个一致性,不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。

上图的情况可以看到,session A在T1时刻说明要给d=5的语句加上锁,而新加的这一条更新语句将此值修改为100。那么这时候我们来分析一下其执行结果:

  1. 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的;
  2. 经过T2时刻,id=0这一行变成(0,5,5);
  3. 经过T4时刻,表里面多了一行(1,5,5);
  4. 其他行跟这个执行序列无关,保持不变。

而binlog上面的内容为:

1
2
3
4
5
6
7
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

这里我们就可以看到会出现数据不一致的问题。所以我们来分析一下数据不一致到底是怎么引入的?

问题就出在我们分析时假设session A中第一条语句导致的即给d=5,id=5这一行加锁。所以我们需要改正一下即把扫描过程中碰到的行,也都加上写锁,再来看看执行效果。

在binlog的执行序列为:

1
2
3
4
5
6
7
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

虽然id=0这一行问题解决了,但是id=1这一行还是没有解决幻读的问题。

其实不难看出,即使把所有的记录都加上锁,还是阻止不了新插入的记录

如何解决幻读

为了解决幻读问题,InnoDB引入了新的锁即间隙锁(Gap 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, +suprenum]。

+∞是开区间。实现上,InnoDB给每个索引加了一个不存在的最大值suprenum,这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和next-key lock的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”。

案例

假如我们遇到一种业务情况,业务逻辑这样的:任意锁住一行,如果这一行不存在的话就插入,如果存在这一行就更新它的数据,代码如下:

1
2
3
4
5
6
7
8
9
begin;
select * from t where id=N for update;

/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;

commit;

可能你很容易想到insert ... on duplicate key update,但其实在有多个唯一键的时候,这个方法是不能满足此情况的。

我们可能会碰到的现象是,这个逻辑一旦有并发,就会碰到死锁即使在for update这种最严格的模式下。

模拟上述并发过程你就可以看出两个session进入互相等待状态,形成死锁。

间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的

不在可重复读隔离级别下

上面我们分析的情况都是在可重复隔离级别解决幻读问题,而间隙锁是在可重复读隔离级别下才会生效的。

但是当我们把隔离级别设置为读提交的时候就没有间隙所,通过设置binlog格式为row来解决数据和日志不一致问题,这样也可以解决幻读的问题。

问题

实际上,这里session B和session C的insert 语句都会进入锁等待状态。

你可以试着分析一下,出现这种情况的原因是什么?

参考

可重复读隔离级别下,经试验:
SELECT * FROM t where c>=15 and c<=20 for update; 会加如下锁:
next-key lock:(10, 15], (15, 20]
gap lock:(20, 25)

SELECT * FROM t where c>=15 and c<=20 order by c desc for update; 会加如下锁:
next-key lock:(5, 10], (10, 15], (15, 20]
gap lock:(20, 25)

session C 被锁住的原因就是根据索引 c 逆序排序后多出的 next-key lock:(5, 10]

CATALOG
  1. 1. MySQL-幻读与间隙锁问题笔记
    1. 1.1. 幻读是什么
    2. 1.2. 幻读出现的问题
      1. 1.2.1. 语义问题
      2. 1.2.2. 数据一致性问题
    3. 1.3. 如何解决幻读
      1. 1.3.1. 案例
    4. 1.4. 不在可重复读隔离级别下
    5. 1.5. 问题
      1. 1.5.1. 参考