mysql 间隙锁

前言

巴纳姆效应：人很容易相信一个笼统的一般性的人格描述，并认为他特别适合自己并准确的揭示了自己的人格特点，即使内容空洞。

注：这真的是一篇很重要的文章，这不是心理暗示

思考问题

1. InnoDB 中的加锁操作如果没有使用到索引，会怎样？
2. 在RR事务隔离级别下，在事务A里读取某个范围内的记录，这时事务B在该范围里插入了新的记录，事务A再次读取该范围的记录就会产生幻行。就算我们给范围内的所有记录都加上行锁，也无法阻止insert语句的执行，因为insert的是新的行，那怎么办？

几种锁的简单说明

看了很多文章，发现大部分都把间隙锁和临键锁完全混为一谈，所以有必要解释一下它们的区别

记录锁（Record Lock）

为某行记录加锁，封锁该行的索引记录。

	// name 列是唯一索引。
	select * from table where name = dustin for update;
	// id 为主键列
	update table set age = 18 where id = 10

注：如果name 不是唯一索引，那么这个加锁语句就会变成 临键锁（next-key lock）

记录锁的两个必要条件

• 记录锁针对的列必须是 唯一索引列（主键也是唯一索引）
• 如果是查询，那么查询语句必须为精确匹配（=），不能是>，<，like这种的，不然也会退化成临键锁

间隙锁（Gap Lock）

锁定一段范围内的索引记录，它是基于 Next-Key Locking 算法的

select * from table where id between 10 and 20 for update;

上面这条sql会锁住 (1,10) 区间内的所有记录行，都是开区间

• 间隙锁是基于 非唯一索引 的
• 间隙锁锁住的是一个区间，而不仅仅是区间内的每一条记录！！！
• 非唯一索引不管是锁住单条还是多条记录，都会产生间隙锁
• 唯一索引产生间隙锁的条件：锁住多条记录 或者 查询一条不存在的记录

临键锁（Next-Key Lock）

Next-Key 算是一种特殊的间隙锁，我们常说的间隙锁其实就是这个临键锁。通过临键锁可以解决上面的思考问题2（幻读）。

• 临键锁也是基于 非唯一索引 的
• 锁住的是一段 左开右闭 区间的数据

间隙锁和临键锁的事务隔离级别是RR，如果是RC就会失效

间隙锁的锁定区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间

场景解读

准备数据

间隙锁的测试表

说明：id为主键，number为普通索引，name无索引，数据库隔离级别为RR

插入测试数据，如下

该表number列潜在的间隙锁（临键锁）有：

(-∞, 1],
(1, 6],
(6, 12],
(12, 18],
(18, 24],
(24, +∞],

一、普通索引的间隙锁问题

• 1. 等值查询

分析：事务1执行了 number=6 的等值查询，按照上面提到的间隙锁锁定区域的算法，加锁的范围为 (1, 6], (6,12]，故事务2插入 number=7 的记录，被堵塞出现锁超时现象，如果插入 number=2 一样也会锁超时

• 2. 范围查询

分析：事务1执行的范围查找，因为是非唯一索引，索引加锁的范围是 (6,18]，故 事务2 插入 number=7 和 事务3 插入 number=14 会被堵塞，同样如果执行update语句，来更新 number=12 的记录也会被堵塞

• 3. 死锁

分析：事务1执行 number=11 的查询后，加锁范围是(6,12]，事务2执行 number=11 的查询后，同样获得 (6,12] 的间隙锁，然后事务1执行插入 number=9 的记录，等待事务2释放锁，堵塞住了，事务2 执行插入 number=8，等待事务1释放锁，也堵塞住了

• 4. 等值limit查询

分析：事务1 执行查询 number<19 的记录，限制2条，查到 number=1 和 number=6 这两条记录就停止了，所以加锁的范围是 (-∞, 6]，故事务2插入 number=5，被堵塞，事务3插入 number=7 成功（为了后续测试把事务3回滚了）

二、唯一索引的间隙锁问题

• 1. 查询一个不存在的记录

分析：事务1根据主键唯一索引查找了一个不存在的记录，产生了间隙锁，加锁范围 (1,6]，故事务2执行插入id=3的记录被堵塞，锁超时

• 2. 范围查询

分析：事务1执行范围查询，加锁范围为(12,18]，因为 查询条件里 包含 id=12，所以根据唯一索引的等值查询，id=12 升级为行锁，故加锁范围为 [12,18]，事务2执行插入 id=14 被堵塞

• 等值查询

分析：事务1执行唯一索引的等值查询，故不产生间隙锁，而是为 id=12 的行加上行锁，事务2执行插入id=13插入成功