事务与锁

事务与锁是 MySQL 面试与生产事故的共同主角：一面考察概念（隔离级别、gap lock、死锁），一面制造故障（锁等待、MDL 雪崩、1205/1213 报错）。本章按"ACID 怎么实现 → 隔离级别 → 锁全景 → 表级锁/MDL → 行锁细则 → 死锁 → 实战案例 → RC 与 RR 差异"的顺序展开，锁冲突一律用"会话 1 / 会话 2"双栏时序演示。版本基线 MySQL 8.4 LTS，锁行为默认按 InnoDB + RR（可重复读）讨论，RC 差异见第 10 节。

1. ACID：每个字母背后是谁在干活

先把四个字母和具体机制对上号，后面每一节都是在展开其中一格：

注意 “一致性"被 ACID 排最后是有意的：它是目的不是机制。数据库只能保证不违反约束、崩溃可恢复；“转账前后两边总额不变"这种业务一致性，必须靠事务边界写对来保证——这正是本章反复演示"会话 1 / 会话 2"的原因。

一句话记住分工：undo 管"退得回去”，redo 管” Commit 了就不丢"，锁和 MVCC 管"多人同时改不打架"。

2. 事务语法与隐式提交的坑

2.1 基本语法

START TRANSACTION;            -- 或 BEGIN; 两者等价
-- ... 若干 DML ...
SAVEPOINT sp1;                -- 设存点
DELETE FROM orders WHERE id = 99;
ROLLBACK TO SAVEPOINT sp1;    -- 只撤销 sp1 之后的操作，事务仍然活着
COMMIT;                       -- 或 ROLLBACK;（回滚整个事务并丢弃所有存点）

START TRANSACTION 本身会先隐式提交上一个未提交的事务；ROLLBACK TO SAVEPOINT 不会释放存点之前的锁，只有 COMMIT / 整体 ROLLBACK 才统一释放。

2.2 autocommit

SELECT @@autocommit;          -- 默认 1
SET autocommit = 0;           -- 仅影响当前会话

autocommit=1 时，每条 DML 单独是一个事务（执行完立即提交）。START TRANSACTION 会临时关掉当前会话的 autocommit，COMMIT 后恢复。

注意 SET autocommit = 0 是经典生产隐患：应用没显式 COMMIT，连接回到连接池，之后每条普通 SELECT 都会悄悄开一个 RR 事务——持有一个永不推进的 ReadView，purge 被钉死、锁一直被拿。排查见 06-MVCC.md 的长事务一节。

2.3 哪些语句会隐式提交

SQL 标准里事务边界只由 BEGIN/COMMIT 控制，MySQL 却在事务中间塞了一批"偷偷提交"的语句：

• DDL 全家：CREATE / ALTER / DROP / RENAME TABLE、CREATE / DROP INDEX 等；
• 部分管理语句：如 SET AUTOCOMMIT = 1、一些复制控制语句（START/STOP SLAVE 类）；
• LOAD DATA INFILE（向有外键的表加载时）。

后果最重的是一条心智规则：事务里跑 DDL，等于提前提交。

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 已执行
ALTER TABLE account ADD COLUMN remark VARCHAR(64);        -- 这行执行时，上面的 UPDATE 已被隐式 COMMIT
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 出错
ROLLBACK;   -- 回滚不了第一句——它早就提交了

部分 DDL 例外（如 ANALYZE TABLE、某些分区维护操作不隐式提交），具体清单建议按版本实测确认；工程上不要依赖例外，统一把 DDL 与 DML 分开管理。改表流程本身见 09-Online-DDL与大表变更.md。

3. 隔离级别：四级别 × 四异常矩阵

先定义四种并发异常，按严重程度从低到高：

SQL 标准允许什么（P=允许，N=禁止）与 InnoDB 实际表现：

*丢失更新严格说 SQL 标准没有定义（标准只管读异常），各实现行为不同：RR/RC 下两条 UPDATE 同时做 balance = balance + 100 不会丢失更新——InnoDB 给行加 X 锁串行化了"读-改-写"；但"先 SELECT 再在应用里算好再 UPDATE"的写法任何级别都可能丢失更新，必须显式加锁或用 WHERE stock >= n 守卫（第 9 节演示）。

