MySQL使用Next-Key Locking解决幻读问题。
幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果,第二次的SQL语句可能会返回之前不存在的行。
MySQL在可重复读的隔离级别下，使用Next-Key Locking锁定一个范围,从而解决幻读问题。
如果是非锁定读,MySQL通过LSN返回回滚段中的数据。与读提交隔离级别不同的是,Read Committed总是返回最新的版本,而REPEATABLE-READ返回查询开始时LSN那个版本的数据。
如果是锁定读,就需要采用Next-Key Locking
实验数据如下:
-
create table t
-
(
-
a int primary key,
-
b int
-
) engine=innodb;
-
-
create index inx_t_b on t(b);
-
-
insert into t select 1,10;
-
insert into t select 2,10;
-
insert into t select 3,20;
-
insert into t select 4,20;
-
insert into t select 5,30;
-
insert into t select 6,30;
-
- commit;
update t set b=-1 where b=10;
可供锁定的范围为
(-无穷,10]
(10,20]
(20,30]
(30,+无穷]
InnoDB会使用Next-Key Locking锁定(-无穷,10]这个范围和辅助索引两个b=10的节点和聚簇索引的1、2节点,
并且对辅助索引下一个键值加上gap lock，范围(10,20)
这样，保证了锁定读的可重复读。
此时，如果在另一终端输入语句:
update t set b= -10 where b=30;
还是会导致阻塞。
虽然b=30在锁定范围之外，但是set中的b=-10却在锁定范围之内,所以被阻塞。
如果这个语句没有被阻塞而顺利提交，则最开始的终端,再次查询会发现多出两条记录(5,-10)和(6,-10).出现了幻读。
同理,
若事务A:update t set b=21 where b=20;
锁定范围 (10,20],(20,21],(21,30)
锁定记录 两个b=20的辅助索引和a=3,a=4的聚簇索引。
Next-Key Lock 相当于 Record Lock+Gap Lock
当查询的索引含有唯一属性时,InnoDB会进行优化,将其降级为Record Lock。