MySQL 间隙锁

本贴最后更新于 300 天前,其中的信息可能已经沧海桑田

💡 间隙锁锁住的是一个区间,而不是一条记录

如何产生一个间隙锁?

我们先通过 SQL 模拟产生一个间隙锁,环境如下:

  1. MySQL InnoDB 引擎
  2. 事务隔离级别:Repeatable Read(可重复读,以下简称 RR

创建一张表 test​,只有一个主键字段 id

CREATE TABLE test
(
    id INT PRIMARY KEY
);

插入一些初始数据

INSERT INTO test (id)
VALUES (3),
       (7),
       (11);

目前表中数据如下:

image

间隙可以理解为索引记录之外的空隙,由于主键也是一种索引,故当前存在的间隙有:

(-∞, 3)

(3, 7)

(7, 11)

(11, +∞)

此时开启一个事务(隔离级别 RR),并执行 SELECT ... FOR UPDATE​语句

BEGIN;
SELECT * FROM test WHERE id = 5 FOR UPDATE;

查看锁的情况

SELECT * FROM performance_schema.data_locks;

image

对上图的解释:

INDEX_NAME: PRIMARY​表示主键索引

LOCK_MODE: GAP​表示间隙锁

LOCK_DATA: 7​表示索引值为 7 的那条记录,前面的间隙被锁住,即 (3, 7)​这个区间

我们可以插入一条 id​为 4 的记录进行验证

# 新建一个会话
INSERT INTO test (id) VALUES (4);

image

可以看到 INSERT​语句被阻塞了。

由此,我们可以得出一个结论:SELECT​一条不存在的数据,会锁住该数据所在的索引区间

比如上面那条查询语句1针对 id​为 5 的数据进行查询,5 在区间 (3, 7)​内

DELETE​和 UPDATE​语句同理

为什么要锁住一个间隙?

在 RR 隔离级别下,为了解决幻读问题,而引入了间隙锁

当执行 SELECT * FROM test WHERE id = 5 FOR UPDATE​这条语句时,结果为空,为了避免在该事务的后续查询中能查到数据(幻读),故需要加锁。因为本质上只需要锁住 id​为 5 的数据,但又无法针对不存在的记录进行加锁,故对 5 所在的一个区间进行加锁,即 (3, 7)​,这样锁的范围就不至于太大,但同时也存在误伤,比如 4、6 这两个 id​也变得无法插入了。

同理可得:

  • 当查询 id​​为 9 的记录时,锁住的区间是 (7, 11)​​
  • 当查询 id​​为 15 的记录时,锁住的区间是​(11, +∞)​​

那么为什么不是锁住 (4, 6)​,这样不是范围更小吗?

反证法:如果能锁住 (4, 6)​,那理论上也能直接锁 [5]​这个区间,又因为锁不能直接加在不存在的记录上,产生悖论。

💡 4 和 6 都是不存在的索引记录,所以不能作为间隙锁区间的临界点

最佳实践

  1. 不推荐把 Repeatable Read​作为默认的事务隔离级别,更推荐的是 Read Committed​(读已提交)
  2. 在 RR 隔离级别下,多线程并发进行先删后增操作时,切忌无脑 DELETE​,应该先根据 id 查询,有记录才 DELETE​,没有记录不做任何操作。无脑 DELETE​有概率产生间隙锁误伤,导致其他线程的 INSERT​操作被阻塞!


  1. BEGIN;
    SELECT * FROM test WHERE id = 5 FOR UPDATE;
    
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    676 引用 • 535 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...