幻读

样例

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

begin;
select * from t where d=5 for update;
commit;

这个语句会命中d=5这一行，对应的主键id=5，在select语句执行完成后，id=5这一行会加一个写锁，由于两阶段锁协议，这个写锁会在执行commit语句的时候释放。（由于字段d上没有索引，这条查询语句会做全表扫描。其他被扫描到的，但是不满足条件的记录也会加锁）

理解幻读

假设：

	sessionA	sessionB	sessionC
T1	begin; select * from t where d=5 for update;– Q1 (5,5,5)
T2		update t set d=5 where id=0;
T3	select * from t where d=5 for update;– Q2 (0,0,5),(5,5,5)
T4			insert into t values(1,1,5);
T5	select * from t where d=5 for update;– Q3 (0,0,5),(1,1,5),(5,5,5)
T6	commit;

sessionA执行了三次查询：Q1、Q2、Q3。它们的SQL语句相同。select * from t where d=5 for update;查所有d=5的行，而且使用的是当前读，能读到所有已经提交的记录的最新值，并且加上写锁。

Q3读到id=1这一行的现象被称为“幻读”。
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

• 在可重复读隔离级别下，普通的查询时快照读，是不会看到别的事务插入的数据的。幻读在“当前读”下才会出现。
• sessionB的修改结果，被sessionA之后的select语句用“当前读”看到，不能成为幻读。幻读仅专指“新插入的行”。

幻读引发的问题

语义上的问题

sessionA在T1时刻声明，把所有d=5的行锁住，不准别的事务进行读写操作，只给id=5这一行加了行锁。sessionB和sessionC中的语句正常修改，会产生新的d=5的行，破坏了sessionA中Q1语句要锁住所有d=5的行的加锁声明。

数据一致性问题

锁的设计是为了保证数据的一致性。不只是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

sessionA在T1时刻加一个更新语句：update t set d=100 where d=5;
数据库中结果：

• T1时刻后，id=5这一行变成(5,5,100)，这个最终结果是在T6时刻正式提交的；
• T2时刻后，id=0这一行变成(0,0,5);
• T4时刻后，表里多了一行(1,1,5);

binlog中内容：

• T2时刻，sessionB事务提交，写入了一条语句；
• T4时刻，sessionC事务提交，写入了一条语句；
• T6时刻，sessionA事务提交，写入了update t set d=100 where d=5这条语句。（所有d=5的行，d改成100）

使用binlog的语句序列，拿到备库执行或者克隆一个库，这三行结构都变成了(0,0,100),(1,1,100),(5,5,100)。产生了数据不一致。

数据不一致是因为select * from t where d=5 for update只给d=5这一行，也就是id=5这一行加锁导致的。

即使把扫描过程中碰到的行，都加上写锁(当前存在的所有行)，也只能解决update语句导致的数据不一致问题(sessionB被锁住，等到sessionA提交后才能继续执行)。但是阻止不了insert语句新插入记录，加锁的时候新插入的记录行不存在，不存在也就加不上锁(无锁可随意修改并先提交，若sessionC中加一条把d改成5的操作并先提交，根据日志语句序列依然会导致主从数据不一致)。

解决幻读

间隙锁

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

执行select * from t where d=5 for update;时，不止是给数据中已有的6个记录加上了行锁，还同时加了7个间隙锁。确保了无法再插入新的记录。

行锁分成读锁和写锁。读读兼容，读写、写写冲突。跟行锁有冲突关系的是“另外一个行锁”。

间隙锁之间都不存在冲突关系。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

sessionA	sessionB
begin;select * from t where c=7 lock in share mode;
	begin;select * from t where c=7 for update;

sessionB并不会被堵住，表t中没有c=7这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们有共同的目标：保护这个间隙，不允许插入值，但是它们之间是不冲突的。

next-key lock

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。示例表可以形成7个next-key lock：(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20,25]、(25,+suprenum]。

间隙锁为开区间，next-key lock为前开后闭区间。
+∞是开区间。InnoDB给每个索引加了一个不存在的最大值supremum，符合前开后闭区间。

新的问题

间隙锁和next-key lock的引入，解决了幻读的问题，但可能会导致同样的语句锁住更大的范围，影响了并发度。

sessionA	sessionB
begin;select * from t where id=9 for update;
	begin;select * from t where id=9 for update;
	insert into t values(9,9,9);– blocked
insert into t values(9,9,9);– error 1213(40001):deadlock found

1. sessionA执行select…for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)；
2. sessionB执行select…for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，这个语句可以执行成功；
3. sessionB视图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待；
4. sessionA视图插入一行(9,9,9)，被sessionA的间隙锁挡住了。

两个session进入互相等待状态，形成死锁。(InnoDB的死锁检测马上就发现了这对死锁关系，让sessionA的insert语句报错返回)

只有一个唯一索引的时候，可以用insert…on duplicate key update语法避免死锁

间隙锁是可重复读隔离级别下才会生效的。如果把隔离级别设置为读提交，就没有间隙锁了。但要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。(set transaction_isolation=’read-committed’;set binlog_format=’row’)

innodb_locks_unsafe_for_binlog=1;表示不加间隙锁