InnoDB 锁

一句话：MVCC 负责快照读，锁负责当前读和写；InnoDB 用记录锁、间隙锁和 Next-Key Lock 保证「读到最新值」和「写不互相踩」，代价是阻塞、死锁和并发度下降。

适用版本：MySQL 8.0（InnoDB）。FOR SHARE 从 8.0.1 起取代 LOCK IN SHARE MODE；旧写法仍可用，但新代码应使用 FOR SHARE。innodb_autoinc_lock_mode 在 8.0 的默认值是 2。

为什么需要

MVCC 让普通 SELECT 读历史版本，不阻塞写。但有些操作必须看最新值：扣库存前要 SELECT ... FOR UPDATE 锁住行，UPDATE/DELETE 本身也要先定位最新行。这些场景不能读快照，只能加锁。

锁要解决两个问题：写写互斥（两个事务不能同时改同一行）和当前读的幻读（范围查询后不能有新区间插入）。

核心机制

快照读与当前读

类型	例子	机制	看到什么
快照读	SELECT * FROM t WHERE id=1	MVCC	ReadView 决定的旧版本
当前读	SELECT ... FOR UPDATE / FOR SHARE	行锁	最新已提交版本
写	UPDATE / DELETE / INSERT	行锁（X）	最新版本

行锁的三种形态

• Record Lock：锁一条索引记录。注意锁的是索引项，不是数据行。
• Gap Lock：锁两个索引值之间的间隙，阻止 INSERT；间隙锁之间不互斥。
• Next-Key Lock：Record + Gap，锁「左开右闭」区间，是 RR 下范围扫描的默认行为。
• Insert Intention Lock：INSERT 时加的意向间隙锁，多个插入意向锁可以共存，但会被间隙锁阻塞。

加锁行为取决于访问路径：

-- 唯一索引等值命中：退化为 Record Lock
SELECT * FROM t WHERE id = 1 FOR UPDATE;

-- 唯一索引等值未命中：Gap Lock，锁住值应在的间隙
SELECT * FROM t WHERE id = 999 FOR UPDATE;

-- 范围查询：Next-Key Lock
SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- 无索引条件：扫描到的所有行都加锁，效果接近表锁
UPDATE t SET status = 0 WHERE name = 'x';

最后一条是生产事故的常见来源：WHERE 没走索引，锁的范围从「一行」变成「全表扫描到的所有行」。

表级锁与 MDL

InnoDB 的 DML 一般不加表级 S/X 锁，但会在表上加意向锁（IS/IX），让表锁能 O(1) 判断「表里有没有行锁」。

MDL（metadata lock）是 Server 层的锁：DML 加 MDL 共享锁，DDL 加 MDL 排他锁。长事务持有共享锁不释放，DDL 的排他锁就会排队，后面的 DML 又排在 DDL 后面，表现为「数据库假死」。

自增锁

innodb_autoinc_lock_mode 在 8.0 默认是 2（interleaved）：不再用表级 AUTO-INC 锁，并发插入性能最好，但批量插入的 ID 可能不连续。依赖「同一语句内 ID 连续」的逻辑要显式改成 1。

隔离级别与间隙锁

RC 下间隙锁基本关闭，只保留记录锁，并发更高，但当前读的幻读需要业务自己处理；RR 下启用 Next-Key Lock，当前读的幻读被锁住。降低隔离级别不是无代价的优化。

实战场景

一、WHERE 没走索引，一行更新锁住全表

现象：一条“把某个商户的状态置为失效”的更新（UPDATE t_merchant SET status=0 WHERE merchant_no='xxx'）执行 30 秒没返回，期间该表几乎所有写都被阻塞，业务侧表现为“商户管理页全卡在保存”。SHOW ENGINE INNODB STATUS 里能看到大量等待同一个表的请求。

排查：锁加在索引项上，不是数据行上。

