DevCN开发者的创作与交流社区
RSS
菜单
全部文章快讯开发科技深度热点
技术文章/开发

第 5 章 锁机制面试精讲

站长
内容摘要

锁是 MySQL 并发控制的核心，也是线上事故的高发区。本篇用 17 道高频面试题串起 InnoDB 锁的全貌：行锁/表锁/间隙锁/Next-Key Lock、意向锁、MDL、自增锁、S/X 锁兼容矩阵、死锁检测与排查、两阶段锁协议、乐观锁与悲观锁选型。学完本篇，你能讲清"为什么 UPDATE 没走索引会锁全表""线上死锁怎么定位""业务里怎么用乐观锁防超卖"。前置知识：第 3、4 章事务与隔离级别；本系列示例基于 MySQL 8.0。

📖 本篇简介：锁是 MySQL 并发控制的核心，也是线上事故的高发区。本篇用 17 道高频面试题串起 InnoDB 锁的全貌：行锁/表锁/间隙锁/Next-Key Lock、意向锁、MDL、自增锁、S/X 锁兼容矩阵、死锁检测与排查、两阶段锁协议、乐观锁与悲观锁选型。学完本篇，你能讲清"为什么 UPDATE 没走索引会锁全表""线上死锁怎么定位""业务里怎么用乐观锁防超卖"。前置知识：第 3、4 章事务与隔离级别；本系列示例基于 MySQL 8.0。

目录

  • 1 锁机制高频考点地图
  • 题目 1MySQL 的锁按粒度和性质怎么分类
  • 题目 2InnoDB 行锁到底加在什么上面
  • 题目 3什么是意向锁 IXIS有什么用
  • 题目 4MDL 元数据锁是什么线上加表字段为什么会卡
  • 题目 5AUTOINC 自增锁是什么有几种模式
  • 题目 6记录锁间隙锁NextKey Lock 的区别
  • 题目 7S 锁和 X 锁的兼容矩阵是什么
  • 题目 8什么是死锁InnoDB 怎么检测和处理
  • 题目 9死锁和锁等待有什么区别
  • 题目 10怎么排查死锁SHOW ENGINE INNODB STATUS 怎么看
  • 题目 11怎么避免死锁
  • 题目 12UPDATE 没走索引为什么会锁全表
  • 题目 13两阶段锁协议是什么
  • 题目 14乐观锁和悲观锁的区别业务怎么选
  • 题目 15什么时候用表锁 LOCK TABLES
  • 题目 16锁等待超时怎么调 innodblockwaittimeout
  • 题目 17插入意向锁Insert Intention Lock是什么
  • 锁机制速查表
  • 常见坑与注意事项
  • 复习清单
  • 练习题

1 锁机制高频考点地图

面试里"锁"类问题的常见问法：

| 难度 | 典型问题 | 本篇对应 |

|---|---|---|

| 入门 | 锁有哪些分类？行锁表锁？ | 题目 1、2 |

| 基础 | 意向锁？MDL？自增锁？ | 题目 3、4、5 |

| 进阶 | 死锁怎么检测？两阶段锁？ | 题目 8、10、13 |

| 实战 | UPDATE 没走索引？乐观锁选型？ | 题目 12、14 |

一条主线记住：InnoDB 默认行级锁，锁在索引上；没走索引就退化成全表加锁；死锁靠等待图检测，主动回滚代价小的事务。

题目 1：MySQL 的锁按粒度和性质怎么分类？

考点

  • 粒度：全局锁/表锁/页锁/行锁；
  • 性质：S/X/意向锁；
  • InnoDB 主要行锁。

参考答案

按粒度分：

| 锁 | 粒度 | 谁用 |

|---|---|---|

| 全局锁 | 整个实例 | FTWRL，备份用 |

| 表锁 | 整张表 | MyISAM、LOCK TABLES、MDL |

| 页锁 | 一页（约几百行） | BDB，InnoDB 不用 |

| 行锁 | 一行/一行 + 间隙 | InnoDB 默认 |

按性质分：共享锁 S、排他锁 X、意向锁 IS/IX、插入意向锁、自增锁等。

  • InnoDB 的优势就是行锁粒度小、并发高；MyISAM 只有表锁，写并发差；
  • 面试常问"InnoDB 行锁比表锁好在哪"——好并发，但没走索引会退化成表级锁（题目 12）。