各级别的"读行为"一句话：

• RU：快照读读到的就是别的事务还没提交的中间态；
• RC：每条 SELECT 生成新快照，行内容可能是新版本（可重复读不保）；
• RR：事务第一条 SELECT 生成快照并全程复用，之后 SELECT 看到的世界冻结在快照瞬间；
• Serializable：不生成快照读，SELECT 变排队加锁读，写读彻底互斥。

3.1 为什么 InnoDB 的 RR 敢宣传防幻读

SQL 标准把 RR 定义为只保"同一行读两次不变"，幻读（新行冒出来）在 RR 是被允许的。InnoDB 在 RR 下做了双保险：

• 快照读（普通 SELECT）：靠 MVCC ReadView，新提交插入的行版本不对我可见；
• 当前读（UPDATE / DELETE / SELECT … FOR UPDATE）：靠 next-key lock（记录 + 前向间隙一起锁），别的事务想往这个范围 INSERT 会被 gap lock 挡住。

两套机制拼起来，把标准里允许的幻读基本堵死。仍有理论例外——本事务的 UPDATE 会让别的事务新提交行"自我可见"——这是 06-MVCC.md 第 6 节"RR 防幻读真相"的核心。面试答到"RR 怎么防幻读"，必须把这三分支（快照读靠 MVCC / 当前读靠 next-key / 例外场景）讲全。

4. 锁全景：先画地图再进城

按粒度分三层，InnoDB 的行锁只是右下角那一块：

MySQL 锁
├── 服务层（Server）
│   ├── MDL 元数据锁：表结构读写互斥（5.2）
│   ├── LOCK TABLES 表读/写锁：显式表锁（5.3）
│   └── FTWRL 全局读锁：备份场景（5.3）
├── 引擎层（InnoDB）
│   ├── 表级：意向锁 IS / IX（5.1）、AUTO-INC 锁（自增列分配，03/04 展开）
│   └── 行级（加在索引上）
│       ├── record lock：单条记录 S / X
│       ├── gap lock：间隙，只挡插入
│       ├── next-key lock：record + 前面的 gap
│       └── insertion intention lock：插入前的意向，被 gap 挡
└── 其他存储引擎：MyISAM / MEMORY 只有表锁，无行锁

按模式记符号：S（共享）、X（排他）、IS/IX（表级意向，分别预告行级 S/X）、AUTO-INC（自增分配）。兼容矩阵只需记两条：S 与 S 兼容，S 与 X、X 与 X 一律互斥；表级意向锁与行级锁的关系是"IS/IX 之间永不冲突，只与表级 S/X 冲突"（5.1 解释）。

按范围记三兄弟（RR 下行锁的三种形态）：

5. 表级锁与 MDL

5.1 意向锁（IS / IX）为什么存在

意向锁是表级"占位灯"：某事务要对表里的行加 S/X 锁前，先在表上登记 IS / IX。它存在的理由只有一个——让"要拿表级 S/X 锁的操作"不必逐行扫描全表。

-- 时间   会话 1                                会话 2
T1      START TRANSACTION;
T2      UPDATE t SET c=1 WHERE id=5;
        -- 表 t 上加 IX，行 id=5 上加 X record lock
T3                                              LOCK TABLES t READ;
        -- 会话 2 要申请表级 S 锁
        -- 检查表 t：IX 与 S 冲突 → 阻塞（而不是扫全表逐行确认没有行锁）

IX 与 S、IS 与 X、X 与两者都冲突；IS/IX 彼此兼容（两个事务都可以预告自己的行级锁）；表级 S 与行级 X 冲突，表级 X 与一切冲突。

5.2 MDL 元数据锁：DML 与 DDL 互斥的雪崩

MDL 规则三条：

1. 任何访问表的 SQL 开始自动拿 MDL 读锁（SELECT/INSERT/UPDATE/DELETE），DDL 拿 MDL 写锁（ALTER/DROP/RENAME）；
2. 读-读兼容，读-写互斥，写-写互斥；
3. MDL 写锁请求会插队阻塞后续 MDL 读锁——写请求排在等待队列里时，后来的读请求必须排在它后面，防止 DDL 被无穷无尽的查询饿死。

第 3 条配合事务边界，就是经典事故配方：

