间隙锁只在非唯一索引 间隙锁 索引

前言：间隙锁旨在锁住某个区间而非单个值，但本人对于网络上的帖子，即间隙索引只能存在与非聚集索引中存在疑惑，故亲自试验证明。

概念说明：

    间隙锁：左右开区间的一段范围锁

    临键锁：行锁+间隙锁，即锁住包括当前行记录在内的一段左开右闭区间

注意：间隙锁和临键锁都是为了防止幻读而产生的，且隔离级别必须要在RR级别下

目录

准备工作

测试聚集索引上，是否存在间隙锁

(1)测试命中已存在的记录时是否会触发间隙锁

(2)测试命中不存在的记录时是否会触发间隙锁

---

准备工作

测试环境:MySql5.7.34,SQLyog Community64

创建表lock_test，建立普通索引idx_age，添加四条数据以供测试

CREATE TABLE lock_test
(
	id INT PRIMARY KEY,
	age INT,
	NAME VARCHAR(64),
	INDEX idx_age ( age )
)

INSERT INTO lock_test VALUES(10,10,'aaa');
INSERT INTO lock_test VALUES(20,20,'bbb');
INSERT INTO lock_test VALUES(30,30,'ccc');
INSERT INTO lock_test VALUES(40,40,'ddd');
COMMIT;

在sqlyog中相同连接分别开启两个窗口

 在2个窗口中执行如下语句，将事务都设置为不自动提交

/**设置事务不自动提交**/
SET autocommit = 0
/**查看是否RR隔离级别**/
SHOW VARIABLES LIKE 'tx_isolation'

测试聚集索引上，是否存在间隙锁

根据如上数据，主键id上如有间隙锁，应当是(负无穷,10),(10,20),(20,30),(30,40),(40,正无穷)

(1)测试命中已存在的记录时是否会触发间隙锁

窗口1中执行

SELECT * FROM lock_test WHERE id = 10 FOR UPDATE

窗口2中执行

INSERT INTO `lock_test` VALUES(11,160,'aaac')

按理说若能命中(10,20)的间隙锁，窗口2会发生阻塞，但发现并没有阻塞， 直接执行成功，这就说明在主键索引上，当等值查询已存在的记录时，不会触发间隙锁

(2)测试命中不存在的记录时是否会触发间隙锁

将刚才第一步中的测试数据删除，将我们的数据初始化。

 窗口1中执行

SELECT * FROM lock_test WHERE id = 12 FOR UPDATE

窗口2中执行

INSERT INTO `lock_test` VALUES(14,160,'aaac')

id=12的记录并不存在，窗口2执行语句后发现阻塞，这是因为触发了(10,20)的区间，导致id为14的 记录也添加不进去，当窗口1中commit之后，窗口2即可运行成功，数据已被添加了进去

结论

聚集索引在命中不存在记录时，会开启间隙锁，锁住对应间隙，否则就只会触发行记录锁，锁住当条记录