-- 全局锁：整库只读，备份用
FLUSH TABLES WITH READ LOCK;
-- 业务读写全停，备份完
UNLOCK TABLES;
-- 生产备份推荐 mysqldump --single-transaction，不阻塞业务

追问

  • 问：页锁为什么不流行？答：粒度介于表和行之间，并发不如行锁、管理复杂，基本被淘汰。
  • 问：MyISAM 为什么快？答：读不阻塞读，但写一来全堵，适合写少读多的报表场景，现代业务基本都换 InnoDB。

题目 2：InnoDB 行锁到底加在什么上面？

考点

  • 行锁加在索引记录上；
  • 没索引 = 全表扫描 = 全表加锁；
  • 聚簇索引 vs 二级索引。

参考答案

  • InnoDB 行锁是加在索引记录上的，不是加在"物理行"上；
  • UPDATE/DELETE 的 WHERE 条件用了哪个索引，就在那个索引上加锁；如果是二级索引，二级索引和聚簇索引都要加锁（先锁二级索引记录，再回表锁聚簇索引记录）；
  • WHERE 条件没走任何索引：扫描全表，给每一行都加锁 = 实际表锁，并发瞬间归零；
  • 这就是"上线前必须 EXPLAIN 看 type"的原因之一。
-- 没走索引 = 锁全表
CREATE TABLE t(id INT PRIMARY KEY, name VARCHAR(50), age INT);
UPDATE t SET age = 20 WHERE name = 'tom';   -- name 没索引 → 全表扫 → 每行加 X 锁
-- 正确：
ALTER TABLE t ADD KEY idx_name(name);
UPDATE t SET age = 20 WHERE name = 'tom';   -- 只锁 name='tom' 的行

追问

  • 问：走了二级索引，回表时聚簇索引也加锁吗？答：加，二级索引记录上 X 锁 + 回表后的聚簇索引记录上 X 锁。
  • 问：SELECT 普通查询加行锁吗？答：不加，快照读走 MVCC；只有 FOR UPDATE/UPDATE/DELETE 才加。

题目 3：什么是意向锁 IX/IS？有什么用？

考点

  • 意向锁是表级锁；
  • 表明"我在表里加了行锁"；
  • 加快表锁判定。

参考答案

  • 意向锁（Intention Lock）是表级锁，分 IS（意向共享）和 IX（意向排他）；
  • 作用：当你要给整张表加表锁时，不用逐行检查有没有行锁——只要看表上有没有 IX/IS 意向锁就知道；
  • 典型场景：
  • 事务要给某行加 X 锁，先在表上加 IX；
  • 另一个事务要 LOCK TABLES t WRITE 加表级 X 锁，一看表上有 IX，就知道"有人在行上写"，阻塞；
  • 意向锁之间互相兼容（IX 和 IX 不冲突），只和表级 S/X 锁冲突。
-- 意向锁自动加，不用手动写
BEGIN;
UPDATE t SET age=1 WHERE id=1;   -- 自动：表级 IX + 行级 X
-- 此时另一个会话 LOCK TABLES t WRITE 会被 IX 阻塞

追问

  • 问：IX 和 IX 冲突吗？答：不冲突，多个事务可以同时在不同行上加 X 锁。
  • 问：意向锁会阻塞 DML 吗？答：不会，DML 之间 IX 兼容；只阻塞表级锁操作。

题目 4：MDL 元数据锁是什么？线上加表字段为什么会卡？

考点

  • MDL 保护表结构；
  • DDL 要拿 MDL 写锁；
  • 长事务阻塞 DDL，DDL 反过来阻塞新查询。

参考答案

  • MDL（Metadata Lock）：访问一张表时自动加，保护表结构不被并发 DDL 改；
  • 读操作加 MDL 读锁，写操作加 MDL 读锁，DDL（ALTER/TABLE）加 MDL 写锁；
  • 线上经典事故：

1. 事务 A 长时间持有表 t 的 MDL 读锁（没提交）；

2. 事务 B 执行 ALTER TABLE t ADD COLUMN ...，要等 MDL 写锁，被 A 阻塞；

3. 之后所有新的 SELECT/UPDATE 都要拿 MDL 读锁，排在 B 后面——全堵死；

  • 这就是"线上 ALTER 加字段把库打挂"的经典根因。