-- 时间   会话 1（业务事务）                 会话 2（改表工单）          会话 3..N（普通查询）
T1      START TRANSACTION;
T2      SELECT * FROM t WHERE id=1;
        -- 持有 t 的 MDL 读锁，且事务不结束不释放
T3                                              ALTER TABLE t ADD COLUMN
                                                flag TINYINT;
        -- 申请 MDL 写锁 → 阻塞
        -- SHOW PROCESSLIST 里 State =
        -- "Waiting for table metadata lock"
T4                                                                      SELECT ... FROM t;
                                                                        -- 想拿 MDL 读锁，
                                                                        -- 但写请求已入队 → 排在写锁后面 → 也阻塞
T5                                                                      查询洪水般堆积，
                                                                        -- 连接池打满，整库瘫痪

一个没提交的事务 + 一个 ALTER = 全库雪崩。止血顺序：

1. SHOW PROCESSLIST 找到 State 为 Waiting for table metadata lock 的会话，以及持有 MDL 的源头（多半是会话 1 这种开了事务没提交的，或跑了很久的查询）；
2. 优先 KILL 会话 1 的 connection id（源头），而不是盲目杀排队的查询；
3. 给 DDL 会话设置超时，不要让 ALTER 无限等：SET lock_wait_timeout = 60;（会话级），ALTER 等不到写锁会在 60 秒后自动放弃退出，雪崩解除；
4. 用 sys.schema_table_lock_waits 看谁持有、谁在等；细粒度诊断可查 performance_schema.metadata_locks（需先开启 wait/lock/metadata/sql/mdl instrument，默认可能是关的，待实测）。

事务未提交持有 MDL、连接池里的"空闲但开着事务"的连接、大查询跑在从库上等，都是事故源头。改表的安全流程（Online DDL、gh-ost/pt-osc）见 09-Online-DDL与大表变更.md。

注意 MDL 的持有者是事务而不是语句，这是它和行锁的本质区别里最容易踩的一条。只要事务还活着，哪怕早先那条 SELECT 一秒就执行完了，MDL 读锁会陪事务到 COMMIT/ROLLBACK。

5.3 FTWRL 与 LOCK TABLES

• FLUSH TABLES WITH READ LOCK：全库只读（阻塞一切 DML 和 DDL），逻辑备份取一致性快照用；8.0.21+ 有更轻的 BACKUP LOCK。只在 11-备份恢复与性能调优.md 展开。
• LOCK TABLES t READ / WRITE：服务层显式表锁。InnoDB 场景不建议用——行锁 + MVCC 本来能提供的并发它全给降级成表级互斥；而且 LOCK TABLES 与 InnoDB 内部锁体系交织，混用会制造反直觉死锁。它主要是给 MyISAM 这类引擎兜底一致性用的。

6. 行锁细则（InnoDB，按 RR 讲）

6.1 加锁的唯一对象是索引

InnoDB 行锁只加在索引记录上，不加在"行"这个抽象概念上。理解这一点，一半的锁怪事就有了答案：

• 走主键 → 锁聚簇索引记录；
• 走二级索引 → 二级索引上加 next-key，同时回表路径上的聚簇索引记录加 record lock；
• 没索引可用 → 全表扫描，聚簇索引所有记录 + 所有间隙 + supremum 全部加锁，行为等同锁表。

-- 时间   会话 1                                   会话 2
T1      START TRANSACTION;
T2      UPDATE t2 SET age = 99
        WHERE remark = 'x';       -- remark 列没有索引
        -- 全表扫描：聚簇索引每条记录 next-key X 锁
T3                                  START TRANSACTION;
T4                                  UPDATE t2 SET age = 8 WHERE id = 7;
        -- 只想锁 id=7 这一行，但 id=7 的锁被会话 1 拿着 → 阻塞
T5      ...  会话 1 迟迟不提交 → 会话 2 等满 50s 报 1205

所以加锁范围的问题，先问"这个 WHERE 走哪个索引"，再谈别的。给 remark 建一个索引，上面的例子立刻收敛到两三条记录。

6.2 Record Lock

锁单条索引记录。S 共享、X 排他。两个事务可以同时持 S 不能同时持 X。

