MySQL InnoDB 二级索引加锁范围过大导致阻塞

适用版本：MySQL 8.0 / Oracle 19c / PostgreSQL 16
核心场景：MySQL InnoDB 的 SELECT ... FOR UPDATE 通过二级索引扫描时，会锁住扫描到的所有索引记录，而非仅锁住 WHERE 条件最终匹配的行。当 WHERE 条件包含二级索引中不存在的列时，回表过滤前已被锁住的行不会被释放，导致其他事务修改看似无关的行时被阻塞。本文通过实验验证该行为，并与 Oracle、PostgreSQL 的锁机制做对比。
分类标签：MySQL锁机制、索引设计、Oracle对比、PostgreSQL对比

一、问题现象

生产环境中，Session A 执行 SELECT ... FOR UPDATE 只锁定了 1 行数据，但 Session B 尝试 UPDATE 另一行完全不同的数据时却被阻塞。明明不是同一行数据，为什么会互相阻塞？

二、环境与表结构

2.1 环境信息

2.2 表结构与数据

-- 隔离级别确认
SHOW VARIABLES LIKE '%iso%';
-- transaction_isolation | READ-COMMITTED

-- 建表
CREATE TABLE test_idx (
    id    INT NOT NULL,
    name  VARCHAR(20) DEFAULT NULL,
    age   INT DEFAULT NULL,
    dept  VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_age (age)          -- 二级索引只有 age 列
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 数据
INSERT INTO test_idx VALUES (1, 'zhangsan', 30, 'hhh');
INSERT INTO test_idx VALUES (2, 'zhang',    30, 'it');
INSERT INTO test_idx VALUES (5, 'lisi',     30, 'it');

关键点：二级索引 idx_age 只包含 age 列，不包含 name 列。

2.3 索引结构示意

二级索引 idx_age（age 列）          聚簇索引（主键 id）
┌─────────┬─────┐                 ┌─────┬──────────┬─────┬──────┐
│  age    │ id  │                 │ id  │ name     │ age │ dept │
├─────────┼─────┤                 ├─────┼──────────┼─────┼──────┤
│   30    │  1  │ ──回表──→       │  1  │ zhangsan │  30 │ hhh  │
│   30    │  2  │ ──回表──→       │  2  │ zhang    │  30 │ it   │
│   30    │  5  │ ──回表──→       │  5  │ lisi     │  30 │ it   │
└─────────┴─────┘                 └─────┴──────────┴─────┴──────┘

三、问题复现

Session 1：锁定 1 行，实际锁了 3 行

-- Session 1
BEGIN;
SELECT * FROM test_idx WHERE age = 30;
-- 返回 3 行：id=1(zhangsan), id=2(zhang), id=5(lisi)

SELECT * FROM test_idx WHERE name = 'zhang' AND age = 30 FOR UPDATE;
-- 返回 1 行：id=2(zhang)，但底层已经锁了 idx_age 上 age=30 的所有 3 条记录

Session 2：修改看似无关的行，被阻塞

-- Session 2
BEGIN;
SELECT * FROM test_idx WHERE name = 'zhangsan' AND age = 30;
-- 返回 1 行：id=1(zhangsan) ← 这行数据看起来和 Session 1 锁的完全无关

UPDATE test_idx SET dept = 'ab' WHERE name = 'zhangsan' AND age = 30;
-- 阻塞！等待 Session 1 释放锁

Session 3：查看锁信息

-- Session 3：诊断锁等待
SELECT locked_table, locked_index, locked_type,
       waiting_trx_id, blocking_trx_id, blocking_lock_mode
FROM sys.innodb_lock_waits\G

-- 结果：
-- locked_index: idx_age
-- locked_type: RECORD
-- blocking_lock_mode: X,REC_NOT_GAP

关键发现：锁加在二级索引 idx_age 上，锁模式为 X,REC_NOT_GAP（排他记录锁，无间隙锁）。

四、根因分析：InnoDB 加锁原理

4.1 InnoDB 加锁的核心机制

MySQL 官方文档（17.7.1 InnoDB Locking）明确说明：

"Record Lock: A lock on an index record. Record locks always lock index records, even if a table has no indexes."

这意味着：

• InnoDB 的行锁本质上是对索引记录的锁，不是对物理行的锁
• 如果表没有二级索引，InnoDB 使用聚簇索引（主键）加锁
• 如果表连主键都没有，InnoDB 自动创建一个 6 字节的隐藏主键

4.2 为什么锁了 3 行而不是 1 行

核心原因：name 列不在二级索引 idx_age 中。

Session 1 执行：SELECT ... WHERE name='zhang' AND age=30 FOR UPDATE

InnoDB 的执行路径：
│
├─ Step 1：优化器选择走二级索引 idx_age（因为 WHERE 中有 age=30）
│
├─ Step 2：扫描 idx_age，找到 age=30 的所有记录
│   ├── 锁住 idx_age 记录 (30, id=1) ← 加锁
│   ├── 锁住 idx_age 记录 (30, id=2) ← 加锁
│   └── 锁住 idx_age 记录 (30, id=5) ← 加锁
│
├─ Step 3：回表到聚簇索引，读取完整行
│   ├── id=1: name='zhangsan' ≠ 'zhang' → 不匹配，但锁已经加了！
│   ├── id=2: name='zhang' = 'zhang'    → 匹配 ✅
│   └── id=5: name='lisi' ≠ 'zhang'     → 不匹配，但锁已经加了！
│
└─ 结果：返回 1 行给用户，但 3 行都被锁住了

4.3 官方文档原文佐证

MySQL 8.0 官方文档（17.7.3 Locks Set by Different SQL Statements）：

"A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned."

翻译：

锁定读、UPDATE、DELETE 会对 SQL 语句处理过程中扫描到的每一条索引记录加锁。即使 WHERE 条件会排除该行也无所谓。InnoDB 不记住精确的 WHERE 条件，只知道扫描了哪些索引范围。

这是 InnoDB 加锁的设计特性，不是 Bug。

4.4 RC 隔离级别下的锁行为

版本差异说明：

• READ COMMITTED（RC）：禁用 Gap Lock（仅外键约束检查和唯一键冲突检查除外），只加 Record Lock。RC 级别下对非匹配行的锁会立即释放（但仅限使用唯一索引的等值查询）。
• REPEATABLE READ（RR）：使用 Next-Key Lock（Record Lock + Gap Lock），锁的范围更大。
• SERIALIZABLE：所有查询都加共享 Next-Key Lock。

本例中：idx_age 是非唯一索引，即使使用等值查询 age=30 也会扫描到 3 条记录。在 RC 级别下虽然不锁间隙，但扫描到的 3 条索引记录都会被加 Record Lock，且不会因回表过滤而释放不匹配行的锁。

4.5 加锁路径完整示意图

SELECT ... WHERE name='zhang' AND age=30 FOR UPDATE

                 ┌──────────────────────┐
                 │   WHERE 条件解析      │
                 │  age=30 AND name='zhang'│
                 └──────────┬───────────┘
                            │
                 ┌──────────▼───────────┐
                 │ 走二级索引 idx_age    │
                 │ （只含 age 列）       │
                 │ 无法用 name 过滤     │
                 └──────────┬───────────┘
                            │
                   扫描 age=30 的 3 条记录
                            │
              ┌─────────────┼─────────────┐
              ▼             ▼             ▼
     ┌────────────┐ ┌────────────┐ ┌────────────┐
     │(30, id=1)  │ │(30, id=2)  │ │(30, id=5)  │
     │  加 X 锁 🔒│ │  加 X 锁 🔒│ │  加 X 锁 🔒│
     └──────┬─────┘ └──────┬─────┘ └──────┬─────┘
            │              │              │
     ┌──────▼─────┐ ┌──────▼─────┐ ┌──────▼─────┐
     │ 回表 id=1  │ │ 回表 id=2  │ │ 回表 id=5  │
     │name=zhangsan│ │ name=zhang │ │ name=lisi  │
     │ ≠ 'zhang'  │ │ = 'zhang' ✅│ │ ≠ 'zhang'  │
     │ 但锁不释放  │ │  返回给用户  │ │ 但锁不释放  │
     └────────────┘ └────────────┘ └────────────┘

五、解决方案

5.1 方案一：建复合索引（推荐）

-- 将 WHERE 条件中所有等值过滤列纳入复合索引
ALTER TABLE test_idx DROP INDEX idx_age;
ALTER TABLE test_idx ADD INDEX idx_age_name (age, name);

效果：

复合索引 idx_age_name（age, name）
┌─────────┬──────────┬─────┐
│  age    │  name    │ id  │
├─────────┼──────────┼─────┤
│   30    │ lisi     │  5  │
│   30    │ zhang    │  2  │  ← 唯一匹配
│   30    │ zhangsan │  1  │
└─────────┴──────────┴─────┘

InnoDB 通过 (age=30, name='zhang') 精确扫描 → 只锁 1 条索引记录 → 只锁 1 行

为什么复合索引能解决：

• InnoDB 可以在二级索引层面完成 age=30 AND name='zhang' 的精确匹配
• 扫描范围缩小到 1 条索引记录，只对这 1 条加锁
• 回表时也不会锁额外的行

5.2 方案二：使用主键作为查询条件

-- 直接用主键定位，只锁 1 行
SELECT * FROM test_idx WHERE id = 2 FOR UPDATE;

5.3 方案对比

5.4 索引设计的锁优化建议

六、三库锁机制对比

6.1 锁定原理对比

6.2 同一场景在 Oracle 中的表现

-- Oracle 表结构（与 MySQL 相同）
CREATE TABLE test_idx (
    id    NUMBER NOT NULL PRIMARY KEY,
    name  VARCHAR2(20),
    age   NUMBER,
    dept  VARCHAR2(20)
);
CREATE INDEX idx_age ON test_idx(age);

-- Session 1
UPDATE test_idx SET dept = 'new' WHERE name = 'zhang' AND age = 30;
-- Oracle 只锁 WHERE 匹配的 1 行（通过 Rowid 定位）

-- Session 2
UPDATE test_idx SET dept = 'ab' WHERE name = 'zhangsan' AND age = 30;
-- 不阻塞！Oracle 锁的是具体数据行，不锁其他行

Oracle 不阻塞的原因：

Oracle 的锁机制与 InnoDB 完全不同：

1. Oracle 通过 ITL（Interested Transaction List） 在数据块头部记录事务信息
2. 每个被修改的行在行头部指向 ITL 中对应的事务槽
3. 锁是基于数据行本身的，不是基于索引记录
4. 即使使用二级索引定位，Oracle 也只对最终匹配的行加锁，不锁扫描路径上的其他行

6.3 同一场景在 PostgreSQL 中的表现

-- PostgreSQL 表结构
CREATE TABLE test_idx (
    id    INT NOT NULL PRIMARY KEY,
    name  VARCHAR(20),
    age   INT,
    dept  VARCHAR(20)
);
CREATE INDEX idx_age ON test_idx(age);

-- Session 1
BEGIN;
SELECT * FROM test_idx WHERE name = 'zhang' AND age = 30 FOR UPDATE;
-- 只锁 1 行

-- Session 2
BEGIN;
UPDATE test_idx SET dept = 'ab' WHERE name = 'zhangsan' AND age = 30;
-- 不阻塞！PostgreSQL 锁的是物理行，不锁其他行

PostgreSQL 不阻塞的原因：

PostgreSQL 的锁机制：

1. PostgreSQL 使用 MVCC（多版本并发控制），通过 xmin/xmax 事务 ID 标记行版本
2. SELECT FOR UPDATE 对匹配的行加行级排他锁，写入行的 xmax 字段
3. 锁是基于物理行的，与使用什么索引无关
4. PostgreSQL 没有 Gap Lock，不会锁范围

6.4 三库锁机制对比总结图

┌─────────────────────────────────────────────────────────────────────┐
│                    SELECT FOR UPDATE 加锁范围                        │
├──────────────┬──────────────────────────────────────────────────────┤
│  MySQL InnoDB │  锁 = 扫描到的所有索引记录（可能 >> WHERE 匹配行）   │
│              │  原因：锁的是索引记录，不记得 WHERE 条件              │
│              │  索引设计直接影响锁范围                                │
├──────────────┼──────────────────────────────────────────────────────┤
│  Oracle      │  锁 = WHERE 精确匹配的行                              │
│              │  原因：通过 ITL 在数据块内锁行，与索引无关              │
│              │  索引只影响查询速度，不影响锁范围                       │
├──────────────┼──────────────────────────────────────────────────────┤
│  PostgreSQL  │  锁 = WHERE 精确匹配的行                              │
│              │  原因：通过 xmin/xmax 在行头部标记锁，与索引无关        │
│              │  没有 Gap Lock，锁范围最小                             │
└──────────────┴──────────────────────────────────────────────────────┘

6.5 三库无索引时的加锁行为对比

这是 MySQL InnoDB 与 Oracle / PostgreSQL 在锁机制上最显著的区别。

七、总结

7.1 核心结论

1. MySQL InnoDB 的锁本质上是"索引记录锁"，不是"物理行锁"。InnoDB 永远锁索引记录，如果表没有索引则锁聚簇索引记录，如果没有聚簇索引则自动创建隐藏主键。
2. InnoDB 不记得 WHERE 条件，只记得扫描了哪些索引范围。扫描到的索引记录即使不满足 WHERE 条件也会被加锁。
3. 索引设计直接影响锁范围。复合索引覆盖 WHERE 条件的全部列，可以精确缩小扫描范围，减少不必要的锁。
4. Oracle 和 PostgreSQL 的锁机制与索引无关。它们锁的是具体数据行，无论用什么索引、甚至不用索引，都只锁 WHERE 匹配的行。
5. MySQL 无索引时会退化为全表锁，这是 MySQL InnoDB 特有的风险，Oracle 和 PostgreSQL 不存在此问题。

7.2 实践建议

7.3 避坑清单

1. 不要认为"查到 1 行就只锁 1 行"：InnoDB 锁的是索引扫描路径上的所有记录，不是最终返回的行。
2. 不要在 FOR UPDATE 的 WHERE 中使用索引中不存在的列做等值过滤：如果必须使用，确保这些列也在索引中（复合索引）。
3. 不要忽略无索引表的锁风险：无索引时 InnoDB 锁全表，生产环境中每张表必须有主键索引。
4. 从 Oracle 迁移到 MySQL 时不要照搬索引设计：Oracle 的索引只影响查询性能，MySQL 的索引还影响锁范围，需要额外关注。