-- 8.0 查看 MDL 锁等待（performance_schema）
SELECT * FROM performance_schema.metadata_locks
WHERE object_name = 't';
-- 处理：KILL 掉持有 MDL 的长事务，ALTER 才能继续
-- 8.0 支持 ALTER TABLE ... NOWAIT / WAIT N 避免无限等
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE, WAIT 10;

追问

  • 问：8.0 的 ONLINE DDL 不阻塞读写吗？答：普通 DML 不阻塞，但MDL 写锁在 DDL 开始和结束瞬间仍要短暂拿，长事务会让这个等待变长。
  • 问：怎么避免？答：DDL 前先查长事务；用 pt-online-schema-change / gh-ost 影子表方案。

题目 5：AUTO-INC 自增锁是什么？有几种模式？

考点

  • 自增列分配 ID 的锁；
  • innodb_autoinc_lock_mode；
  • 8.0 模式 2 性能好。

参考答案

  • 给 AUTO_INCREMENT 列分配 ID 时要加自增锁，避免并发插重复 ID；
  • innodb_autoinc_lock_mode 三种模式：

1. 0（传统）：语句级表锁，整条 INSERT 结束才释放，批量插入性能差；

2. 1（连续，5.7 默认）：简单 INSERT 用轻量 mutex，批量 INSERT 仍表锁到语句结束；

3. 2（交错，8.0 默认）：所有插入都用 mutex，不锁表，性能最好；

  • 模式 2 下自增 ID 可能出现"不连续、回跳"（并发插入交错分配），业务别依赖 ID 连续。
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';  -- 8.0 默认 2
-- 批量 INSERT ... SELECT 在模式 2 下，主从用 ROW binlog 才安全

追问

  • 问：自增 ID 会回跳吗？答：会，事务回滚后已分配的 ID 不复用；模式 2 下并发交错也会跳号。
  • 问：分布式 ID 怎么选？答：高并发分布式用雪花算法/号段模式，本地自增只在单库内用。

题目 6：记录锁、间隙锁、Next-Key Lock 的区别？

考点

  • Record Lock 锁单行；
  • GAP Lock 锁间隙；
  • Next-Key = 两者合一。

参考答案

| 锁类型 | 锁的对象 | 作用 |

|---|---|---|

| Record Lock | 单条索引记录 | 阻止别人改/删该行 |

| GAP Lock | 索引记录之间的开区间 | 阻止别人在间隙插入 |

| Next-Key Lock | 左开右闭区间 (prev, cur] | Record + GAP，防幻读 |

  • Record Lock：WHERE id=10 唯一索引命中，锁 id=10 这一行；
  • GAP Lock：WHERE id>10 AND id<20 查不到行时，锁 (10,20) 间隙，阻止插入；
  • Next-Key Lock：范围查询默认加，左开右闭；
  • RC 级别基本只有 Record Lock，RR 才用 Next-Key Lock 防幻读。
-- 索引记录：10, 20, 30
SELECT * FROM t WHERE id >= 20 FOR UPDATE;
-- 加的 Next-Key Lock：(10,20]、(20,30]、(30,+∞)
-- 插入 25、35 都会阻塞

追问

  • 问：唯一索引等值命中退化成什么？答：退化成 Record Lock（不加间隙）。
  • 问：唯一索引等值查不到呢？答：加 GAP Lock 在该值所在间隙。

题目 7：S 锁和 X 锁的兼容矩阵是什么？

考点

  • S/S 兼容；S/X、X/X 互斥；
  • 意向锁矩阵；
  • 锁等待的根源。

参考答案

行级锁兼容矩阵：

| 已持有 \ 请求 | S | X |

|---|---|---|

| S | ✅ 兼容 | ❌ 阻塞 |

| X | ❌ 阻塞 | ❌ 阻塞 |

  • S 锁：共享，读锁；多个事务可以同时读同一行；
  • X 锁：排他，写锁；拿到 X 后别人既不能读（加锁读）也不能写；
  • 意向锁兼容矩阵：IS/IX/IX/IS 互相兼容，只和表级 S/X 互斥。
-- S 锁演示：会话 A 加 S，会话 B 加 S 成功，加 X 阻塞
-- A：
BEGIN;
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;   -- S
-- B：
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;   -- ✅ 成功
SELECT * FROM t WHERE id=1 FOR UPDATE;            -- ❌ 阻塞（要 X）