SELECT * FROM t WHERE id = 5 FOR SHARE;      -- S record lock（RR 下 unique 等值：仅 record，无 gap）
UPDATE t SET c = 1 WHERE id = 5;             -- X record lock

6.3 Gap Lock

锁开区间，只挡插入，不挡对已有记录的读写。gap lock 之间互相兼容（两个事务可以持有同一个 gap 的 gap lock）——这句话是第 8.2 节 gap 死锁的全部伏笔。gap lock 在 X 模式和 S 模式下都可能出现，本质都只是"挡这个间隙里的插入"。

6.4 唯一索引等值：命中只锁 record，未命中只锁 gap

-- 表 t：主键 id，已有记录 5, 10, 15, 20
-- 会话 1：
SELECT * FROM t WHERE id = 10 FOR SHARE;   -- 命中 → 仅 id=10 一条 record lock，没有 gap
-- 会话 2：
SELECT * FROM t WHERE id = 8 FOR SHARE;    -- 未命中 → gap lock (5, 10)，锁住"这里不能插"

命中为什么不需要 gap？因为唯一索引保证"不会再出现第二个 id=10"，幻读威胁天然不存在，只需要锁这一条记录本身。加锁范围最小化的关键手段就是唯一索引 + 等值条件。

6.5 非唯一索引等值：命中行 next-key + 后继 gap

这是面试标准题型，用 (5, 10, 15, 20) 走一遍：

CREATE TABLE t (
  id  INT PRIMARY KEY,
  age INT,
  KEY idx_age (age)
);
INSERT INTO t VALUES (1,5),(2,10),(3,15),(4,20);   -- idx_age 叶子：5,10,15,20,supremum

-- 时间   会话 1                                    会话 2
T1      START TRANSACTION;
T2      SELECT * FROM t WHERE age = 10 FOR SHARE;
        -- idx_age 上加两把：
        --   next-key (5, 10]
        --   gap (10, 15)
        -- 聚簇索引上加 id=2 的 record S 锁
T3                                                  INSERT INTO t VALUES (5, 12);
        -- 12 落在 (10,15) 间隙 → 插入意向锁被会话 1 的 gap lock 挡 → 阻塞
T4                                                  UPDATE t SET age = 11 WHERE id = 2;
        -- id=2 上的 X 锁与会话 1 的 record S 冲突 → 阻塞
        -- （11 同样落在被锁间隙里，即使 id=2 不冲突也进不去）
T5      COMMIT;                                   -- 会话 2 解除阻塞，各自继续

为什么非唯一等值要 next-key + 后继 gap？因为 age=10 可能有多条命中行，且 (10,15) 之间再插一个 age=10 就是新命中行——本事务内再查一次 age=10 行数就变了，这是幻读。RR 的"可重复 + 防幻"承诺在这里要求把"10 号记录本身（next-key 右端）+ 它右边的间隙"都看住。规则：命中行的 next-key，加到第一条不匹配行（本例第一条不匹配是 15，所以锁到 (10,15)）。

6.6 范围查询：扫到哪锁到哪，边界再补一个 gap

-- 表数据同 6.5，主键 id = 5,10,15,20
-- 会话 1：
SELECT * FROM t WHERE id BETWEEN 10 AND 15 FOR UPDATE;
-- 聚簇索引：next-key (5,10]，next-key (10,15]，gap (15,20)
-- 会话 2：
--   UPDATE t SET c=1 WHERE id=10;   → 阻塞（被 next-key 命中）
--   INSERT INTO t VALUES (12, ...); → 阻塞（落进 (10,15] 的 gap 部分）

范围扫描的加锁原则：沿索引右向扫，扫过的每条记录加 next-key，第一条越界记录再补一个 gap（因为它前面的间隙可能藏新行）。边界闭开要小心：BETWEEN 闭区间，< 15 开区间时 15 只被"挡住 gap"不锁记录。

6.7 插入意向锁：插入前"打招呼"的锁

事务 INSERT 在真正放入记录前，要先在目标间隙申请一个 insert intention lock——它唯一的天敌就是 gap lock：

-- 时间   会话 1                                会话 2
T1      START TRANSACTION;
T2      SELECT * FROM t WHERE id = 8
        FOR UPDATE;                     -- 未命中 → gap (5,10)