定位层	取证据	判定
是否走索引	EXPLAIN UPDATE ... 的 key、rows	key=NULL 且 rows 接近全表 → 命中
锁住多少行	SELECT * FROM performance_schema.data_locks WHERE object_name='t_merchant'	记录锁数量接近表行数量级即确认
阻塞链	data_lock_waits 或 sys.innodb_lock_waits	一个 blocker 卡住几十上百个 waiter
字段类型	merchant_no 是否字符串列却传了数字	隐式类型转换让索引失效，退化成全表扫描
字符集/排序规则	联合索引列的 collation 是否与连接一致	不一致同样导致不走索引
统计信息	information_schema.STATISTICS 与最近 ANALYZE 时间	优化器误判选错索引

-- 生产事故的常见来源：WHERE 没走索引，锁范围从“一行”变成“扫描到的所有行”
UPDATE t_merchant SET status = 0 WHERE name = 'x';

对症做法：

-- 1. 给等值条件列建索引（或修正类型/字符集，让索引真的被用上）
ALTER TABLE t_merchant ADD INDEX idx_merchant_no (merchant_no);

-- 2. 大表加索引走在线 DDL，避免长时间阻塞写
ALTER TABLE t_merchant ADD INDEX idx_merchant_no (merchant_no), ALGORITHM=INPLACE, LOCK=NONE;

-- 3. 让“等不到锁就失败”而不是无限排队
SET SESSION innodb_lock_wait_timeout = 5;      -- 默认 50s，按业务收紧
SET SESSION lock_wait_timeout = 60;            -- MDL 等待上限，DDL/查询都受它约束

4. 批量变更按主键区间切片执行，每片 1000~5000 行、片间 commit，
   把一次锁全表变成多次锁少量行。
5. 高危 SQL 走审核规则：UPDATE/DELETE 必须命中索引且带 LIMIT 或主键范围，
   由 DBA 平台在执行前 EXPLAIN 拦截。

复盘要点：判据是“生产上不存在不带索引条件的 UPDATE/DELETE”，用 SQL 审计平台的 EXPLAIN 结果统计比例；另外“单条 SQL 阻塞等待者数量”做成告警（例如 >20 个 waiter，经验值需校准），它比锁等待时间更早反映危险。

二、一个未提交事务 + 一条 DDL，整库“假死”

现象：某次给热表加字段，ALTER TABLE 卡住不动。运维 kill 掉 ALTER 后，业务请求反而大面积超时。最终发现现场是这样：一个后台任务开着事务没提交（持 MDL 共享锁）→ ALTER 排队等 MDL 排他锁 → 之后所有对该表的 DML 都排在 ALTER 后面。表现像“数据库挂了”，其实只有那一列队。

排查：MDL 是 Server 层的锁，DML 加共享、DDL 加排他。

定位层	取证据	判定
谁在等 MDL	SHOW PROCESSLIST 里 State: Waiting for table metadata lock	成批出现即队列形成
谁持有	performance_schema.metadata_locks 的 LOCK_STATUS='GRANTED' 行 + 对应线程	找到源头事务
源头是否长事务	INNODB_TRX.trx_started 与 trx_query（可能为 NULL）	空闲未提交事务最容易漏
DDL 优先级	ALTER 在队列中间，会阻塞其后所有 DML	这才是“假死”的机制
等待上限	lock_wait_timeout 是否过大或未设	没有上限就会无限排队
版本与工具	是否用 Online DDL / gh-ost / pt-osc	影响锁与复制负担

SELECT object_schema, object_name, lock_type, lock_status, owner_thread_id
  FROM performance_schema.metadata_locks
 WHERE object_type = 'TABLE' AND object_name = 't_order';
-- 用 owner_thread_id 反查 processlist / events_statements，找到那个没提交的事务

对症做法：

-- 1. DDL 前先设短等待，抢不到就放弃，不让自己变成队列头
SET SESSION lock_wait_timeout = 5;
ALTER TABLE t_order ADD COLUMN tag VARCHAR(32) NULL, ALGORITHM=INSTANT;   -- 8.0 多数加列可 INSTANT

