【第20天】每天一个MySQL知识点，百日打怪升级

行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表？

大家好，我是一名拥有10年以上经验的DBA老兵，没有那多。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第20天内容。

前三天聊了事务隔离级别和三大问题。但有个东西一直藏在背后——锁。

隔离级别不是概念，是靠 MVCC（多版本并发控制）和锁配合实现的。RC怎么做到避免脏读？RR怎么做到的不可重复读？靠的是MVCC一致性读快照。Serializable靠什么串行化？靠的是给普通SELECT也隐式加锁。而MVCC的底层，也离不开锁来保护数据结构的并发访问。

但大多数人第一次接触到"锁"这个字，不是从原理书里，而是从一条报警短信里：

Lock wait timeout exceeded; try restarting transaction

然后就开始百度"MySQL锁等待超时怎么办"。今天咱们把这个东西讲透——从最基础的行锁 vs 表锁开始。

最核心的区别：锁住了什么

行锁：你在表上有一行 id=1 的数据加了行锁，别人照样可以改 id=2。互不影响。

表锁：你锁了整张表，别人想改任何一行都得等你释放。

听起来行锁完胜对不对？但事情没这么简单。

致命陷阱：看似行锁，实际是表锁

这是生产环境最常见的锁故障，没有之一。

InnoDB 的行锁是挂在索引上的。如果你的 WHERE 条件没有命中索引，InnoDB 只能全表扫描——会锁住扫描过程中碰到的每一行。最终结果是整张表的所有行都被锁住了，并发效果和表锁完全一样。（注意这不算真正的"锁升级"，InnoDB 没有行锁→表锁的升级机制，本质是一大堆行锁。）

-- 假设表结构：CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), age INT);
-- 索引情况：id 有主键索引，age 没有索引

-- 事务 A：按非索引列更新
BEGIN;
UPDATE t1 SET name = 'new' WHERE age = 25;
-- age 没有索引，InnoDB 只能全表扫描
-- 每一行都会加锁，最终所有行都被锁住！

-- 事务 B：想更新另一行（完全不同的数据）
UPDATE t1 SET name = 'other' WHERE id = 2;
-- 被阻塞！明明操作的不是同一行

这就是经典的生产事故：你在开发环境跑得好好的，因为数据量小，全表扫描很快。上了生产，某个查询走了全表扫描，把所有行都锁死了，所有更新排队，系统瞬间卡死。

我处理过一个case：凌晨2点告警，某核心表的更新全部超时。查出来是一个定时任务跑了 UPDATE ... WHERE status = 'pending'，status 字段没有索引，60万行全表扫描，锁了10分钟。加了个索引，问题消失。

原则：UPDATE 和 DELETE 的 WHERE 条件一定要走索引。不是为了快，是为了避免锁住全表。

InnoDB 的三种行锁

锁不是非黑即白的。InnoDB 在行级别上有三种锁模式：

Next-Key Lock 是最有意思的。它锁的不是"一个值"，而是"一个区间"。比如你的索引值有 5、10、15，Next-Key Lock 会锁住 (-∞, 5]、(5, 10]、(10, 15]、(15, +∞) 这些区间。

这就是 RR 级别下防幻读的秘密：你查 WHERE id > 5 AND id < 15 FOR UPDATE，InnoDB 把 (5, 10] 和 (10, 15) 区间都锁住了，别人想插入 id=8 或 id=12——等你的事务结束再说。

不过话说回来，有一次我调试一个奇怪的锁等待问题，查 SHOW ENGINE INNODB STATUS 看了半天没看出名堂。后来发现是我把 RC 和 RR 搞混了——RC 下 Gap Lock 是关闭的，我以为加了 FOR UPDATE 就能防幻读，实际上在 RC 下 FOR UPDATE 只锁行不锁间隙。吃了一次亏之后长了记性。

RR 下：SELECT ... FOR UPDATE → Next-Key Lock（行+间隙一起锁）
RC 下：SELECT ... FOR UPDATE → Record Lock（只锁行，不锁间隙）

