MySQL中的Repeatable-Read探讨

在客户端A中，做以下操作：

在客户端B中，修改id=1的记录，然后提交：

再回到客户端A中，发现id=1的记录并没有改变：

紧接着，在A中执行UPDATE语句，发现是从400的基础上进行修改的，数据一致性没有被破坏。可重复读的隔离级别下使用了MVCC机制，SELECT操作不会更新版本号，会读历史版本属于快照读（历史版本）；INSERT、UPDATE和DELETE会更新版本号，会读最新版本属于当前读（当前版本）。

在B中INSERT数据：（先COMMIT事务A，再重新开启一个事务A）

先看A：

操作B：

再看A，发现并没有幻读问题：

虽然此时A中没有ID为5的记录，但是我们尝试直接更新如下：（出现了当前读的幻读问题）

Note：
1.事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；
  如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
  从上面看，我们在B中INSERT的时候，明明成功了...别慌...
  
2.MySQL事务隔离级别为serializable时会锁表，读写数据都会锁住整张表，因此不会出现幻读的情况。

间隙锁

生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。
这里的小红，小明，小花，小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。

间隙锁锁定的区域：
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

InnoDB自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有索引（没有索引的话，MySQL会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

接下来，通过实际操作观察下间隙锁的作用范围…

Note：表中id为主键，num为普通索引。

--session 1: 此时会锁住num=2到num=5之间的行
start  transaction ;
select  * from news where number=4 for update ;

--session 2:
start  transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（执行成功）
insert into news value(9,5);#（执行成功）
insert into news value(11,5);#（执行成功）

--session 1:
--检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，
--因此，id=13,num=11之后的所有行都会被锁住。
start transaction ;
select * from news where number=13 for update ;

--session 2:
start  transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功) 区分这两条记录
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞) 这是间隙锁的功能2
update news set id=11 where number=11;#(执行成功)

--session 1:会锁住num=4到num=11的所有行
start transaction ;
select * from news where number=5 for update;

--session 2:
start  transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)

--注意这个记录，value（9,12）会被插到id=13行的后面！这里指的是二次索引！
insert into news value(9,12);#(执行成功) 
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#（执行成功）
update news set id=4 where number=4 ;#（阻塞）

--session 1:
start transaction;
select * from news where number>4 for update;

--session 2:
start  transaction;
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(执行成功)
insert into news value(null,13);#(阻塞)