MySQL FOR UPDATE 误锁全表原因 InnoDB 三层锁原理与锁故障排查

很多开发者使用 SELECT … FOR UPDATE 时误以为仅锁定单行数据，在 RR 隔离级别下常出现大范围锁表、业务阻塞故障，根源是不熟悉 InnoDB 多层锁机制。本文分层拆解 MDL、意向锁、行锁的作用与区别，对比 RC、RR 锁策略差异，分析无索引锁表、长事务阻塞等线上痛点，附带锁问题实用排查 SQL。

一、MySQL 有三层锁同时存在

你写一条 UPDATE，InnoDB 同时在三个层面上了锁：

UPDATE user SET age = 30 WHERE id = 1;
表级：MDL 读锁       → 保结构不变（有人在改数据，别动表结构）
表级：意向独占锁 IX   → 表上打个标记"里面有行被锁了"
行级：行独占锁 X      → 锁住 id=1 这一行

MDL 读锁兼容读锁，MDL 写锁排斥一切。 命名上注意：INSERT/DELETE/UPDATE 虽然是”写数据”，但对表结构来说只是”用表”，所以拿 MDL 读锁。

最经典的坑：长事务改表，整库卡死

事务 A（长事务）：SELECT * FROM user;  还没提交 → 持有 MDL 读锁
事务 B：ALTER TABLE user ADD COLUMN xxx;
        → 需要 MDL 写锁 → 等 A 释放读锁
事务 C：SELECT * FROM user;
        → 也需要 MDL 读锁 → 但排在 B 后面 → 也被阻塞
        → 瞬间：这张表的所有读写全堵死

MDL 读锁本来互相兼容——但写锁排队的瞬间，后面的所有读写也跟着排队。锁队列是先到先得，B 排在 C 前面，C 不能插队。

解决方案：改表前先 SELECT 查一下有没有长事务，或者用 LOCK_WAIT_TIMEOUT 让改表别无限等。

三、表锁 vs MDL vs 意向锁

• 表锁 = 手动扔核弹（LOCK TABLES WRITE 把这表全锁了）；
• MDL = 自动保安（有 DDL 才打架，DML 之间相安无事）；
• 意向锁 = 门口登记表（有人要锁整表时看一眼：里面有行锁吗？有就等）。

意向锁的意义：有人 LOCK TABLES user WRITE 时，不用一行行去查有没有行锁——看一眼表上有没有 IX 标记就行。

四、行级锁

InnoDB 有三种行级锁，但不是你显式选哪个，而是同一句 SQL 在 RR 下自动加的组合：

记录锁（Record Lock）

锁住存在的索引记录。所有精确命中的写操作都会加：

UPDATE user SET age = 30 WHERE id = 5;        -- 锁 id=5 这一行
DELETE FROM user WHERE id = 5;                -- 锁 id=5
SELECT * FROM user WHERE id = 5 FOR UPDATE;   -- 锁 id=5

间隙锁（Gap Lock）

锁住索引记录之间的空隙。只在 RR 级别下才加：

索引 age：15 → [间隙] → 22 → [间隙] → 25 → [间隙] → 30
            ↑           ↑           ↑           ↑
         值有记录锁    空隙有间隙锁   ...
事务 A：SELECT * FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE;
事务 B：INSERT INTO user VALUES(NULL, '王五', 23);
        → 23 落在 22~25 的间隙 → 被间隙锁阻塞 → 等待

间隙锁是 RR 防幻读的真正手段——不让新行插进查询范围。

Next-Key Lock（记录锁 + 间隙锁合体）

RR 级别默认就是这个：锁住记录 + 它前面的间隙。

-- RR 下，即使你只查一行
SELECT * FROM user WHERE id = 10 FOR UPDATE;
→ 加的实际上是 (上一个 id, 10] 这个区间
→ 前面的间隙也锁住了

五、为什么 RC 有行锁，还是会发生不可重复读

