串行化隔离级别怎么解决幻读问题? 先说下幻读的含义,幻读就是在事务中按照同样的条件前后两次查询的结果数据量不同。  解决串行化的幻读问题用间隙锁(gap lock),间隙锁是给不存在的记录加锁,要正确理解间隙,知道间隙的范围。条件无非就是两类:范围查询和等值查询。再说下范围查询和等值查询都是怎么加间隙锁的,分别从主键索引和辅助索引两个场景来说。
一、间隙锁的概念

我们把事务2 select的指定的条件分为2类:范围查询、等值查询
record lock(记录锁,就是行锁) gap lock(间隙锁) next-key lock:record lock 和 gap lock
二、测试间隙锁范围加锁
设置事务为手动提交,隔离级别设置成串行化

查看表结构,id、age和name都有索引

场景1:用不可重复的主键id测试间隙锁
做范围查询

事务2的select操作只给三行数据加了排它锁,为什么插入id=24的数据也不行?
这是因为在串行化隔离级别中,不仅仅是获取了满足条件的这3行的行锁,而且把表数据后边空洞的地方也上了间隙锁。

图中红色线的地方都上了间隙锁,上锁范围(左开右闭)为:( 11 , 12 ] ∪ ( 12 , 22 ] ∪ ( 22 , 23 ] ∪ ( 23 , + ∞ ]
12,22,23是三个行记录,因为过滤条件是用id带有索引的,所以select获取了12,22,23的共享行锁(record-lock), 还把间隙加了间隙锁,其实就是给间隙加上共享锁或者排他锁,将间隙锁和行锁统称next-key lock (record-lock和gap-lock),也就是说where id>11加了next-key lock。正是因为给空洞也加锁了,所以事务1再想获取间隙的排它锁是不可以的,因为共享锁和排它锁是不能共存的。
由于事务2是select,所以是给间隙加上了共享锁,如果事务1做select id>11还是可以的,不能update、insert、delete id>11的数据。
场景2:用可重复的age(有索引)测试间隙锁
测试辅助索引树上,间隙锁的范围
我们先查看表结构、表数据,然后回滚。

根据表的内容建简单的辅助索引 
开启事务进行测试

很明显,由于age>20的区间都被事务1加上了间隙锁(这里加的是共享锁),所以事务2插入age=22和age=21都失败了 
幻读就是同一事务两次用相同的条件查询数据,下一次查出的数据量和上一次的数据量不一样,就算事务1把age=20的数据插入表,事务2再用age>20查询,得到的数据量也不会改变。
那事务1插入age=20的数据能否成功呢?
 依然不能成功,这是因为我们插入的数据id是自增的,所以这条数据为(age=20,id=24),位于辅助索引树中(age=20,id=12)的右边,由于(age=20,id=12)右边都被上了锁,(age=20,id=24)自然无法插入。
辅助索引值相等的话。主键按升序排列。
 很显然,事务1插入的age=18和age=19都不在事务2上锁的范围,所以可以插入
场景3:实际情况需要具体分析用的到底是行锁还是表锁
 回滚,重新开启事务
 开始测试
 我们发现事务1无论是插入age>18范围内的数据,还是范围外的数据,都无法成功
这时我们就要分析了,这应该没有用到索引,因为我们用索引,过滤出的数据占了整张表的一大半,MySQL server没使用索引。
没有加行锁,只能加表锁(这时加的是共享锁),所以事务1无论插入什么数据都不行
 果然,没有用到索引

age>20用到了索引,所以可以用行锁
三、测试间隙锁等值加锁
查看表结构和表数据
 设置手动提交,设置串行化隔离级别,回滚然后启动事务

1. 测试不能重复的主键索引
此时事务2做select操作,由于是等值查询,所以给这条数据加了共享锁。
 事务2的主键或者唯一键进行等值查询的时候,事务1插入一个新的数据是可以成功的,因为主键id不能重复,我们不能再插入主键id=9的数据。
 在这种情况下,主键或者唯一键是不能重复的,事务2进行等值查询时,事务1插入一个新的数据,不用担心这条数据和查询条件是一样的,所以肯定能成功
2. 测试能重复的辅助索引
回滚并重启事务
 事务2等值查询,给age=18这行数据加上了共享锁(record-lock)
 这是一个等值查询,而且用的是辅助索引age,那么在辅助索引age的辅助索引树上叶子节点存的是age的辅助索引值和它所在行的主键值,  事务1插入age=18是不被允许的,否则事务2再查询age=18就有两条记录了。
 奇怪的是,我们插入age=17,16,15也被阻塞住了
 这是因为,为了防止幻读,除了age=18这条数据加了共享锁,其两侧也被加了间隙锁。
如果插入(age=15,id=1)就可以成功,根据辅助索引值相同,按照主键值升序排列,(age=15,id=1)应该放在(age=15,id=7)前面,不在间隙锁范围内
 插入age=14,13都可以成功,不在间隙锁范围内。

间隙锁是给不存在的数据记录的范围加锁:
- 对于辅助索引,若值允许重复,在串行隔离级别中如果进行等值查询,InnoDB会给数据加上行锁和间隙锁(防止别的事务插入索引值重复的数据,造成幻读)
- 对于主键索引,或者唯一键索引,值不允许重复,那只需要加行锁就够了(对于唯一键索引,不可能发生插入索引值重复的数据)
|