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

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

---

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

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

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

---

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

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

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

> `Lock wait timeout exceeded; try restarting transaction`

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

---

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

| 锁类型 | 锁的范围 | 并发度 | 典型场景 |
|--------|---------|-------|---------|
| **行锁** | 只锁住命中的那几行 | 高（其他人可以操作别的行） | InnoDB 默认，有索引时 |
| **表锁** | 锁住整张表 | 极低（别人啥都干不了） | 手动 `LOCK TABLES`、MyISAM 表、DDL 元数据锁 |

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

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

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

---

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

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

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

```sql
-- 假设表结构：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 在行级别上有三种锁模式：

| 锁类型 | 别名 | 作用 |
|--------|------|------|
| **Record Lock** | 行锁 | 只锁住索引上的**某一条记录** |
| **Gap Lock** | 间隙锁 | 锁住索引记录之间的**空隙**，防止插入 |
| **Next-Key Lock** | 临键锁 | **Record Lock + Gap Lock** 的组合 |

**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"。

先检查当前状态：

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

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

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

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

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

```sql
-- 谁被阻塞了？
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，也能用）：

```sql
-- 方案一：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，看看有没有锁等待：

```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知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  


---

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


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

【MySQL百日打怪升级第20天】行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表？

【第20天】MySQL锁机制精讲！深入解析InnoDB行锁与表锁的本质区别：行锁依赖索引，无索引则全表扫描→实际锁全表；详解Record/Gap/Next-Key三种行锁、意向锁作用及锁诊断实战（PS视图+INNODB STATUS）。避坑指南：UPDATE/DELETE务必走索引！

数据库

后端

开发工具

深入了解MySQL InnoDB锁机制：行锁与表锁的区别。InnoDB默认使用行锁，但若无索引会全表扫描导致锁表，影响并发性能。掌握索引优化、行锁类型（记录锁、间隙锁、临键锁）及锁诊断方法，解决生产环境锁等待问题。适合DBA及开发者提升数据库性能与排查能力。

数据结构

短信

MySQL

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第20天】行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表？-腾讯云开发者社区-腾讯云

【MySQL百日打怪升级第20天】行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表？

【MySQL百日打怪升级第20天】行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表？

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