mysql中的幻读与间隙锁

一、数据库隔离级别

一般来讲，数据库的隔离级别分为读未提交、读已提交(read commit,rc)、可重复读(read reapeat,rr)、串行化四个级别。在mysql中默认隔离级别是rr。读未提交存在脏读问题(A事务读到B事务未提交的数据)，读已提交存在重复读问题(A事务读取两次数据a，期间a被B事务修改后提交，两次数据不一致)，可重复读存在幻读问题(A事务读取两次a=1的数据，期间B事务插入了一天a=1的数据,导致两次读取结果不一致)。

mysql中通过mvcc解决了脏读和重复读的问题，其中rr是在事务开启时，创建read view。rc在每次查询时创建read view。实际上rc隔离级别上不存在幻读问题，所以可以使用rc+row格式的binlog组合避免幻读。

rr隔离离别下，正常的查询语句其实也不存在幻读问题。但是一些update/delete语句采用的是当前读，这会导致只有行锁的情况下，产生幻读，假设没有间隙锁，当前读中也会出现重复读的问题。mysql在rr隔离级别下解决幻读问题，采用的是行锁+间隙锁，两者合称next-key lock。

我们知道mysql的行锁实际上是加在索引上的，所以进入正题前，我还多聊几句索引与行锁。

二、行锁

在innodb引擎下才有行锁，

行锁是两阶段锁，在事务结束后才会释放。行锁有分为读锁和写锁，两者关系如下图:

                           

跟行锁冲突的是另一个行锁。

三、索引

索引可以根据存储的数据，分为主键索引与非主键索引。其中主键索引又称聚簇索引，它的叶子节点存整行的值，实际数据就存储在这个索引上，即表和索引存在一起。非主键索引又称非聚簇索引（二级索引），它的叶子节点存的是主键的数据。所以使用非覆盖的非聚簇索引会引起回表操作。

之前说过行锁实际加载索引上，所以如果一个update语句的where条件中，如果没有明确的索引时，会导致所有行均被加上行锁（所有间隙也会加上间隙锁）

四、幻读是如何产生的

接下来我们先来聊一下幻读是怎么产生的。我们可以先建表如下：

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);

 

对于t表如果如下操作：

	session A	session B	session C
t1	begin update t set d=100 where c=5
t2		update t set c=5 where id=0
t3			insert into t values(50,5,50)
t4	commit

在只有行锁的情况下。如果在t1,t2与t3之间，t4时刻分别用select * from t where c=5 for update进行当前读，会发现当前读中出现重复读与幻读的问题。但这并不是最严重的的问题，同样的只考虑行锁的情况下，事务提交顺序是b->c->a。最后的实际数据是(5,5,100),(0,5,0),(100,5,100)

但是binlog中记录的sql如下:

update t set c=5 where id=0

insert into t values(50,5,50)

update t set d=100 where c=5

这样binlog执行后产生的数据是(5,5,100),(0,5,100),(50,5,100)。此处id为0和id为5的数据均和预期不一致。

为了解决上面的问题，innodb引入了间隙锁(gap lock)

五、间隙锁

索引上的记录之间，是有间隙的，上文中的表插入数据后，其间隙如下：

       

我们可以对上图中的间隙加锁，也就是间隙锁，但是与行锁不同，间隙锁质检不互斥，与间隙锁冲突的是“往这个间隙中插入一个记录”这个操作。

根据B+树索引的搜索规则，非唯一索引满足条件的数据与后一条数据，一定会被搜索到，唯一索引只会搜索到满足条件的数据，而所有在索引上查找到的数据，都会加锁。加锁时，数据所在行加行锁，所在行前一个间隙加间隙锁，两者组合成为next key lock。这里需要注意，比如上图中(0,5]next-key lock中，也会阻止其他事物插入值为5的数据。

对于唯一索引的等值查询，因为不可能插入其他等值的数据，所以next-key会退化为行锁。

非唯一索引等值查询，向两侧遍历到最新值后，由于最新值一定不等于当前值，所以next-key会退化为间隙锁。

下面我们一起看一下，innodb是如何用next-key lock解决幻读的。

六、间隙锁的运转

6.1间隙锁的基本使用

先回到我们之前看的问题，可以知道t1时刻会在索引c上有(0,5]和（5,10）的next-key锁。所以t2时刻的事务实际无法提交，需要等待t4时刻session A提交后释放next-key锁，才能进行事务。

但实际上由于session B和session C虽然没获取到行锁上的写锁，单两者都获取了(0,5]和（5,10)的间隙锁，所以t4时刻后，两者事务提交都会被阻塞，需要死锁检测释放其中一个持有的间隙锁后，才能依次执行。

之前索引中我们提到过非聚簇非覆盖索引会引起回表，实际这个回表操作会额外对主键索引(聚簇索引)加锁。如果通过lock in share mode先使用覆盖索引，再使用主键索引时，主键索引上会会不产生锁，所以可以修改数据，示例如下：

	session A	session B	session C
t1	begin select id from t where c=5 lock in share mode
t2		update t set d=100 where id=5
t3			insert into t values(7,7,7)

上面的例子中t2时刻session B的update语句是可以执行的，但是由于之前说过对普通锁因c，t1时刻会产生(0,5]和（5,10）的next-key锁，所以t3时刻的session C的insert语句是无法执行的。

如果t1时刻使用for update进行加锁，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。这会导致t2时刻session B的update语句是无法执行。

6.2间隙锁与二级索引

二级索引非唯一是，会按照主键进行排序，这会导致下面案例中的问题：

	session A	session B	session C
t1	begin update t set d=100 where c=10
t2		insert into t values(2,10,50)
t3			insert into t values(7,10,50)
t4	commit

上面案例中sessionb可以执行，但是session c不能执行。原因如下图：

       

6.3动态的间隙

	session A	session B	session C
t1	begin select * from t where c=11 for update
t2		delete from t where c=10
t3			insert into t values(10,10,10)
t4	commit

上面流程中session b可以执行，但是session c执行会失败。原因是session a中是对(10,15)这个间隙加锁，session b中删除了c=10的数据后，会导致（5,10）和（10,15）这两个间隙合并成了一个新的间隙（5，15），同时它继承了之前的加锁状态，所以ssssion c中的插入操作会失败