追问

  • 问：普通 SELECT 加 S 锁吗？答：不加，快照读走 MVCC，无锁；只有 LOCK IN SHARE MODE / FOR SHARE 才加 S。
  • 问：UPDATE 加什么锁？答：X 锁。

题目 8：什么是死锁？InnoDB 怎么检测和处理？

考点

  • 循环等待；
  • wait-for graph；
  • 主动回滚 undo 量小的事务。

参考答案

  • 死锁：两个或多个事务互相持有对方需要的锁，循环等待，谁也动不了；
  • InnoDB 主动死锁检测（innodb_deadlock_detect=ON）：每次加锁等待时构造"等待图"，发现环就立即回滚其中一个事务（选 undo log 量小、代价低的），让另一个继续；
  • 与锁等待的区别：死锁是循环等待，立即检测到；锁等待是单向等待，靠超时解除。
-- 死锁经典时序
-- 会话 A：
BEGIN; UPDATE t SET b=1 WHERE id=1;   -- 锁 id=1
-- 会话 B：
BEGIN; UPDATE t SET a=1 WHERE id=2;   -- 锁 id=2
-- 会话 A：
UPDATE t SET b=2 WHERE id=2;          -- 等 B 的 id=2
-- 会话 B：
UPDATE t SET a=2 WHERE id=1;          -- 等 A 的 id=1 → 死锁，A/B 之一被回滚

追问

  • 问：死锁检测有性能问题吗？答：高并发下每次加锁都要扫描等待图，开销 O(n)；超热点场景可关检测 + 调小超时，但要谨慎。
  • 问：死锁报错长什么样？答：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。

题目 9：死锁和锁等待有什么区别？

考点

  • 循环 vs 单向；
  • 主动检测 vs 超时；
  • 报错码不同。

参考答案

| 对比 | 死锁 | 锁等待 |

|---|---|---|

| 等待方向 | 循环互相等 | 单向等 |

| 解除方式 | InnoDB 主动检测回滚一方 | 等对方提交或 innodb_lock_wait_timeout 超时 |

| 报错码 | 1213 (Deadlock found) | 1205 (Lock wait timeout exceeded) |

| 响应速度 | 立即 | 最多等 50 秒（默认） |

  • 死锁是"事故"，要复盘代码；锁等待是"排队"，超时后业务可重试。

追问

  • 问：锁等待超时后事务结束了吗？答：报错后当前语句回滚，但事务没结束，需要显式 ROLLBACK。
  • 问：1205 和 1213 业务上怎么处理？答：都要重试，但 1213（死锁）要排查为什么会循环等。

题目 10：怎么排查死锁？SHOW ENGINE INNODB STATUS 怎么看？

考点

  • LATEST DETECTED DEADLOCK 段；
  • 事务持有的锁和等待的锁；
  • 8.0 用 performance_schema。

参考答案

SHOW ENGINE INNODB STATUS\G
-- 重点看 LATEST DETECTED DEADLOCK 段：
-- 1. 两个事务各自执行的 SQL
-- 2. 各自持有的锁（HOLDS THE LOCK）
-- 3. 各自等待的锁（WAITING FOR THIS LOCK）
-- 4. 哪个事务被回滚（VICTIM）

-- 8.0 也可以查历史死锁日志
SELECT * FROM performance_schema.data_locks;
-- 开启死锁日志打印到 error log：
SET GLOBAL innodb_print_all_deadlocks = ON;
  • 排查思路：看两个事务的 SQL，找"加锁顺序不一致"的地方——把加锁顺序统一即可消除。

追问

  • 问：innodb_print_all_deadlocks 开着有副作用吗？答：死锁频繁时 error log 会暴涨，高并发场景按需开。
  • 问：pt-deadlock-logger 是干嘛的？答：Percona 工具，定期抓取死锁日志存表，方便事后分析。

题目 11：怎么避免死锁？

考点

  • 统一加锁顺序；
  • 缩短事务；
  • 降低隔离级别；
  • 索引走到位。

参考答案

  • 常见手段：

1. 统一加锁顺序：多个事务按相同顺序访问行（比如都按 id 升序锁），避免循环等待；

2. 缩短事务：事务越短，持有锁时间越短，死锁概率越低；