意向锁：你感觉不到但一直在用的锁

InnoDB 还有两种"幕后锁"：意向共享锁（IS） 和 意向排他锁（IX）。

你不用手动加它们，InnoDB 自动管理。它们的作用是——告诉你"有人正在锁某行"。

场景：你想给整张表加表锁，但是有很多行已经被别的事务锁住了。如果没有意向锁，你得遍历整张表检查每一行。有了意向锁，你只需要检查"表级别上有没有 IX 锁"——有，说明有人在锁行，你等。

意向锁的存在是为了行锁和表锁能共存。它是表级别的锁，由 lock manager 统一管理。你可以把它理解为"我打算在行上做点什么"的提前声明——加 IX 锁表示"我要改某行"，别人看到 IX 就知道不能给整张表加排他锁了。

锁的诊断：出了问题怎么看？

线上出现锁等待了，第一件事不是重启，是查"谁在等谁"。

但先说清楚开这个东西的成本：

第一层：performance_schema=ON。MySQL 5.7+ 默认就是 ON，不用你操心。但如果你在某个旧实例上发现它是 OFF，想打开的话——改 my.cnf 加 performance_schema=ON，然后重启实例，没法动态改。这一步本身已经有内存开销了（几百 MB 左右）。

第二层：data_lock_waits 能看到谁在等谁，但要查出持有锁的那条 SQL 是什么，还需要 events_statements_current consumer 也开着。这个 consumer 不开，data_lock_waits 里的 THREAD_ID 就关联不到具体的 SQL 文本，你只知道"某个线程在堵"，不知道"它在跑什么 SQL"。

先检查当前状态：

SHOW VARIABLES LIKE 'performance_schema';
SELECT * FROM performance_schema.setup_consumers 
WHERE NAME LIKE 'events_statements%';

如果 events_statements_current 是 NO，可以动态开启（不需要重启）：

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'events_statements_current';

注意这个 consumer 会在内存里缓存每个线程最近执行的 SQL，线程越多占的内存越大。如果连接数上千，这个开销就不小了。

首选方案（MySQL 8.0，PS 开启 + events_statements_current 开启）：

-- 谁被阻塞了？
SELECT * FROM performance_schema.data_lock_waits;

-- 从 THREAD_ID 找到对应的连接 ID（KILL 需要的是 PROCESSLIST_ID）
SELECT PROCESSLIST_ID 
FROM performance_schema.threads 
WHERE THREAD_ID = <BLOCKING_THREAD_ID>;

-- 看看阻塞者最近在跑什么 SQL
SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID = <BLOCKING_THREAD_ID>;

-- 拿到 PROCESSLIST_ID 后，低频任务直接 KILL
-- KILL <PROCESSLIST_ID>;
-- 高频业务先去查代码

兜底方案（PS 关掉了或者不想开 events_statements_current，也能用）：

-- 方案一：SHOW PROCESSLIST
-- 看 State 列：如果有 "Waiting for table level lock" 或 "Waiting for row lock"
SHOW PROCESSLIST;

-- 方案二：SHOW ENGINE INNODB STATUS
-- 重点看 "LATEST DETECTED DEADLOCK" 和 "TRANSACTIONS" 章节
-- 内容比较难读，但胜在永远能用
SHOW ENGINE INNODB STATUS\G

-- 方案三（MySQL 5.7）：information_schema 旧视图
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;

🤖 AI实战工具箱：让AI帮你造锁测试

锁的问题也是典型的"并发才出现、单机不暴露"。AI 擅长生成多 session 的竞争脚本。

复现行锁升级表锁

直接把下面这段粘给AI：

帮我写一组MySQL测试SQL，演示InnoDB中UPDATE操作在未命中索引时锁住所有行，效果等同于表锁。需要两个终端，终端A按无索引的age字段UPDATE，终端B按主键UPDATE另一行——验证B被阻塞。再建一个索引后重新测试，验证B不再被阻塞。用SLEEP控制时序，加注释说明。

