mysql 间隙锁 幻读

间隙锁 幻读"/>

mysql 间隙锁 幻读

1.什么是幻读

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

现在我们举例说明一下:

sessionA

image.png

Session B

image.png

在 RR 隔离级别下，step1、step2 是会正常执行的，step3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作。T1 不敢相信的又执行了 step4，发现和 setp1 读取的结果是一样的(RR下的 MMVC机制)。此时，幻读无疑已经发生，T1 无论读取多少次，都查不到 id = 1 的记录，但它的确无法插入这条他通过读取来认定不存在的记录(此数据已被T2插入)，对于 T1 来说，它幻读了。

2.如何解决这个问题呢？

next-lock key / gap 锁(范围行锁)，即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里先介绍下 加锁的这个原则

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，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 DEFAULT CHARSET=utf8;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

image.png

案例一：等值查询间隙锁

Session A

Begin

update t set d=d+1 where id=7;

image.png

Session B

insert into t values(9,9,9);

image.png

Session C

update t set d=d+1 where id=10;

image.png

我们发现 Sessoion B执行会等待 ，其实 你可以试下

执行

insert into t values(8,8,8);也是不行的

image.png

由于表里面没有id=7的行，优先加锁单元 next-key lock,事务一 加锁范围是 (5,10]；

又由于id=10 不满足id=7的条件 于是退化成间隙锁 (5,10)

那就是为啥Session C可以改变id=10的那一行的原因了

案例二：非唯一索引等值

Session A

Begin

select * from t where c=5 lock in share mode;

image.png

Session B

update t set d=d+1 where id=5;

image.png

Session C

insert into t values(7,7,7);

image.png

image.png

如图所示 加锁next-key lock 会给(0,5] 加上next-key lock锁

因为c是普通索引，因此访问c=5会继续向右边匹配 直到c=10放弃为止 由于访问到的都要加锁，

因此要给(5,10] 等值判断，最后一个不满足c=5的条件 退化为间隙锁(5,10)

所以执行 update t set d=d+1 where c=10; 可成功

image.png

案例三：主键索引范围锁

Session A:

select * from t where id>=10 and id<11 for update;

Session B:

Insert into t values(8,8,8);

Insert into t values(13,13,13);

image.png

Session C:

Update t set d=d+1 where id=15;

Session A 执行之后 ，首先next-key lock锁 5，10],

主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁这一点就是非唯一索引的区别

由于id <11 所以 一直到id=15那一行才结束 (10,15] 范围查询并不能退化成间隙锁 所以id=15还是锁着的。

案例四：非唯一索引范围锁

Sessoion A:

select * from t where c>=10 and c<11 for update;

image.png

Session B:

image.png

Session C:

image.png

这次 session A 用字段 c 来判断

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

案例五：唯一索引范围锁 bug

Session A:

select * from t where id>10 and id<=115 for update;

image.png

Session B:

update t set d=d+1 where id=20;

image.png

Session C:

insert into t values(16,16,16);

image.png

发现 A范围查询 首先 加上(10,15]next-key lock ，由于id=15 判断id=15这一行就停止了

但是 inndb 往前扫描第一个不满足条件的行为止，也就是id=20 这个是访问扫描，因此索引id (15，20]

也被锁上了 ，可以总结的是必须找到不满足全部条件的行为止

案例六：limit 语句加锁

Session A

Delete from t where c=10 limit 2;

image.png

SessonB

Insert into t values(12,12,12);

image.png

第二种情况

Session A

Delete from t where c=10 limit 2;

image.png

Session B

Insert into t values(12,12,12);

image.png

session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。 因此，索引 c 上的加锁范围就变成了从(c=5,id=5) 到(c=10,id=30) 这个前开后闭区间。