先来看表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE a (
  `id`bigint AUTO_INCREMENT ,
  `a` int,
  `b` int,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`)
);

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`)
)

看一下join语句，因为b上没有索引，所以mysql用的BLNJ：

1
2
3
4

explain select * from a 
join b using(b)
where a = 1
order by a, b;

如果b表有索引的话：

1
2
3
4
5
6
7

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`b`)
)

可以发现a表idx_a_b有序性没有利用上，至于原因，先看一下BNLJ执行的流程图:

执行过程为：

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，直到 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取之后数据放入 join_buffer 中，继续执行第 2 步，直到所有数据读取完毕。

其中隐含的问题在于第二步：即使t1表的数据是有序读取到join_buffer中的，由于是先扫描t2表再关联join_buffer数据，导致join_buffer中的有序性失效。

如果表b有索引idx_b,那么使用BKA算法第二步的关联顺序与BNLJ相反，是先扫描join_buffer后通过索引关联t2,则可以利用join_buffer中的有序数据。

为了便于说明问题，我们先使用一个小一点儿的表，建表和初始化语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。

下面的语句序列，是怎么加锁的，加的锁又是什么时候释放的呢？

1

select * from t where d = 5 for update;

比较好理解的是，这个语句会命中 d = 5 的这一行，对应的主键 id = 5，因此在 select 语句执行完成后，会在id = 5 这一行主键上加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

我们知道，InnoDB 的默认事务隔离级别是可重复读，所以本文接下来没有特殊说明的部分，都是设定在可重复读隔离级别下。

幻读是什么？

现在，我们就来分析一下，假设只在 id = 5 这一行加锁，而其他行的不加锁的话，会怎么样。

下面先来看一下这个场景（这个结果是建立在前面假设之上，实际上是错误的）：

假设只在 id = 5 这一行加行锁，可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * fom t where d=5 for update。我们来看一下这三条 SQL 语句，分别会返回什么结果。

1. Q1 只返回 id = 5 这一行；
2. 在 T2 时刻，session B 把 id = 0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id = 0 和 id = 5 这两行；
3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id = 0、id = 1 和 id = 5 的这三行。

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

这里，我需要对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。
2. 上面 session B 的修改结果，被 session A 之后的 select 语句用当前读看到，不能称为幻读。幻读仅专指新插入的行。

如果只从我们学到的事务可见性规则来分析的话，上面这三条 SQL 语句的返回结果都没有问题。

因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值。并且，session B 和 sessionC 的两条语句，执行后就会提交，所以 Q2 和 Q3 就是应该看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。

幻读有什么问题？

首先是语义上的。session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。所以我们假设只锁了id = 5这一行的语义与select * from t where d = 5 for update 不同。

其次，是数据一致性的问题。 这个数据不一致到底是怎么引入的？肯定是前面的假设有问题。

我们把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。

由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

这样对于 id = 0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

1
2
3
4
5
6
7

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*/
 
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id = 0 这一行的最终结果也是 (0,5,5)。所以，id = 0 这一行的问题解决了。

但同时你也可以看到，id = 1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都上了锁，还是阻止不了 id = 1 这一行的插入和更新呢？

原因很简单。在 T3 时刻，我们给所有行加锁的时候，id = 1 这一行还不存在，不存在也就加不上锁。

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决幻读？

现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

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

也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

现在你知道了，数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

比如行锁，分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

也就是说，跟行锁有冲突关系的是“另外一个行锁”。

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

这句话不太好理解，我给你举个例子：

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

间隙锁和行锁合称 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]。

备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间。

你可能会问说，这个 supremum 从哪儿来的呢？

这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

1
2
3
4
5
6
7
8
9

begin;
select * from t where id=N for update;
 
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
 
commit;

这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？

这里，我用两个 session 来模拟并发，并假设 N=9。

图 8 间隙锁导致的死锁

你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

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

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

你现在知道了，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

你可能会说，为了解决幻读的问题，我们引入了这么一大串内容，有没有更简单一点的处理方法呢。

我在文章一开始就说过，如果没有特别说明，今天和你分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

• 幸存者偏差：高估了成功的概率
• 看清楚自己
• 是否高估自己
• 是否陷入从众心理
• 是否与沉没成本难舍难分
• 是否是互惠互利陷阱
• 摆脱确认偏差，不要证明自己是正确的
• 是否屈服于权威
• 是否依据对比来判断（请不要怎么做）
• 现成偏见
• 是否故意营造一种危机气氛
• 明确判断他人的能力范围
• 警惕控制错觉
• 警惕激励过敏
• 是否是平均值会起作用？
• 警惕公地悲剧
• 切勿只以结果判断决定
• 面对选择，遵守自己的标准
• 排除对他人好感的影响
• 禀赋效应 let it go
• 无巧不成书，如果从不发生才令人感到意外
• 唱反调的人也许是最重要的人
• 0风险偏误
• 稀有性谬论
• 概率偏误-前车之鉴后事之师
• 独立事件-赌徒谬论
• 锚定效应，你的锚在哪里？
• 警惕归纳法，警惕黑天鹅事件
• 厌恶风险，但不要无视损失
• 社会性懈怠
• 赢家诅咒-你会得到什么、失去什么
• 是否高估了人的影响
• 相互关系不等同于因果关系
• 避免光环效应过度影响总体印象
• 合理不一定真实
• 不要被框架效应限制注意力
• 遇到不明情况，就会发生行动偏误
• 不作为偏误，如果不解决问题，你就是问题的一部分
• 自利偏误，为什么你从来不自责
• 避免长时间负面影响
• 自我选择偏误
• 联想偏误，不要欠拟合也不要过拟合
• 认知失调，是否在自我安慰
• 即使行乐，只限周末，不要为了眼前的利益破坏未来的利益