T3                                        START TRANSACTION;
T4                                        INSERT INTO t VALUES (7, 'a');
        -- 7 落在 (5,10)：插入意向锁与会话 1 的 gap lock 冲突 → 阻塞
T5      COMMIT;                         -- 会话 2 插入成功

insert intention 之间互相兼容（同间隙多个并发插入并不互斥，只要没人锁这个 gap）。

6.8 DELETE 之后锁并不会松：删除标记与版本链的连锁

DELETE 只给记录打"删除标记"，物理清理等 purge——同样，行锁持有到事务提交，删除标记记录继续挡人：

-- 表 t：id = 5,10,15,20
-- 时间   会话 1                              会话 2
T1      START TRANSACTION;
T2      DELETE FROM t WHERE id = 10;   -- id=10 上 X 锁（唯一等值），记录打删除标记
T3                                        START TRANSACTION;
T4                                        SELECT * FROM t WHERE id = 10;
        -- 会话 2 的快照读：看不到会话 1 未提交的删除 → 仍返回 id=10（MVCC）
T5                                        INSERT INTO t VALUES (10, 'new');
        -- 主键 10 的 X 锁仍在会话 1 手里 → 阻塞
T6      COMMIT;                         -- 会话 2 解除等待，INSERT 报 1062 Duplicate entry
        -- （物理记录尚未 purge，等 purge 清理后才能插）

两条结论：删除标记记录继续持锁直到提交；快照读和当前读看到的版本不同步——会话 2 里 SELECT 看到旧行、INSERT 却撞新锁，这是 06-MVCC.md 的主题之一。

7. SELECT 加锁：锁定读与任务队列

7.1 FOR UPDATE / FOR SHARE

InnoDB 的 SELECT 默认是快照读（不加锁、读 MVCC 版本）。想对"读到的行"持锁：

SELECT * FROM t WHERE id = 10 FOR UPDATE;       -- X 当前读
SELECT * FROM t WHERE id = 10 FOR SHARE;        -- S 当前读（8.0 语法）
SELECT * FROM t WHERE id = 10 LOCK IN SHARE MODE;  -- 旧形式，8.4 仍兼容
SELECT * FROM a JOIN b ... FOR UPDATE OF a;     -- 多表只锁 a（8.0+）

当前读读的是最新版本（不是快照）并加锁，RR 下按第 6 节规则带 next-key/gap。

注意 “先 FOR SHARE 读、再 UPDATE 同批行"的升级模式有死锁隐患：两个事务都持 S 都想升级 X，互相等待。要么一开始就 FOR UPDATE，要么接受回滚重试。FOR SHARE 适合"读出来只为计算、确认不改同一行"的场景（如读父表状态再插子表，防父表被删）。

7.2 NOWAIT / SKIP LOCKED（8.0+）

默认锁等待最长 innodb_lock_wait_timeout（50 秒）；两个修饰词让"等"变成"不等或跳过”：

• FOR UPDATE NOWAIT：遇到锁立刻报错（3572，“Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set”），把重试交回应用层；
• FOR UPDATE SKIP LOCKED：把被别的会话锁住的行直接跳过，只返回拿到锁的行。

7.3 实战：任务队列抢单

CREATE TABLE task_queue (
  id       BIGINT PRIMARY KEY AUTO_INCREMENT,
  status   TINYINT NOT NULL DEFAULT 0,       -- 0=待处理 1=处理中 2=完成
  payload  JSON,
  KEY idx_status (status)
);

-- 每个 worker：
START TRANSACTION;
SELECT id, payload FROM task_queue
WHERE status = 0 ORDER BY id LIMIT 5
FOR UPDATE SKIP LOCKED;          -- 已被别的 worker 锁住的行直接跳过
-- 处理成功后：
UPDATE task_queue SET status = 2 WHERE id IN (/* 刚才返回的 id */);
COMMIT;

要点四条：

1. 取任务与置状态在同一个事务，否则两个 worker 拿到同一批任务；
2. SKIP LOCKED 天然让多 worker 互不等待、无重复分配；
3. NOWAIT 语义不同：宁可报错重试的场景用它，常用于"取不到就走"的削峰；
4. RR 隐患：status=0 是非唯一索引范围，锁定读会按 6.6 规则给 idx_status 加 next-key——正在被消费的这批 0 状态行之后还有 gap，可能挡新任务 status=0 的插入。队列类表建议改 RC（无 gap，见第 10 节），或把"取"与"插"错开状态设计（如只锁 id 区间走主键）。