因为行锁管的是写写互斥，不管读。MVCC 的读走的是另一条路：

MVCC 快照读（无锁）        行锁（有锁）
─────────────────         ──────────
SELECT（纯读）             UPDATE
SELECT（纯读）             DELETE  
                          SELECT ... FOR UPDATE
两条线完全独立！

不可重复读的发生路径：

事务 A：SELECT age → 快照读 age=25（走左边，无锁，完全不管右边在干嘛）
事务 B：UPDATE age=30; COMMIT（走右边，拿行锁写完提交）
事务 A：SELECT age → RC 新建快照 → age=30（不可重复读！）

行锁管的是”两个写同一行别打架”，从来不管”读看到什么数据”。

六、没索引的 FOR UPDATE ≈ 表锁

InnoDB 的行锁加在索引记录上。没索引怎么办？全表扫描，扫到一行锁一行：

-- age 没索引
SELECT * FROM user WHERE age > 25 FOR UPDATE;
→ 全表扫描 → 扫到的每一行全加锁 → age=20、15、50 都锁
→ 等效表锁，所有写操作全堵

所以 WHERE 条件走索引不只是为了快——更是为了让锁的范围尽可能小。

七、长事务的锁是如何一步步把数据库拖死的

长事务持锁不释放，最可怕的不是死锁，是锁等待传导：

事务 A（10 分钟）：SELECT ... FOR UPDATE，锁住订单 id=100
事务 B：UPDATE 订单 id=100 → 等 A 释放
事务 C：UPDATE 订单明细，外键关联 id=100 → 等 B 释放
事务 D：INSERT 订单日志，间隙被 A 锁了 → 等 C
...
一排车堵在单行道，头车不动，后面全卡

死锁还好——InnoDB 会主动检测并回滚一个。锁等待超时更隐蔽：业务看到的是”操作超时请重试”，用户以为系统挂了。

此外，RR 的间隙锁在高并发下是性能杀手：

-- 秒杀：100 个用户同时下单
SELECT stock FROM product WHERE id = 100 FOR UPDATE;
RC：只锁 id=100 这一行，同行的 UPDATE 排队，INSERT 不受影响
RR：行 + 前间隙全锁，INSERT 也被堵 → 排队人数翻倍

这就是为什么高并发场景普遍选 RC——锁的范围就是并发度的上限。

八、各种写操作触发什么锁

纯 SELECT 不加任何行锁——MVCC 快照读。这也是”读不阻塞写”的根因。

九、线上怎么排查锁问题

-- 1. 看当前谁在等锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 2. 看当前持有哪些锁
SELECT * FROM information_schema.INNODB_LOCKS;  -- 5.7
SELECT * FROM performance_schema.data_locks;     -- 8.0
-- 3. 找长事务（超过 60 秒的）
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_started < NOW() - INTERVAL 60 SECOND;

总结

1. 三层锁同时存在：MDL 锁表结构 + 意向锁做标记 + 行锁锁数据，各管各的
2. RC 和 RR 的核心锁差别：RC 不要间隙锁，RR 有间隙锁，这是并发吞吐差好几倍的主因
3. 行锁不保护读：MVCC 和锁是两条独立的路，不可重复读不是行锁失效，是读根本没走锁
4. 没索引的 FOR UPDATE 就是表锁：锁加在索引记录上，没有索引就全表扫全表锁
5. 长事务的锁会传导放大：不是死锁才致命，锁等待超时更隐蔽

MySQL 锁由 MDL、意向锁、行锁协同管控，MDL 防护表结构变更，行锁约束数据修改。RC 舍弃间隙锁提升并发，RR 依靠间隙锁杜绝幻读。优化时合理选用隔离级别、保证查询命中索引、规避超长事务，便能精准缩小锁范围，有效避免数据库大面积阻塞。

以上关于MySQL FOR UPDATE 误锁全表原因 InnoDB 三层锁原理与锁故障排查的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。