3. 降低隔离级别：RC 没有间隙锁，死锁大幅减少；

4. 走索引：UPDATE/DELETE 走索引，锁范围小；

5. 大事务拆小：批量删除分批做，每批提交一次；

6. 业务重试：捕获 1213 错误，幂等重试。

-- 反例：两个事务加锁顺序相反 → 死锁
-- 正例：都按 id 升序锁
BEGIN;
SELECT * FROM t WHERE id IN (1,2,3) ORDER BY id FOR UPDATE;  -- 顺序固定
-- 业务逻辑
COMMIT;

追问

  • 问：批量 UPDATE 怎么写不死锁？答：按主键范围分批 + 排序，每批小事务提交。
  • 问：唯一索引冲突会导致死锁吗？答：会，两个事务同时插同一唯一键，互相等对方的间隙锁，典型死锁场景。

题目 12：UPDATE 没走索引为什么会锁全表？

考点

  • 全表扫描 = 每行加锁；
  • RR 下连间隙都锁；
  • 线上事故高发。

参考答案

  • InnoDB 行锁加在索引记录上；UPDATE 的 WHERE 没走索引时，优化器全表扫描，对扫描到的每一行都加 X 锁；
  • RR 级别下还会加 Next-Key Lock，等于把表"锁住"，其他事务的插入、更新全堵；
  • 这是线上"一个 UPDATE 把库打挂"的最常见原因。
-- 事故现场
UPDATE order SET status = 'PAID' WHERE out_trade_no = 'xxx';  -- out_trade_no 没索引
-- 全表扫描 + 每行 X 锁 → 业务所有写被阻塞
-- 修复：
ALTER TABLE order ADD KEY idx_out_trade_no(out_trade_no);
UPDATE order SET status = 'PAID' WHERE out_trade_no = 'xxx';  -- 只锁一行

追问

  • 问：SELECT 没走索引也锁表吗？答：普通 SELECT 是快照读不加锁；UPDATE/DELETE/SELECT FOR UPDATE 才锁。
  • 问：怎么提前发现？答：上线前 EXPLAIN，type 不是 range/ref/const 的 UPDATE 一律打回。

题目 13：两阶段锁协议是什么？

考点

  • 扩展阶段加锁；
  • 收缩阶段只解锁；
  • InnoDB 基本遵循。

参考答案

  • 两阶段锁协议（2PL）：事务分两阶段：

1. 扩展阶段：可以申请任何锁，不能释放；

2. 收缩阶段：可以释放锁，不能再申请新锁；

  • InnoDB 虽然不是严格 2PL（死锁检测会提前回滚），但锁持有到事务结束才释放这一点符合 2PL；
  • 意义：保证可串行化调度；代价是锁持有时间长，并发低。
-- 锁在 COMMIT/ROLLBACK 才释放
BEGIN;
UPDATE t SET a=1 WHERE id=1;   -- 加 X 锁
UPDATE t SET a=2 WHERE id=2;   -- 再加 X 锁
-- 中间 id=1 的锁还没释放
COMMIT;                         -- 所有锁一起释放

追问

  • 问：能不能语句结束就放锁？答：InnoDB 不行，必须到事务结束；这是长事务危害大的根因。
  • 问：乐观锁违背 2PL 吗？答：乐观锁不加 DB 锁，靠版本号校验，属于"无锁"方案，不在 2PL 框架内。

题目 14：乐观锁和悲观锁的区别？业务怎么选？

考点

  • 悲观锁 = 数据库行锁；
  • 乐观锁 = 版本号/CAS；
  • 冲突频率决定选型。

参考答案

| 对比 | 悲观锁 | 乐观锁 |

|---|---|---|

| 实现 | SELECT FOR UPDATE | version 字段 + CAS UPDATE |

| 冲突时 | 阻塞等待 | 更新失败重试 |

| 适合 | 冲突频繁、短事务 | 冲突少、高并发读 |

| 性能 | 低（排队） | 高（无锁） |

  • 悲观锁：先加锁再改，SELECT ... FOR UPDATE；
  • 乐观锁：读时记下版本号，更新时 UPDATE ... SET version=version+1 WHERE id=? AND version=?，影响行数为 0 说明冲突，重试。