8. 死锁专节

8.1 经典死锁：两事务反向加锁

-- 时间   会话 1                        会话 2
T1      START TRANSACTION;            START TRANSACTION;
T2      UPDATE t SET c=1 WHERE id=1;  -- X(id=1)
T3                                    UPDATE t SET c=1 WHERE id=2;  -- X(id=2)
T4      UPDATE t SET c=1 WHERE id=2;  -- 阻塞，等会话 2
T5                                    UPDATE t SET c=1 WHERE id=1;  -- 阻塞，等会话 1 → 成环
        -- InnoDB 立刻回滚一方（1213），另一方拿到锁继续

顺序固定的反向加锁是最常见形态，业务代码里体现为：批量更新两个资源但顺序不一致、A→B 转账与 B→A 转账并发。

8.2 Gap 死锁：两个 gap 兼容，一起挡住对方的插入

gap lock 之间互相兼容，这个特性决定了 gap 死锁的固定剧本（官方文档经典场景）：

-- 表 t：id = 5,10,15,20
-- 时间   会话 1                        会话 2
T1      START TRANSACTION;            START TRANSACTION;
T2      SELECT * FROM t WHERE id=8
        FOR UPDATE;                   -- 未命中 → gap(5,10)
T3                                    SELECT * FROM t WHERE id=9
        FOR UPDATE;                   -- 同样只拿到 gap(5,10)，与 T2 兼容，不阻塞
T4      INSERT INTO t VALUES (7,'a'); -- 插入意向，被会话 2 的 gap 挡 → 阻塞
T5                                    INSERT INTO t VALUES (8,'b'); -- 被会话 1 的挡 → 成环
        -- 死锁，一方回滚

“先 SELECT 判空再 INSERT"的分布式锁/唯一键防重写法在 RR + 高并发下就是这个死锁机器。解法方向：改用唯一约束 + INSERT ... ON DUPLICATE KEY、改 RC、或接受死锁重试。

8.3 检测与代价

• InnoDB 维护 wait-for graph，新锁等待到来时检测环；发现死锁立刻回滚 victim（通常是 undo 量更小的一方——改动更少的那个事务），报 1213。innodb_deadlock_detect = ON（默认）。
• 代价：热点行高并发（秒杀）下，N 个等待事务 × 每次新请求遍历图，CPU 开销近似 O(N²)。缓解：SET GLOBAL innodb_deadlock_detect = OFF + 把 innodb_lock_wait_timeout 调小（如 5 秒），用超时兜底；或前置 Redis 挡流量（第 9.4 节）减少锁队列长度。
• 只留最近一次死锁日志不够用：innodb_print_all_deadlocks = ON（默认 OFF）把每次死锁写进 error log，生产长期打开。

8.4 排查：三段输出怎么读

SHOW ENGINE INNODB STATUS（LATEST DETECTED DEADLOCK 段，节选示意）：

*** (1) TRANSACTION: ... INSERT INTO t VALUES (7,'a')
*** (1) HOLDS THE LOCK(S): RECORD LOCKS ... 'gap before rec' ... mode S
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: insert intention
*** (2) TRANSACTION: ... SELECT ... FOR UPDATE
*** (2) HOLDS THE LOCK(S): ...
*** WE ROLL BACK TRANSACTION (1)

读法：两个 *** (n) TRANSACTION 各给一条语句；HOLDS 是被持住的锁（含锁型、表、索引、LOCK_DATA——主键值或 supremum）；WAITING 是拿不到的那把；两边互为 HOLDS/WAITING 即成环；最后一行告诉你谁被牺牲。

performance_schema.data_locks / data_lock_waits（8.0+，替代旧 information_schema.INNODB_LOCKS）：

SELECT engine_transaction_id, thread_id, object_name, index_name,
       lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't';
-- 示意输出（锁型字符串为 8.0.28 实测口径：表级行 LOCK_DATA 为 NULL、
-- 记录锁 X,REC_NOT_GAP、间隙锁 X,GAP，逗号无空格）：
-- 12345 | 56 | t | NULL    | TABLE  | IX         | GRANTED | NULL
-- 12345 | 56 | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10
-- 67890 | 78 | t | PRIMARY | RECORD | X,GAP      | GRANTED | supremum
-- 12345 | 56 | t | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 7

