1.表结构
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;
Tips:下面介绍的操作内容都是 可重复读 隔离级别。
2.插入实验数据
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
如下图所示:
3.什么是幻读?
SESSION A | SESSION B | SESSION C | |
时刻1 | begin; select * from t where d=5 for update; |
||
时刻2 | update t set d=5 where id=0; | ||
时刻3 | select * from t where d=5 for update; | ||
时刻4 | insert into t values(1,1,5); | ||
时刻5 | select * from t where d=5 for update; commit; |
Tips:其中 for update 表示在查询的时候加上一个写锁,并且是当前读,两阶段锁协议 规则是在 InnoDB 事务中,行锁是在需要的时候才加上的,要等到事务结束时才释放。
- 幻读:按照两阶段锁协议的逻辑,在上述例子中,在 时刻1 的时候,SESSION A 查出来的满足条件的结果为 (5,5,5), 若此时 SESSION A 中事务只对 d=5 的行加上了行锁,那么在 时刻2 时刻 SESSION B 对 id=0 的行的修改,会使得 SESSION A 在 时刻3 查出来的满足条件的结果为 (0,0,5)、(5,5,5),然后在 时刻4 的时候,新增的行是没有行锁的,所以 SESSION A 在 时刻5 查出来满足条件的结果为 (0,0,5)、(1,1,5)、(5,5,5),像 SESSION A 这样的在同一个事务中前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行被称为幻读
4.幻读有什么问题?
SESSION A | SESSION B | SESSION C | |
时刻1 | begin; select * from t where d=5 for update; update t set d=100 where d=5; |
||
时刻2 | update t set d=5 where id=0; update t set c=5 where id=0; |
||
时刻3 | select * from t where d=5 for update; | ||
时刻4 | insert into t values(1,1,5); update t set c=5 where id=1; |
||
时刻5 | select * from t where d=5 for update; commit; |
假设 SESSION A 中的事务只在 id=5 这一行加行锁,在时刻1 的时候,SESSION A 中的 update 修改语句是在 commit 之后才提交的,在 时刻5 之后提交之后数据是 (5,5,100),在时刻2 的时候,SESSION B 中的 update 修改语句会把 id=0 这行数据修改为 (0,5,5),在 时刻4 的时候,表中会增加一条数据 (1,5,5),综合上面事务提交情况,binlog 记录的内容逻辑如下:
#时刻2 SESSION B update t set d=5 where id=0; /*(0,0,5)*/ update t set c=5 where id=0; /*(0,5,5)*/ #时刻4 SESSION C insert into t values(1,1,5); /*(1,1,5)*/ #时刻5 SESSION A commit 后 update t set c=5 where id=1; /*(1,5,5)*/ update t set d=100 where d=5;/*所有d=5的行,d改成100*/
上面的 binlog 日志逻辑,如果是从库用来复制,或者备课用来恢复,得到的数据逻辑就会和原主库主库不一致,如果只在加行锁的情况下,即使把所有的记录都加上锁,还是阻止不了新插入的记录。
5.间隙锁(Gap Lock)是如何解决幻读问题的
按照上述研究的内容,行锁只能锁住已有的行,但阻止不了新增的行数据,要更新的是记录之间的间隙,为了解决幻读问题,InnoDB 引入了间隙锁 (Gap Lock),而间隙锁和行锁合称为 next-key lock,每个 next-key lock 是前开后闭区间,表 t 初始化以后,如果用 select * from t for update 要把整个表所有记录锁起来,就形成了 7 个 next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum],其中 InnoDB 给每个索引加了一个不存在的最大值 supremum,具体现象如下:
- 表初始化数据如下:
- SESSION A 执行和现象如下:
- SESSION B 执行和现象如下:
- SESSION C 执行和现象如下:
Tips:从上述现象可以看出,InnoDB 通过行锁和间隙锁解决幻读问题。
6.next-key lock在什么情况下会造成死锁
SESSION A | SESSION B | |
时刻1 | begin; select * from t where id=9 for update; |
|
时刻2 | begin; select * from t where id=9 for update; |
|
时刻3 | insert into t values(9,9,9); | |
时刻4 | insert into t values(9,9,9); |
上述情况,在时刻1的时候,由于在 SESSION A 中 id=9 这一行不存在,会加入 (5,10) 的间隙锁,此时事务还没提交,在时刻2的时候,在 SESSION B 中 id=9 这一行不存在,也会加入 (5,10) 的间隙锁,并且间隙锁之间不互斥,此时 SESSION B 插入的新行数据会被 SESSION A 的间隙锁阻塞,在 时刻4 的时候,SESSION A 插入的新行数据会被 SESSION B 的间隙锁阻塞,因此两个 SESSION 进入了互相等待对方锁释放,就造成了死锁,具体现象如下:
- SESSION A 执行和现象如下:
- SESSION B 执行和现象如下:
- SESSION B 执行和现象如下:
- SESSION A 执行和现象如下:
Tips:间隙锁是在可重复读隔离级别下才会生效的,如果把隔离级别设置为读提交的话,就没有间隙锁了。