-- 2. 热表的大改动走影子表工具，避免长时间持有排他 MDL
--    gh-ost / pt-online-schema-change：复制 + 原子改名，切换只需一次短暂 MDL

3. 发布窗口前先清场：
   检查 INNODB_TRX 里超过 60s 的事务并处理，再执行 DDL；
   顺序错了就会把“改字段”变成“整库不可用”。
4. 空闲事务要收口：连接池不要允许 autocommit=false 且长时间 idle 的连接；
   服务端设 wait_timeout，应用侧事务边界用注解/模板明确包住。
5. kill 掉 DDL 是正确止血的第一选择（它排除了队列头），
   但只有先解决持锁事务才不会再复现。

// 6. 应用侧防呆：只读探测也收进事务外，避免“查一下就把表锁住不放”
@Transactional(readOnly = true, timeout = 5)    // 给只读事务也设上限
public Snapshot loadSnapshot(long id) { ... }

复盘要点：判据是“DDL 变更单里必须包含“无长事务”检查项与 lock_wait_timeout 设置”，并把“Waiting for table metadata lock 的连接数”做成告警——它是这个链条最早可见的信号。MDL 与行锁是两个层次的锁：InnoDB 的 DML 一般不加表级 S/X 锁，但会在表上加 IS/IX 意向锁，让表锁能 O(1) 判断表里有没有行锁；把两者混为一谈会找错方向。

三、间隙锁与插入意向锁互撞，形成周期性死锁

现象：订单超时关闭任务每 5 分钟跑一轮，与用户侧“补发一条明细”的写操作交替出现死锁：SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 里，两边都在 t_order_item 上等待对方，一个持有 gap lock、另一个等 insert intention lock。每天约 10 余次（经验值，需校准），业务表现为零星“下单失败请重试”。

排查：RR 下范围扫描默认加 Next-Key Lock，间隙锁之间不互斥但会挡住插入。

定位层	取证据	判定
死锁原文	SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段	能直接读出两边各持什么锁
加锁范围	语句是否为范围/非唯一索引等值：BETWEEN 10 AND 20 → Next-Key	命中区间即锁间隙
唯一索引未命中	WHERE id=999 FOR UPDATE → Gap Lock	未命中也加间隙锁，这是插入死锁的来源
插入语义	另一事务的 INSERT 需要插入意向锁	被 gap 锁阻塞即等待
执行顺序	两个事务对同一区间加锁的先后	交叉即死锁
隔离级别	是否为 RR；RC 下间隙锁基本关闭	降到 RC 能消除这一类死锁

-- 关闭任务：范围扫描 + 当前读，锁住 [10, 20] 这段间隙
SELECT id FROM t_order_item WHERE order_id BETWEEN 10 AND 20 FOR UPDATE;
-- 用户侧：往这段间隙里插一行，需要 Insert Intention Lock，被上面挡住
INSERT INTO t_order_item(order_id, sku) VALUES (15, 'A');
-- 若用户侧事务先插了另一行、又等这个区间的锁，就构成环路 → 死锁

对症做法：

-- 1. 缩小锁范围：任务按主键列表处理，不用 BETWEEN
SELECT id FROM t_order_item WHERE id IN (...) FOR UPDATE;      -- 命中唯一索引，退化为 Record Lock

-- 2. 用唯一键 + UPSERT 替代“先锁再插”，避免插入意向锁与 gap 争用
INSERT INTO t_order_item(order_id, sku, qty) VALUES (?, ?, ?)
  ON DUPLICATE KEY UPDATE qty = VALUES(qty);

-- 3. 统一加锁顺序：所有路径都按 (order_id asc) 排序后处理，避免交叉等待

# 4. 需要高并发写入的业务，考虑降到 RC（binlog_format=ROW 是前提）
#    RC 下只保留记录锁，间隙锁相关死锁基本消失，代价是当前读的幻读要业务自己处理
transaction-isolation = READ-COMMITTED
binlog_format = ROW

