MySQL 间隙锁

串行化隔离级别怎么解决幻读问题？
先说下幻读的含义，幻读就是在事务中按照同样的条件前后两次查询的结果数据量不同。

解决串行化的幻读问题用间隙锁（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会给数据加上行锁和间隙锁（防止别的事务插入索引值重复的数据，造成幻读）
• 对于主键索引，或者唯一键索引，值不允许重复，那只需要加行锁就够了（对于唯一键索引，不可能发生插入索引值重复的数据）