SELECT requesting_engine_transaction_id, blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;

sys.innodb_lock_waits（现成视图，行锁等待专用）：

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits;

sql_kill_blocking_connection 直接把"杀掉谁"的 SQL 拼好了。注意：视图只覆盖 InnoDB 行锁等待，不含 MDL 等待（那是 5.2 的工具链）。

8.5 锁等待 vs 死锁

调小 timeout 治锁等待，治死锁没用（死锁不等 timeout）；开死锁检测治死锁，对超时没影响。两者是两套防线。

8.6 避免清单

1. 固定顺序访问：多行/多表更新按同一规则排序（如都按主键升序）；
2. 事务短小：少持锁、少造环；
3. 合理索引缩小锁范围：全表锁 → 两行锁，死锁概率指数级下降（6.1）；
4. 热点拆分：单行热点（账户/库存）改分片账户（指 12-分库分表.md），或前置缓存吸收读；
5. 高并发业务把"判空再插"改为"唯一约束 + 错误码”（避开 8.2 剧本）；
6. 死锁了不要慌：1213 回滚重跑即可，业务层实现幂等重试。

9. 实战：电商扣库存的四种写法

9.1 先看超卖怎么发生

反例——读-改-写在应用里拼：

-- 时间   会话 1（应用 A）                 会话 2（应用 B）      初始 stock = 1
T1      SELECT stock FROM product
        WHERE id=1;                  → 1
T2                                  SELECT stock FROM product
                                    WHERE id=1;               → 1
T3      UPDATE product SET stock=0
        WHERE id=1;
T4                                  UPDATE product SET stock=0
                                    WHERE id=1;               -- 两单都成功，卖出 2 件，实际库存 1

autocommit=1 下两个 UPDATE 确实串行执行了，但"校验"发生在应用里——读到的 1 在写回时早已过时。丢失更新的根因：SELECT 和 UPDATE 之间没有保护。

9.2 四种写法

写法一：单条 UPDATE 原子扣减（带守卫条件）

UPDATE product SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
-- 判断影响行数：1=扣成功；0=库存不足

UPDATE 本身是当前读 + X record lock，“读旧值-算-写回"在引擎内原子完成，天然无超卖。最简单也最推荐，默认首选。

写法二：SELECT … FOR UPDATE 再判断

START TRANSACTION;
SELECT stock FROM product WHERE id = 1 FOR UPDATE;   -- 拿 X 锁，后续读它的事务全等
-- 应用里判断 stock >= 1
UPDATE product SET stock = stock - 1 WHERE id = 1;
COMMIT;

适合"扣库存前要综合多个字段/多行决策"的复杂校验。代价：X 锁从 SELECT 持到 COMMIT，同商品并发排队，吞吐低于写法一。

写法三：乐观锁 version

-- 读：SELECT stock, version FROM product WHERE id = 1;   -- stock=10, version=7
-- 写：
UPDATE product SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 7;
-- 影响行数 0 → 被别人抢先改了，重读重试

读时不加锁、写时验证版本号。低冲突场景性能最好；热点商品高并发下重试风暴会吃掉收益，反而不如写法一。

写法四：Redis 前置扣减

库存搬到 Redis，Lua 原子 DECR，成功后异步落库。QPS 天花板最高，适合秒杀入口；边界也最多——Redis 宕机丢库存、异步落库失败的对账、DB 与缓存双写一致性，都要专门设计（缓存侧机制见 ../redis/07-缓存实战.md，扣库存与 MQ 消费幂等联动指 ../kafka/08-可靠性与幂等消费.md）。

9.3 选型结论

10. RC 与 RR 的差异

以上所有 gap lock / next-key 规则都是 RR 的行为。READ COMMITTED 的改动清单：

