LOGO OA教程 ERP教程 模切知识交流 PMS教程 CRM教程 开发文档 其他文档  
 
网站管理员

为什么非唯一索引会触发间隙锁?

freeflydom
2025年3月6日 9:52 本文热度 544

在 MySQL 的 InnoDB 存储引擎中,非唯一索引触发间隙锁的核心原因是为了 防止幻读(Phantom Read),尤其是在 REPEATABLE READ(可重复读)隔离级别下。以下从原理、场景和示例详细解释:


1. 间隙锁的作用

间隙锁(Gap Lock)是 InnoDB 在索引记录之间的 间隙(Gap) 上施加的锁,用于阻止其他事务在这些间隙中插入新数据,从而保证事务执行期间查询结果集的一致性。

示例场景
假设事务 A 执行 SELECT * FROM users WHERE age = 25 FOR UPDATE,若 age 是非唯一索引,InnoDB 会锁定 age = 25 的所有记录以及相邻的间隙,阻止其他事务插入 age = 25 的新记录。


2. 非唯一索引的结构特点

非唯一索引允许存在重复的键值,因此索引树中可能存在多个相同键值的记录。
当通过非唯一索引查询时,InnoDB 无法仅通过锁定单一行来避免幻读,因为其他事务可能在 相同的键值范围内插入新数据

示例数据:

假设表 users 的 age 是非唯一索引,数据如下:

+----+-----+
| id | age |
+----+-----+
| 1  | 20  |
| 3  | 25  |
| 5  | 25  | 
| 7  | 30  |
+----+-----+

索引 age 的结构(按升序排列):

(20) → (25) → (25) → (30)

3. 非唯一索引触发间隙锁的场景

场景 1:等值查询(age = 25

-- 事务 A
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;
  • 锁定范围

    1. 行锁:锁定所有 age = 25 的记录(id=3 和 id=5)。
    2. 间隙锁:锁定 age = 25 的前后间隙:
      • 左间隙:(20, 25)
      • 右间隙:(25, 30)
  • 其他事务插入 age = 25 的新数据会被阻塞

    -- 事务 B(阻塞)
    INSERT INTO users (id, age) VALUES (6, 25);
    

场景 2:范围查询(age > 20

-- 事务 A
BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE;
  • 锁定范围
    1. 行锁:锁定 age = 25 和 age = 30 的记录。
    2. 间隙锁:锁定所有符合条件的间隙:
      • (20, 25)(25, 25)(25, 30)(30, +∞)

4. 对比唯一索引的行为

如果是 唯一索引(如主键 id),等值查询不会触发间隙锁:

-- 事务 A
BEGIN;
SELECT * FROM users WHERE id = 3 FOR UPDATE;
  • 锁定范围:仅锁定 id = 3 的行,不涉及间隙锁。
  • 其他事务插入 id = 4 不会被阻塞
    -- 事务 B(成功)
    INSERT INTO users (id, age) VALUES (4, 25);
    

例外:唯一索引的范围查询仍会触发间隙锁:

-- 事务 A
BEGIN;
SELECT * FROM users WHERE id > 3 FOR UPDATE;
  • 锁定 id > 3 的所有行和间隙(如 (3, 5)(5, 7)(7, +∞))。

5. 为什么非唯一索引必须加间隙锁?

  • 防止幻读:如果仅锁定现有行,其他事务可以在间隙中插入相同键值的新数据,导致事务 A 的后续查询出现“幻行”。
  • 保证可重复读:事务 A 在多次查询同一范围时,结果集必须一致。

6. 如何避免非唯一索引的间隙锁?

方案 1:改用 READ COMMITTED 隔离级别

在 READ COMMITTED 级别下,InnoDB 不使用间隙锁(仅行锁),但可能产生幻读:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

方案 2:优化查询条件

尽量使用 唯一索引 或 精确匹配,减少范围查询。

方案 3:缩短事务时间

尽快提交事务,减少锁持有时间。


总结

非唯一索引触发间隙锁的本质是 防止在重复键值范围内插入新数据导致幻读。理解这一机制有助于:

  1. 合理设计索引(优先使用唯一索引)。
  2. 在高并发场景下优化锁竞争(如选择 READ COMMITTED)。
  3. 避免大范围查询对性能的影响。

​转自https://juejin.cn/post/7475688052909899802


该文章在 2025/3/6 9:52:59 编辑过
关键字查询
相关文章
正在查询...
点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。
点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理,结合码头的业务特点,围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体,是物流码头及其他港口类企业的高效ERP管理信息系统。
点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。
点晴免费OA是一款软件和通用服务都免费,不限功能、不限时间、不限用户的免费OA协同办公管理系统。
Copyright 2010-2025 ClickSun All Rights Reserved