复现死锁

帮我写一组MySQL测试SQL，演示两个事务互相等待的经典死锁场景。事务A锁住行1等待行2，事务B锁住行2等待行1。用SLEEP控制时序，最后用SHOW ENGINE INNODB STATUS查看死锁信息。

分析锁等待

我有一个锁等待超时的报警，下面是我的SHOW ENGINE INNODB STATUS输出，帮我分析是哪条SQL阻塞了哪条SQL：[粘贴 SHOW ENGINE INNODB STATUS 输出]

第三个场景才是最实用的——线上出问题的时候，把锁状态贴给AI，几秒钟就能定位到阻塞链。比肉眼盯着TRANSACTIONS章节看快多了。

面试加餐

Q: 行锁和表锁在性能上差多少？

行锁的维护成本比表锁高。InnoDB 对每一行加锁都要在内存里维护锁结构，如果一次 UPDATE 涉及几万行，锁结构的开销就很可观了。表锁虽然并发差，但锁的维护成本极低——就一个标记。所以MySQL的默认存储引擎从MyISAM换成InnoDB后，并发高了，但单条SQL的锁开销也大了。

Q: GAP LOCK 在 RC 级别下存在吗？

不存在。RC级别下Gap Lock被禁用，只保留Record Lock。这也是RC并发更好的原因之一——锁的范围更小。代价就是RC下不能防幻读。

Q: 什么情况下 UPDATE 会锁住比预期更多的行？

两种情况最典型：一是 WHERE 条件没走索引，全表扫描锁住所有行（前面讲过了）。二是 WHERE 条件走了索引但索引选择性差（比如 status 字段只有两个值），InnoDB 可能会锁住大量相邻的索引记录。这种情况可以用 EXPLAIN 看 rows 字段来预判。

注意这里有个隔离级别的差异：RR 下索引选择性差会导致 Next-Key Lock 锁住大量间隙，因为 RR 会锁扫描到的索引范围。而 RC 下 Gap Lock 是关闭的，即使索引选择性差也只锁命中的行，不会额外锁间隙。所以从锁范围来看，RC 在这个场景下更友好。

Q: 怎么看当前谁在等锁、谁在持有锁？

MySQL 5.7 用 SELECT * FROM information_schema.INNODB_LOCK_WAITS，MySQL 8.0 用 SELECT * FROM performance_schema.data_lock_waits。8.0 的视图信息更全，能看到每个锁对应的线程ID和SQL。

总结

🎯 面试考点

• 行锁 vs 全表锁定：行锁挂在索引上，没索引→全表扫描→锁住所有行，效果等同于表锁——这是线上最常见的锁故障
• InnoDB 三种行锁：Record Lock（锁行）、Gap Lock（锁间隙）、Next-Key Lock（两者组合）
• RR 下用 Next-Key Lock，RC 下只用 Record Lock —— Gap Lock 只在 RR 及以上级别生效
• 意向锁：表级别的标记，告诉别人"有人在锁行"，不是你手动加的
• 锁诊断：PS 开启时用 data_lock_waits（精准）；PS 关闭时用 SHOW ENGINE INNODB STATUS（通用）

今天就试一下：登录你的数据库跑一遍这三条SQL，看看有没有锁等待：

-- MySQL 8.0：performance_schema 视图（需 PS 开启）
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- MySQL 5.7：information_schema 旧视图
-- SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- SELECT * FROM information_schema.INNODB_LOCKS;

-- 通用：InnoDB 引擎状态
SHOW ENGINE INNODB STATUS;

不需要等到出问题再看——现在看一眼"干净"的状态，等出问题的时候你才知道什么是"异常"。

下期预告：InnoDB MVCC 机制详解 —— 快照读、当前读、Undo 链！

全本合集：《每天一个MySQL知识点，百日打怪升级》

有问题欢迎评论区交流，明天见！