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);
什么是幻读
在可重复读的隔离级别下,我们如果只对id=5(也就是d=5由于d上没有索引,所以会走主键索引树)加行锁,我们可以分析以下SessionA会出现什么情况:
注意:上述Session A中查询都是当前读,关于当前读可以见MySQL MVCC(多版本控制)这篇文章。
Q3读到id=1这一行的现象成为幻读。幻读是指一个事务在前后两次查询同一个范围的数据的时候,后一次查询查到了第一次查询没有查到的行。
在可重复隔离级别下,普通的查询时快照读,是无法看到别的事务插入的数据的,只有当前读才会出现幻读。
幻读有什么问题?
Session A在T1时刻就声明我要把所有d=5的行锁住,不允许别的事务进行读写操作,但是实际上别的事务可以破坏这个声明。
数据似乎很正常没有什么问题,但是如果你去分析binlog就会发现有问题了:
上述形成的binlog如下:
-- binlog的模式是statement;
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/*所有d=5的行,d改成100*/
这个binlog如果被拿到备库执行或者用来克隆一个数据库,这三行的结果会变成(0,5,100)、(1,5,100)和(5,5,100),此时id=0和id=1这两行就出现了数据不一致。
如何解决幻读?
产生幻读的原因就是行锁只能锁住行,插入动作更新的是记录之间的间隙。因此为了解决幻读问题,InnoDB引入了间隙锁。
什么是间隙锁?
在文章开始的时候我们插入了6条数据,这就会产生7个间隙,如下:
当我们在执行select * from t where d=5 for update的时候,除了给数据库已有的行加行锁以外,还会对7个间隙加锁,这样确保了没有拿到锁的事务无法插入新的记录。
间隙锁之间没有冲突,跟间隙锁冲突的是往这个间隙中插入一个记录的操作。
什么是next-key lock?
间隙锁和和行锁合称next-key lock,每个next-key lock都是前开后闭区间。
select * from t where d=5 for update在执行的时候将形成7个next-key lock:
其中+∞在这里是InnoDB给每个索引加了一个不存在的最大值。
间隙锁导致死锁?
Session B的insert会被阻塞,Session A在执行insert的时候会检测到死锁,如下图:
此时两个Session形成死锁等待,InnoDB的死锁检测发现死锁关系,让Session A的insert 语句报错返回。
间隙锁是在可重复读隔离级别下才会生效,如果将隔离级别设置为读提交,就不会有间隙锁了,但是同时需要解决数据和日志不一致的问题(需要把binlog格式设置为ROW)。