-- 乐观锁典型写法
ALTER TABLE account ADD COLUMN version INT NOT NULL DEFAULT 0;
-- 读
SELECT balance, version FROM account WHERE id=1;   -- balance=100, version=3
-- 改
UPDATE account SET balance=balance-10, version=version+1
WHERE id=1 AND version=3;   -- affected_rows=1 成功；=0 冲突，重试

追问

  • 问：库存扣减用悲观锁还是乐观锁？答：热点商品秒杀用乐观锁 + 失败重试；低频订单用悲观锁更简单。
  • 问：乐观锁 ABA 问题？答：数据库 version 单调递增没有 ABA；Java 的 AtomicStampedReference 才要管 ABA。

题目 15：什么时候用表锁 LOCK TABLES？

考点

  • LOCK TABLES 显式表锁；
  • 与 InnoDB 行锁对比；
  • 特殊场景。

参考答案

  • LOCK TABLES t READ/WRITE 是 Server 层的显式表锁，会绕过 InnoDB 行锁直接锁整表；
  • 生产很少用，典型场景：

1. MyISAM 表的写操作（本来就表锁）；

2. 批量数据迁移，要独占一张表；

3. 某些元数据一致性操作；

  • InnoDB 业务表一般不用 LOCK TABLES，行锁更细。
-- 表锁演示
LOCK TABLES t WRITE;   -- 独占，其他会话读写都阻塞
-- 操作
UNLOCK TABLES;

追问

  • 问：FLUSH TABLES WITH READ LOCK 和 LOCK TABLES READ 区别？答：FTWRL 锁所有库所有表（备份用）；LOCK TABLES 锁指定表。
  • 问：5.6 之前 LOCK TABLES 有什么坑？答：它会隐式提交当前事务，慎用。

题目 16：锁等待超时怎么调 innodb_lock_wait_timeout？

考点

  • 默认 50 秒；
  • 生产建议 5~10 秒；
  • 与死锁检测分工。

参考答案

  • innodb_lock_wait_timeout：行锁等待超时，默认 50 秒；
  • 生产建议调到 5~10 秒，避免连接被长时间挂住拖垮连接池；
  • 死锁检测（innodb_deadlock_detect=ON）负责循环等待的即时检测；这个超时负责单向等待；
  • 超时后报错 1205，业务要捕获并重试。
SET GLOBAL innodb_lock_wait_timeout = 5;   -- 全局
SET SESSION innodb_lock_wait_timeout = 5;  -- 当前会话

追问

  • 问：调太小会不会误伤正常业务？答：长 SQL 单条执行时间本身就长，但锁等待是"等别人提交"，不是自己执行慢，5 秒足够；超过 5 秒说明对方事务太长，该治理。
  • 问：死锁检测关掉后靠什么兜底？答：靠这个超时，超时自动报错，业务重试。

题目 17：插入意向锁（Insert Intention Lock）是什么？

考点

  • INSERT 时在间隙上加的意向锁；
  • 与 GAP 锁的关系；
  • 并发插入阻塞。

参考答案

  • 插入意向锁是一种特殊的 GAP 锁：INSERT 时在目标位置的间隙上加，表明"我打算在这个间隙插入"；
  • 插入意向锁之间互相兼容（多个事务可以同时在同一间隙准备插入不同行）；
  • 但如果间隙被别的事务加了普通 GAP 锁，插入意向锁要等待——这就是 RR 下并发插入互相阻塞的原因。
-- 间隙锁阻塞插入意向锁
-- 会话 A：
SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE;  -- 加 (10,20) Next-Key
-- 会话 B：
INSERT INTO t VALUES(15);   -- 插入意向锁要等 A 的 GAP，阻塞

追问

  • 问：RC 下插入意向锁还阻塞吗？答：RC 没有普通 GAP 锁，所以插入意向锁不阻塞，并发高。
  • 问：唯一索引冲突为什么死锁？答：两个事务都想插入同一唯一键，互相等对方的插入意向锁/间隙锁。

锁机制速查表

| 锁 | 粒度 | 谁加 | 作用 |

|---|---|---|---|

| 记录锁 X/S | 单行索引记录 | UPDATE/DELETE/FOR UPDATE | 阻止同行并发写 |

| GAP 锁 | 索引间隙 | RR 范围查询 | 阻止间隙插入 |

| Next-Key Lock | 左开右闭区间 | RR 范围当前读 | 防幻读 |

| 意向锁 IX/IS | 表级 | 自动 | 加速表锁判定 |

