幻读，间隙锁，next-lock key加锁原则

（该文为极客时间《Mysql实战45讲》的20、21讲学习笔记）

环境准备sql

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 的默认事务隔离级别是可重复读，幻读是在可重复读的事务隔离级别才会出现。

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

举例：
场景1：Session A查不到id=1的记录，但是也插不进去id=1的记录

Session A	Session B
Select * from t where id = 1; (查无记录)
	insert into t values (1,1,1);
insert into t values (1,1,1); （主键冲突）
Select * from t where id = 1; (查无记录)

场景2：该场景为假设场景

Session A	Session B
begin; Select * from t where id = 1 for update;/*Q1 （查无记录） update t set d=2 where id = 1;/*Q2 （无更新记录）
	insert into t values (1,1,1);/*Q3 （得到(1,1,1)） update t set d=0 where id = 1;/*Q4 （得到(1,1,0)）
Select * from t where id = 1 for update;/*Q5 （查出(1,1,0)） commit;

注意：
1、在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
2、 select 语句用“当前读”（select … for update）看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读的问题：
1、破坏了语义。场景2的Q1的本义是锁住id=1的记录，但是Q3却可以修改id=1的记录，原因是无法锁住新插入的记录。
2、破坏了数据一致性。锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。场景2的binlog在statement模式下，是这样的：

insert into t values (1,1,1);
update t set d=0 where id = 1;
update t set d=2 where id = 1;

如果用binlog恢复数据将会得到(1,1,2)的记录，而不是原来的(1,1,0)。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。间隙锁，锁的就是两个值之间的空隙，防止新数据插入。

显然，间隙锁会影响并发度。如可重复读的以下场景，会造成死锁。

Session A	Session B
begin; select * from t where id = 9 for update;
	select * from t where id = 9 for update; insert into t values (9,9,9); (block)
insert into t values (9,9,9); (Deadlock found)

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这是现在不少公司使用的配置组合。

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

案例一：等值查询间隙锁

Session A	Session B	Session C
begin; update t set d=d+1 where id=7;
	insert into t values (8,8,8); (block)
		update t set d=d+1 where id=10; (query ok)

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

案例二：非唯一索引等值锁

Session A	Session B	Session C
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); (block)

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

案例三：主键索引范围锁

Session A	Session B	Session C
select id from t where id>=10 and id<11 for update;
	insert into t values (8,8,8); (query ok) insert into t values (13,13,13); (block)
		update t set d=d+1 where id=15; (block)

1、开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
2、范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。
3、注意，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

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

Session A	Session B	Session C
select id from t where c>=10 and c<11 for update;
	insert into t values (8,8,8); (block)
		update t set d=d+1 where c=15; (block)

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

例五：唯一索引范围锁 bug

Session A	Session B	Session C
select id from t where c>10 and c<=15 for update;
	update t set d=d+1 where id=20; (block)
		insert into t values (16,16,16); (block)

session A 是一个范围查询，按照原则 1 应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

案例六：非唯一索引上存在"等值"的例子

Session A	Session B	Session C
begin; delete from t where c=10;
	insert into t values (12,12,12); (block)
		update t set d=d+1 where c=15; (Query OK)

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) 的间隙锁。

案例七：limit 语句加锁

Session A	Session B
begin; delete from t where c=10 limit 2;
	insert into t values (12,12,12); (Query OK)

session A 的 delete 语句加了 limit 2。删除的效果和案例六一样的，但是加锁的效果却不同。这是因为案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，

结论：在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

Session A	Session B
select id from t where c=10 lock in share mode;
	update t set d=d+1 where c=10; (blocked)
insert into t values (8,8,8); (blocked)
	(deadlock found where trying to get lock)

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

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

（以上内容摘抄自极客时间《Mysql实战45讲》，仅用作为本人的学习笔记）