Mysql 间隙锁、next-key lock

文章目录[隐藏]

数据库内容
基本概念
间隙锁
next-key lock
参考

数据库内容

表 t

id(主键)	c（普通索引）	d（无索引）
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25

基本概念

间隙锁

间隙锁是事务隔离级别为可重复读的条件下的东西
间隙锁是加在主键上的
间隙锁是用来解决幻读问题的

-- 以下代码会上间隙锁 (5,10)
-- 上锁后，无法插入 id∈(5,10) 的记录

select * from t where id = 6 for update;

next-key lock

next-key lock = 间隙锁 + 行锁
next-ley lock 加锁的基本单位是 (x,y] 为前开后闭区间

-- 以下代码会上 next-key lock (10,15] 和 行锁 id=10

select * from t where id >= 10 and id < 11 for update;

间隙锁

事务之间不互斥

例如，事务A锁住(1,5)，同时，事务B也可以锁住(1,5)。

但是这样增大了产生死锁的概率，如下图就会产生死锁：

next-key lock

加锁规则

原则 1：加锁的基本单位是 next-key lock，希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，记录不存在时（向右遍历时且最后一个值不满足等值条件的时候），next-key lock 退化为间隙锁。
优化 2：唯一索引上的等值查询，next-key lock 退化为行锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

索引的等值查询（包括唯一索引和非唯一索引），当记录不存在时，退化成间隙锁

索引的等值查询（包括唯一索引和非唯一索引），当记录不存在时(向右遍历时且最后一个值不满足等值条件的时候)，退化成间隙锁

-- 以下代码会上间隙锁 (5,10)

select * from t where id = 6 for update;

唯一索引上的等值查询

-- 以下代码只会对 id=10 上行锁

select * from t where id = 10 for update;

唯一索引上的范围查询

-- 1.上 next-key lock (5,10]，由于有唯一索引上的等值查询 id=10，故退化成行锁，只锁id=10
-- 2.由于是范围查询，会访问到 id=15 的记录，故上锁 next-key lock (10,15]
-- 3.综上，上锁区域为 [10,15]

select * from t where id >= 10 and id < 11 for update;

-- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
-- 如下语句，会访问到 id=20 的记录

select * from t where id >= 10 and id <= 15 for update;

普通索引上的等值查询

-- 1.上 next-key lock (5,10]
-- 2.由于是普通索引，会访问到 c=15(id=15) 的记录，故上锁 next-key lock (10,15]
-- 3.综上，上锁区域为 (5,15]

select * from t where c = 10 for update;

普通索引上的范围查询

-- 1.上 next-key lock (5,10]，虽然其中有等值查询 c=10，但是由于 c 不是唯一索引，故不会退化成行锁
-- 2.由于是范围查询，会访问到 c=15（id=15） 的记录，故上锁 next-key lock (10,15]
-- 3.综上，上锁区域为 (5,15]

select * from t where c >= 10 and c < 11 for update;