| MDL | 表元数据 | 自动 | 防 DDL/DML 并发改结构 |

| AUTO-INC 锁 | 自增计数器 | INSERT 时 | 防重复自增 ID |

| 插入意向锁 | 间隙 | INSERT 时 | 标记插入意图 |

| 报错码 | 含义 | 处理 |

|---|---|---|

| 1213 | 死锁 | 重试 + 复盘加锁顺序 |

| 1205 | 锁等待超时 | 重试 + 查长事务 |

常见坑与注意事项

1. UPDATE 没 WHERE 或没走索引：全表加 X 锁，线上事故之王。

2. 以为加索引就万事大吉：WHERE 里函数/隐式类型转换会让索引失效，照样全表锁。

3. 事务里加 RPC/HTTP：锁持有时间随外部接口拉长，连锁死。

4. DDL 不查长事务：MDL 排队把业务打挂，8.0 用 LOCK=NONE, WAIT N。

5. RR 下大范围 SELECT FOR UPDATE：Next-Key 锁一大片，批量任务改 RC 或分批。

6. 乐观锁忘了 WHERE version=?：退化成无条件 UPDATE，并发照样超卖。

7. 以为死锁检测开着就不用管：高热点表死锁检测本身就是瓶颈，要拆业务。

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 锁分类 | 粒度 + 性质两个维度 | 漏了 MDL/意向锁 |

| 行锁加哪 | 索引记录上 | 说"加在物理行" |

| 意向锁 | 表级，加速表锁判定 | 以为它锁行 |

| MDL | DDL 排队堵业务 | 忘了长事务是根因 |

| Next-Key | Record+GAP 左开右闭 | 区间方向说反 |

| 死锁检测 | wait-for graph，回滚小事务 | 答成"等超时" |

| 死锁 vs 锁等待 | 循环 vs 单向，1213 vs 1205 | 报错码混淆 |

| UPDATE 没索引 | 全表加锁 | 以为 SELECT 也锁 |

| 两阶段锁 | 锁到事务结束才放 | 以为语句结束就放 |

| 乐观锁 | version + CAS | 忘了重试 |

练习题

练 1：会话 A 执行 UPDATE t SET name='x' WHERE phone='138xxxx'，phone 列没有索引。RR 级别下，会话 B 同时 INSERT INTO t ... 会怎样？

<details>

<summary>参考答案</summary>

A 全表扫描，对每一行加 X 锁 + Next-Key Lock（RR），等于锁全表；B 的 INSERT 要拿插入意向锁，被间隙锁阻塞，直到 A 提交。这就是"没索引的 UPDATE = 表锁"。修复：给 phone 加索引。

</details>

练 2：业务要求"扣库存时不能超卖"，表 sku(sku_id, stock, version)。写出乐观锁扣减 SQL，并说明 affected_rows=0 怎么办。

<details>

<summary>参考答案</summary>

-- 先读 stock 和 version
SELECT stock, version FROM sku WHERE sku_id=100;
-- 扣减，CAS
UPDATE sku SET stock=stock-1, version=version+1
WHERE sku_id=100 AND version=:version;
-- affected_rows=1：成功
-- affected_rows=0：说明别人改过，重读 version 再重试（最多 3 次），仍失败则提示系统繁忙

</details>

练 3：线上 ALTER TABLE t ADD COLUMN c INT 一直卡住，之后所有 SELECT 也卡住。最可能的原因和处理步骤？

<details>

<summary>参考答案</summary>

根因：表 t 上有个长事务持有 MDL 读锁，ALTER 要拿 MDL 写锁排在后面；新的 SELECT 也要 MDL 读锁，排在 ALTER 后面，全堵。处理：

1. SELECT * FROM information_schema.INNODB_TRX 找长事务；

2. KILL 长事务连接；

3. ALTER 拿到 MDL 写锁继续；

4. 事后排查为什么会有长事务，DDL 改用 LOCK=NONE, WAIT N 或 gh-ost。

</details>

下一篇预告：第 6 章 MVCC原理面试精讲

— 全文完 —回到顶部 ↑
下载推广海报

文章推广海报

《第 5 章 锁机制面试精讲》完整推广海报
DISCUSSION

文章回复

0 条公开回复
未登录回复需要审核后公开
还没有回复，欢迎参与讨论。