5. innodb_print_all_deadlocks 打开，把死锁细节持续写入错误日志便于统计频率；
   死锁本身是 InnoDB 自动回滚一方，应用必须能安全重试（幂等 + 有限重试）。
6. 降低隔离级别不是无代价的优化：
   先确认下游是否依赖“当前读不幻读”，再决定是改级别还是改 SQL 形态。

复盘要点：判据是“死锁次数按 SQL 指纹统计，Top1 指纹必须被消掉”，而不是笼统说“偶发死锁”。真正能消掉这类死锁的是 SQL 形态（等值命中唯一索引 / UPSERT / 固定加锁顺序），把 RR 改成 RC 只是把问题从间隙锁转移到业务判断上。超时与重试的具体口径见 SQL 调优与 EXPLAIN。

最小上手

两个会话观察锁等待（MySQL 8.0）：

-- 会话 A
START TRANSACTION;
SELECT * FROM t WHERE id = 1 FOR UPDATE;   -- 持有 X 锁

-- 会话 B（会阻塞，默认 50 秒后报错）
SELECT * FROM t WHERE id = 1 FOR UPDATE;
-- 或立即失败：
SELECT * FROM t WHERE id = 1 FOR UPDATE NOWAIT;
-- 或跳过被锁行：
SELECT * FROM t WHERE id = 1 FOR UPDATE SKIP LOCKED;

-- 会话 A
ROLLBACK;

共享锁的新写法：

SELECT * FROM t WHERE id = 1 FOR SHARE;   -- 8.0.1+，取代 LOCK IN SHARE MODE

生产动作

锁等待排查：

-- 当前锁与等待关系（8.0）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- sys 库封装好的等待视图
SELECT * FROM sys.innodb_lock_waits\G

-- 元数据锁
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 't_order';

• innodb_lock_wait_timeout=50（8.0 默认，秒）：行锁等待超时。超时默认只回滚当前语句，事务还在，必须显式 ROLLBACK；innodb_rollback_on_timeout=OFF（默认）就是这种行为。
• innodb_deadlock_detect=ON（8.0 默认）：检测到死锁立即回滚代价较小的事务；死锁日志看 SHOW ENGINE INNODB STATUS\G 的 LATEST DETECTED DEADLOCK。
• lock_wait_timeout=31536000（8.0 默认，秒）：MDL 等元数据锁的超时，默认接近一年，等于不超时；DDL 前应显式设置一个短值。
• 排障顺序：现象是「某条 SQL 一直不返回」→ 查 data_lock_waits 找到阻塞者 → 看阻塞事务的 trx_query 和 trx_started → 决定 kill 还是等；不要只看被阻塞的 SQL。

边界

• 无索引的写操作会放大锁范围：UPDATE/DELETE 的 WHERE 必须能走索引，否则扫描到的行都加锁。
• 间隙锁降低并发：高并发插入场景，RR 的 Next-Key Lock 容易造成插入等待和死锁；是否降到 RC 要结合 Binlog 格式和业务一致性要求。
• NOWAIT / SKIP LOCKED 不是银弹：前者把等待变成快速失败，后者会跳过被锁行，业务必须能接受「这次没拿到」。
• 死锁不是 bug：它是并发加锁的必然可能，应用要能识别死锁错误并重试，而不是假设它不会发生。
• MDL 和行锁是两层：行锁排查完仍卡住，去看 metadata_locks。

讲清楚

InnoDB 的并发控制是两条腿：普通 SELECT 走 MVCC 读快照，当前读和写走锁读最新版本。行锁锁的是索引项，唯一索引等值命中退化成记录锁，范围查询用 Next-Key Lock 锁住记录和间隙，没有索引的 WHERE 会把扫描到的行全锁上。RR 靠间隙锁防当前读的幻读，RC 关掉间隙锁换并发但把幻读留给业务。生产上最该看的是锁等待视图和 MDL：一条 SQL 不返回，先找谁在阻塞，再决定 kill 还是等；DDL 卡住往往不是行锁，而是长事务持有的 MDL 共享锁。