1. 不记 gap lock / next-key（例外：外键检查、唯一键冲突检查仍用 gap）。UPDATE t SET c=1 WHERE id BETWEEN 10 AND 15 在 RC 只锁命中记录，区间里的 INSERT 不挡——并发吞吐更好；
2. 半一致读（semi-consistent read）：RC 下 UPDATE 扫描时先读最新已提交版本，WHERE 不匹配的行不留锁直接跳过。RR 下这条优化不适用（快照语义不允许中途换版本判断）。后果：低选择性 UPDATE（如改 status 列）在 RC 下锁的行比 RR 少得多；
3. 快照读可见性：RC 每条语句新建 ReadView，能看见别的事务刚提交的新行；RR 整个事务复用一份（细节 06-MVCC.md）；
4. 主从复制约束：RC 下语句的执行顺序不再等价于逻辑顺序（UPDATE ... WHERE 匹配哪些行取决于当前锁等待顺序），基于 STATEMENT 的 binlog 可能主从不一致，所以 RC 要求 binlog_format=ROW（或 MIXED）；
5. 死锁画像变化：gap 型死锁（8.2）在 RC 下消失，反向加锁的记录锁死锁（8.1）照旧。互联网大厂偏爱 RC 的理由基本是这五条的组合：锁范围小、并发高、死锁模式更可预测，配合 ROW 复制的运维成本可控。

注意 切 RC 之前先确认复制链路用的是 ROW/MIXED binlog（07-日志体系.md / 10-主从复制与高可用.md），否则是带着主从不一致风险上线。

11. 锁的性能账

• 单行热点 = 串行化天花板：所有对同一行的 X 锁等待排成一队，这一行的更新 TPS 上限 ≈ 1 / 持锁时长。持锁 0.5ms 理论上约 2000 TPS，但线上 UPDATE 后面常跟着 COMMIT，redo/binlog 落盘把提交耗时拉高，实际上限常落在几百 TPS。
• 组提交缓解提交成本：binlog 组提交（binlog_group_commit_sync_delay）让多个事务合并一次 fsync，缩短锁持有中的"提交排队"段——机制见 07-日志体系.md。
• 拆行是终极解法：热点账户一行拆 N 行（每行随机分摊），把串行队列变并行——方案与一致性代价见 12-分库分表.md。
• 死锁检测开销：热点行上等待队列越长，O(N²) 检测越贵（8.3）；这也是 innodb_deadlock_detect 在秒杀场景被讨论关闭的原因。
• 锁等待本身不耗 CPU 但耗连接：一个被阻塞的 UPDATE 占着连接池里的一个连接，50 秒 timeout × 高并发 = 连接池雪崩，比慢 SQL 更急。

12. 实战建议

1. 默认 RR 不轻易动。业务没有 RC 需求就别切；真要切，先确认 ROW binlog、评估 gap 锁消失后"判空再插"类逻辑的行为变化。
2. 所有加锁范围问题，第一步看 WHERE 走哪个索引。无索引 = 锁全表，这一条能解释八成"我没更新几行怎么把库锁了"的事故。
3. 事务里只放必要的语句，尤其：别让 START TRANSACTION 之后的人工排查操作拖住事务（MDL、行锁、ReadView 三样全被钉住）。
4. 线上常开 innodb_print_all_deadlocks=ON；死锁 1213 是正常现象，在业务层做幂等重试，不要靠消灭死锁上线。
5. 给 DDL 会话永远设 lock_wait_timeout（如 60s），宁可 ALTER 失败重试，不可 ALTER 无限排队。
6. 扣库存这类"校验+修改”，优先单条原子 UPDATE，其次 FOR UPDATE，再考虑乐观锁和 Redis。
7. 任务队列用 FOR UPDATE SKIP LOCKED 是 8.0+ 的正确答案，但注意 RR 的 gap 副作用，必要时队列表单独用 RC。
8. 排查锁问题按链路记工具：MDL 看 PROCESSLIST State + sys.schema_table_lock_waits；行锁看 sys.innodb_lock_waits + performance_schema.data_lock_waits；死锁看 SHOW ENGINE INNODB STATUS + error log。
9. 改表走 Online DDL 流程（09-Online-DDL与大表变更.md），大事务拆小（11-备份恢复与性能调优.md 的资源视角）。
10. 把"会话 1 / 会话 2"当调试习惯：生产任何"数据不对"，先画出两个并发事务各自拿到什么锁、看见哪个版本，八成答案自己浮出来——下一站 06-MVCC.md。