一、起源

事务与锁要解决的是一个被具体化的矛盾:多个会话同时读写同一批数据时,如何既保证各自看到的结果符合预期,又不过度牺牲并发能力。

1.1 并发读写带来的三类问题

在没有隔离机制的前提下,两个事务交替执行会产生三类可复现的异常现象。三者都源自"一个事务读到了另一个未完成事务的影响"或"同一事务的两次读之间数据被改动"。

脏读

一个事务读到了另一个事务尚未提交的修改。若后者随后回滚,前者依据的是一份从未真正成立的数据。

事务 A                              事务 B
BEGIN
UPDATE account SET balance = 0
                                    BEGIN
                                    SELECT balance → 读到 0(脏读)
ROLLBACK
                                    -- 事务 B 依据的 0 从未存在

不可重复读

同一事务内两次读取同一行,结果不同。原因是两次读之间另一个事务提交了修改。

事务 A                              事务 B
BEGIN
SELECT balance FROM account WHERE id = 1 → 100
                                    BEGIN
                                    UPDATE account SET balance = 200 WHERE id = 1
                                    COMMIT
SELECT balance FROM account WHERE id = 1 → 200(与第一次不一致)

不可重复读针对的是已存在行的内容发生改变,表现为同一条记录的值前后不同。

幻读

同一事务内两次执行同一个范围查询,结果集的行数不同。原因是两次查询之间另一个事务插入或删除了满足条件的行。

事务 A                              事务 B
BEGIN
SELECT COUNT(*) FROM orders WHERE amount > 100 → 3
                                    BEGIN
                                    INSERT INTO orders (amount) VALUES (200)
                                    COMMIT
SELECT COUNT(*) FROM orders WHERE amount > 100 → 4(多出一行"幻影")

幻读针对的是结果集的边界发生改变,表现为凭空多出或少掉若干行。它与不可重复读的处置手段不同:不可重复读可以通过锁定已有行解决,幻读必须通过锁定"行之间的空隙"解决,这正是间隙锁存在的原因。

1.2 一致性与并发度的取舍

上述三类问题并非必须全部消除。消除得越彻底,需要的锁定范围越大、持锁时间越长、并发度越低。取舍体现在三个层面。

  1. 强度取舍:脏读在任何业务中都不能接受;不可重复读在多数业务中可以通过读单行后立即使用规避;幻读只在"先查后写"的判断类逻辑中才构成真实风险。隔离强度应按业务对数据确定性的实际要求选择,而不是一律拉满。
  2. 实现取舍:解决读一致性有两条路径。一是加锁,让读操作阻塞写操作,读到的永远是当前最新值;二是多版本,写操作保留旧版本,读操作读取符合自身快照的旧版本,读写互不阻塞。锁的代价是并发度,多版本的代价是存储与清理开销。
  3. 范围取舍:锁定的对象可以是整行、整表,也可以是行之间的空隙。锁定的粒度越细,并发度越高,但加锁规则的推理成本与死锁概率随之上升;粒度越粗,实现简单但阻塞面大。

MySQL 的 InnoDB 采用的组合是:行级锁负责写与写的互斥,多版本并发控制(MVCC)负责读与写的互不阻塞,两者共同支撑四种隔离级别。

1.3 本文的边界

本文讨论的范围限定在单个 MySQL 实例、单个数据库内的事务与锁,包括隔离级别、MVCC、锁的类型与加锁规则、死锁与长事务的治理。

以下内容属于其他文档的范围,本文不重复:

  • 事务的操作命令、隔离级别的查看与设置命令、诊断命令清单,见 mysql: 使用命令 第十章与第九章。
  • 跨服务、跨数据库的分布式事务与最终一致方案(两阶段提交、TCC、本地消息表、事务消息、最大努力通知),见 消息一致性:事务消息与最终一致性。本篇只讨论本地事务,不涉及跨数据源的原子性协调。
  • 缓存与数据库双写造成的不一致窗口、更新顺序与延迟双删,见 缓存基础与一致性 第 2.4 节。
  • 主从复制的线程模型、位点推进与复制模式,见 mysql:主从同步原理。本篇只讨论一致性读与复制延迟的交互。

二、原理

2.1 事务与 ACID

事务是一组要么全部生效、要么全部不生效的数据库操作。ACID 四个特性分别描述事务在四个维度上的承诺,每个特性背后有对应的实现机制。

特性 含义 实现手段
原子性(Atomicity) 事务内的操作全做或全不做,失败时回到事务开始前的状态 undo log 记录反向操作,回滚时逐条撤销
一致性(Consistency) 事务前后数据满足业务约束与完整性约束(主键唯一、外键、字段取值范围) 由原子性、隔离性、持久性共同保证,叠加数据库约束与业务校验
隔离性(Isolation) 并发事务之间互不干扰,各自看到的数据符合隔离级别约定 行级锁与 MVCC 共同实现
持久性(Durability) 事务提交后结果不因崩溃而丢失 redo log 预写(WAL),提交时刷盘

undo log 与原子性

undo log 是逻辑日志,记录的是"如何撤销":插入对应删除,删除对应插入(并保存被删行的内容),更新保存被更新列的旧值。回滚时 InnoDB 沿 undo 记录逆向执行,把数据恢复为事务开始前的样子。

undo log 按用途分为两类:insert undo 只在事务回滚时使用,事务提交后即可删除;update undo 除回滚外还承担 MVCC 的旧版本读取,事务提交后不能立即删除,必须等没有任何快照需要它时才能清理(见 2.3 节)。

redo log 与持久性

数据页的修改先写入内存中的缓冲池,随后在检查点阶段异步刷盘。若在刷盘前崩溃,内存中的修改会丢失。redo log 记录"数据页上做了什么物理修改",事务提交时先保证 redo log 落盘(write-ahead logging),崩溃恢复时依据 redo log 重放尚未刷盘的数据页。

redo log 是固定大小的环形文件,写满后需要推进检查点、把脏页刷盘才能覆盖,因此写密集场景下 redo 容量与刷新策略直接影响写入吞吐。

提交流程与两阶段提交

一次事务提交涉及 redo log 与 binlog 两个日志,两者的写入必须保持一致,否则主从数据或崩溃恢复后的数据会出现分歧。InnoDB 采用两阶段提交:

1. 事务修改写入内存并生成 redo,redo 进入 prepare 状态
2. 写入 binlog 并刷盘
3. redo 进入 commit 状态
崩溃恢复时的判定:
  redo 处于 prepare 且 binlog 完整   → 提交
  redo 处于 prepare 且 binlog 不完整 → 回滚

提交与回滚的代价

提交并非零成本,其开销集中在刷盘与日志写入上。

  • 提交时的 fsync 次数由 innodb_flush_log_at_trx_commit 控制:取 1 时每次提交都刷 redo,最安全,写入延迟最高;取 2 时只写操作系统缓存,实例崩溃可能丢最近若干事务;取 0 时由后台线程按秒刷,性能最好但崩溃丢失窗口最大。
  • binlog 是否同步刷盘由 sync_binlog 控制,取 1 时每次提交都刷 binlog 文件。
  • 回滚的代价与被撤销的修改量成正比。回滚一个大事务(如一次删除百万行)需要重新读取 undo 并逐条撤销,期间会持续占用 IO 与 CPU,并可能拖慢其他会话,因此"先写后回滚"不是省事而是更贵。

2.2 隔离级别

SQL 标准定义四种隔离级别,级别越高,允许出现的并发异常越少。InnoDB 对标准的实现有两处需要特别说明:可重复读级别通过临键锁基本避免了幻读,读未提交级别实际几乎不被使用。

隔离级别 脏读 不可重复读 幻读 InnoDB 的实现特点
读未提交(READ UNCOMMITTED) 允许 允许 允许 直接读取最新版本,不保证快照,几乎不用于生产
读已提交(READ COMMITTED,RC) 不允许 允许 允许 每次快照读都生成新的 ReadView;不使用间隙锁(外键与唯一键冲突检查除外)
可重复读(REPEATABLE READ,RR) 不允许 不允许 不允许 事务内首次快照读生成 ReadView 并复用;写操作使用临键锁,基本避免幻读
串行化(SERIALIZABLE) 不允许 不允许 不允许 所有普通读自动加上共享锁,读写互相阻塞,等效于串行执行

默认级别

MySQL 的默认隔离级别为可重复读(RR),由参数 transaction_isolation 控制。主从复制中 STATEMENT 格式的 binlog 依赖 RR 级别的可重复读语义才能保证从库重放结果一致,这是 RR 成为默认值的历史原因之一;ROW 格式的 binlog 不受此约束。

设置的作用范围

隔离级别可以在三个层次设置,后者的作用范围小于前者:

作用范围 语句 生效时机
全局 SET GLOBAL TRANSACTION ISOLATION LEVEL ... 对之后建立的新连接生效,已存在的连接不受影响
会话 SET SESSION TRANSACTION ISOLATION LEVEL ... 对当前连接之后开始的事务生效
下一个事务 SET TRANSACTION ISOLATION LEVEL ...(须在事务开始前执行) 仅对紧接着开始的一个事务生效

高危 提醒:修改全局隔离级别会影响此后建立的全部连接,即影响所有应用实例的读写行为,属于高影响面配置变更。兜底方式:变更前确认应用是否依赖 RR 的可重复读语义(尤其是"先查询判断、再写入"的逻辑);先在单实例或从库以会话级别验证;记录变更前的取值,异常时立即用 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ 改回,并让应用重连以脱离旧会话级别。

不同级别下的加锁差异

隔离级别直接影响加锁行为,其中 RC 与 RR 的差别最需要关注。

对比项 读已提交(RC) 可重复读(RR)
快照生成时机 每次快照读都重新生成 ReadView 事务内第一次快照读生成,之后复用
同一事务内两次读 可能读到不同结果(不可重复读) 结果稳定
写操作加锁 一般只加行锁,不加间隙锁 加临键锁(记录锁加间隙锁)
间隙上的插入 不被等值更新阻塞 可能被间隙锁阻塞
死锁概率 较低 较高(持锁范围更大)
主从一致性 半一致读(semi-consistent read)可减少不匹配行的等待 无此优化

半一致读是 RC 下的一项优化:更新时若发现某行不满足更新条件,InnoDB 会先释放该行上的锁再返回给 MySQL 层,从而避免为不匹配的行长期持锁。该行为在 RR 下不生效。

因此 RC 的取舍是明确的:以放弃事务内的读一致性换取更小的锁定范围、更低的死锁概率与更好的主从一致性。这也是多数互联网业务选择 RC 的原因。

2.3 MVCC

多版本并发控制让"读"不必等待"写",其前提是每一行数据都保留多个历史版本。InnoDB 通过在数据行中附加隐藏列、并把旧版本存放在 undo log 中实现。隐藏列、undo 表空间与回滚段在存储层的组织方式见 mysql: InnoDB 存储引擎。

隐藏列与版本链

InnoDB 的聚簇索引记录中除业务列外,还包含三个隐藏列:

隐藏列 长度 含义
DB_ROW_ID 6 字节 行标识。表上没有主键、也没有非空唯一索引时,InnoDB 用该列生成隐藏聚簇索引
DB_TRX_ID 6 字节 最近一次修改该行的事务 ID
DB_ROLL_PTR 7 字节 回滚指针,指向 undo log 中该行的上一个版本

每一行的隐藏列构成一条链表:当前记录指向 undo log 中的上一个版本,上一个版本再指向更早的版本,形成版本链。链的头部是最新值,尾部是最早的旧值。查询时若当前版本不可见,就沿 DB_ROLL_PTR 逐级回溯,直到找到可见版本。

ReadView 的构成

ReadView 是事务执行快照读时产生的一份"可见性判定依据",包含四个字段:

字段 含义
m_ids 生成 ReadView 时,当前系统中尚未提交的活跃事务 ID 列表
min_trx_id m_ids 中的最小值,即当前活跃事务中最小的事务 ID
max_trx_id 生成 ReadView 时系统应分配给下一个事务的 ID,即当前最大事务 ID 加一
creator_trx_id 生成本 ReadView 的事务自身的事务 ID

可见性判定规则

读取某行记录时,取该行版本的 DB_TRX_ID 记为 trx_id,按下述顺序判定:

  1. trx_id == creator_trx_id:该版本由本事务修改,可见。
  2. trx_id < min_trx_id:该版本在 ReadView 生成前已提交,可见。
  3. trx_id >= max_trx_id:该版本由 ReadView 生成之后才开始的事务修改,不可见。
  4. min_trx_id <= trx_id < max_trx_id:若 trx_id 在 m_ids 中,说明该事务在生成 ReadView 时仍活跃、未提交,不可见;若不在 m_ids 中,说明该事务已提交,可见。

判定为不可见时,沿 DB_ROLL_PTR 取上一个版本重新判定,重复至找到可见版本或版本链结束。

读已提交与可重复读的快照创建时机

两者的差异只在一处:ReadView 何时生成、是否复用。

  • 可重复读(RR):事务内第一次快照读时生成 ReadView,此后整个事务复用同一份 ReadView。因此同一事务内后续的每次快照读都基于同一份活跃事务列表进行判定,读到的结果稳定,实现了可重复读。
  • 读已提交(RC):每次快照读都重新生成一份 ReadView。因此上一次查询之后提交的事务,其结果在本次查询中变为可见,表现为不可重复读。
RR:事务内                         RC:事务内
  快照读1 → 生成 ReadView A          快照读1 → 生成 ReadView A
  快照读2 → 复用 ReadView A          快照读2 → 生成 ReadView B(包含新提交的事务)
  快照读3 → 复用 ReadView A          快照读3 → 生成 ReadView C

当前读与快照读

MVCC 只作用于快照读。以下两类语句的行为完全不同,混用是排查锁问题时最常见的误解来源。

类型 语句 读取的数据 是否加锁
快照读(一致性读) 普通 SELECT 符合本事务 ReadView 的版本链中的可见版本 否
当前读(锁定读) SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE 最新版本 是,加行锁或临键锁
当前读(锁定读) INSERT、UPDATE、DELETE 最新版本 是,加行锁或临键锁

结论:写操作与显式加锁的读操作读取的都是最新数据,即当前读;普通 SELECT 才是快照读。 这意味着"先用普通查询判断,再用更新落库"的模式无法阻止并发修改——判断依据是快照,落库依据是最新值,两者之间不存在一致性保证。需要原子判断时必须使用当前读或带条件的更新语句。

undo 版本链的回收与长事务

undo log 中的旧版本不能随意删除:只要仍存在一个活跃事务,它的 ReadView 可能还需要某个旧版本,purge 线程就不能清理该版本及其之后的版本链。

判定依据是系统中最早生成的、尚未结束的 ReadView。因此:

  • 一个长期不提交的事务会持续钉住一份 ReadView,使 purge 无法推进,undo 版本链不断变长,回滚段与历史数据占用持续增长;
  • 表现为 SHOW ENGINE INNODB STATUS 中 History list length 持续增大,undo 表空间膨胀,快照读需要遍历更长的版本链而变慢;
  • 清理只能在事务结束后由 purge 线程异步追赶,积压量大时清理过程本身会带来 IO 压力。

这是长事务最直接的危害,也是"避免长事务"的技术根据,具体治理见 2.8 节与 3.3 节。

2.4 锁的类型与粒度

InnoDB 的锁按作用范围分为全局、表级、行级三层。层级之间由意向锁衔接,形成"先声明后加锁"的关系。

2.4.1 全局锁

全局锁锁定整个数据库实例,加锁期间除持有者外的所有写操作被阻塞。

  • FLUSH TABLES WITH READ LOCK(FTWRL):使整个实例进入只读状态,用于一致性备份与数据导出前的准备。
  • 影响范围是整个实例,属于维护类操作。InnoDB 的在线备份能力已可替代大部分场景下的全局锁需求。

2.4.2 表级锁

表级锁 语义 加锁方式
表共享锁(S) 允许读写该表的数据,阻止其他会话获取表排他锁 LOCK TABLES t READ
表排他锁(X) 只有持有者可读写该表 LOCK TABLES t WRITE
元数据锁(MDL) 保护表结构,防止查询与修改表结构并发 系统自动加:普通读写加 MDL 读锁,ALTER TABLE 等 DDL 加 MDL 写锁
意向共享锁(IS) 表级标记:"本事务即将在表中某些行上加共享锁" 系统自动加,无需显式声明
意向排他锁(IX) 表级标记:"本事务即将在表中某些行上加排他锁" 系统自动加,无需显式声明
自增锁(AUTO-INC) 保护自增列取值的连续性 系统自动加,行为由 innodb_autoinc_lock_mode 控制

元数据锁的实际影响

MDL 是生产事故的常见来源。一个未提交的查询或事务持有 MDL 读锁时,ALTER TABLE 需要的 MDL 写锁会被阻塞;而该 DDL 请求进入等待队列后,后续所有对该表的读写请求都会被排在 DDL 之后(写锁请求优先级高于读锁请求),表现为"一条未提交的查询把整张表挂住"。识别方式见 3.1 节,处置方式是尽快结束持锁事务或选择业务低峰执行 DDL。

自增锁的三种模式

innodb_autoinc_lock_mode 决定自增值的分配方式,取舍在"连续性"与"并发度"之间:

取值 模式 行为 适用
0 传统模式 语句级持有自增锁,语句结束才释放,保证同一语句内自增值连续 需要连续自增值的旧应用
1 连续模式(默认) 普通插入用轻量互斥量取一批值即释放;批量插入(INSERT ... SELECT)等无法预知行数时仍用语句级锁 多数场景
2 交叉模式 始终用轻量互斥量分配,同一语句的自增值可能不连续 配合 ROW 格式 binlog,并发最高

取值 2 时自增值可能出现空洞且语句内不连续,在 STATEMENT 格式的 binlog 下会导致主从不一致,因此取值 2 必须配合 ROW 格式的 binlog。

2.4.3 行级锁

行级锁 作用对象 语义
记录锁(Record Lock) 索引上的一条记录 锁定单条索引记录。锁加在索引记录上,若表无索引则退化为锁定整个聚簇索引的所有记录
间隙锁(Gap Lock) 两条索引记录之间的开区间 阻止其他事务在该间隙内插入,不阻止对已有记录的修改。只在 RR 级别下使用
临键锁(Next-Key Lock) 记录锁与间隙锁的组合 锁定"前开后闭"区间(上一个记录, 本记录],既锁住已有记录也锁住其左侧间隙。是 RR 级别下加锁的基本单位
插入意向锁(Insert Intention Lock) 插入位置所在的间隙 一种特殊的间隙锁,表示"即将在此间隙内插入"。多个插入意向锁之间兼容,但与已存在的间隙锁或临键锁冲突

行锁加在索引上

一个必须明确的前提是:InnoDB 的行锁加在索引记录上,而不是加在数据行上。因此:

  1. 若一条更新语句能通过索引精确定位,则在对应索引记录上加锁;
  2. 若无法使用索引,则扫描聚簇索引上的每条记录并逐条加锁,锁定范围扩大到全表;
  3. 二级索引上的锁与聚簇索引(主键)上的锁可能同时存在:通过二级索引定位后再回表读取数据,主键索引上的对应记录也会被加锁。

这也解释了后文加锁规则中"唯一索引"与"非唯一索引"、"命中"与"未命中"、"等值"与"范围"为何必须分别讨论。

2.4.4 兼容关系

表级锁与意向锁的兼容矩阵

已持有 \ 请求 表 X 表 S IX IS
表 X 冲突 冲突 冲突 冲突
表 S 冲突 兼容 冲突 兼容
IX 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

意向锁之间(IX 与 IS、IX 与 IX、IS 与 IS)互相兼容,因为意向锁只表达"将在某些行上加锁"的意图,不产生实际互斥;意向锁与表级锁冲突,因为表级锁要求的是整表控制权。

行级锁的兼容矩阵

已持有 \ 请求 记录 S 记录 X 间隙锁 插入意向锁
记录 S 兼容 冲突 兼容 兼容
记录 X 冲突 冲突 兼容 兼容
间隙锁 兼容 兼容 兼容 冲突
插入意向锁 兼容 兼容 冲突 兼容

三处需要注意:

  1. 间隙锁之间互不冲突。两个事务可以同时对同一间隙加间隙锁,间隙锁的唯一作用是阻止"插入",而不是阻止其他事务加锁。这也意味着间隙锁不会造成两个事务之间的直接锁等待,但会共同阻塞插入。
  2. 插入意向锁与间隙锁冲突。这是"间隙锁导致插入阻塞"的完整链路:事务 A 持有某间隙的间隙锁,事务 B 在该间隙插入时先申请插入意向锁,申请被阻塞,INSERT 表现为等待。
  3. 临键锁是两个锁的组合,其兼容性需要分别对记录部分与间隙部分判断:记录部分的互斥规则同记录锁,间隙部分不与其他间隙锁冲突。

2.5 加锁规则

InnoDB 在可重复读级别下的加锁行为不是逐条硬编码,而是由两条原则与两条优化规则推导得出。掌握推导过程比记住结论更重要,因为数据分布与索引结构的变化会改变结果。

两条原则

  1. 加锁的基本单位是临键锁,即"前开后闭"区间(上一个索引记录, 本次访问到的记录]。
  2. 只有查找过程中真正访问到的对象才会被加锁。未被访问的记录与间隙不受影响。

两条优化

  1. 索引上的等值查询,若使用唯一索引(含主键)且命中了记录,临键锁退化为记录锁。原因是由唯一性可以确定不会有第二行满足条件,无需锁住其左侧间隙。
  2. 索引上的等值查询,若有记录不满足等值条件而被访问到(即未命中),临键锁退化为间隙锁。原因是既然不存在满足条件的记录,需要保护的是"不该出现满足条件的记录"这一区间。

此外存在一条已知的边界行为:唯一索引上的范围查询会一直向右扫描到第一个不满足条件的记录为止,该记录也会被加上临键锁,使锁定范围比直觉上多一格。不同版本之间存在差异,因此下文的推理结论应在具体实例上实测验证(实测方法见 3.1 节)。

用于推理的示例表结构:

CREATE TABLE t (
  id INT PRIMARY KEY,
  c  INT,
  d  INT,
  KEY idx_c (c)
) ENGINE = InnoDB;

-- 示意数据:id 与 c 的取值依次为 0、5、10、15、20、25

2.5.1 等值命中唯一索引

SELECT * FROM t WHERE id = 5 FOR UPDATE;

推理过程:通过主键索引定位到 id=5 的记录,访问到的对象只有这一条记录。按原则 1,加临键锁 (0, 5];按优化 1,唯一索引等值命中,退化为记录锁。

结果:只在 id=5 上加记录锁。插入 id=3 或 id=7 均不受阻塞,更新 id=10 也不受影响。

2.5.2 等值未命中唯一索引

SELECT * FROM t WHERE id = 7 FOR UPDATE;

推理过程:在主键索引上定位 id=7,落在 5 与 10 之间,未命中任何记录。按优化 2,临键锁退化为间隙锁。

结果:加间隙锁 (5, 10)。插入 id=6 或 id=9 会被阻塞;更新 id=5 与 id=10 的记录不受影响(间隙锁不阻止修改已有记录)。

关键差异是:命中加记录锁,未命中加间隙锁。后者的阻塞对象是"尚不存在的行"。

2.5.3 非唯一索引等值

SELECT * FROM t WHERE c = 5 FOR UPDATE;

推理过程分三步:

  1. 在二级索引 idx_c 上定位 c=5,按原则 1 加临键锁 (0, 5];
  2. 向右遍历到 c=10,该记录不满足 c=5,按优化 2 加间隙锁 (5, 10);
  3. 回表读取聚簇索引,对主键 id=5 的记录加记录锁。

结果:idx_c 上 (0, 5] 加临键锁、(5, 10) 加间隙锁,合并后 (0, 10) 范围内的插入均被阻塞;同时主键索引上 id=5 的行被锁定。

与优化 1 的差别正在于此:非唯一索引无法由索引唯一性推断"只有一行满足条件",因此必须锁住其右侧间隙,防止在该间隙内插入新的 c=5 记录。这就是非唯一索引等值更新比唯一索引等值更新更容易产生阻塞与死锁的原因。

2.5.4 范围查询

唯一索引上的范围查询:

SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;

推理过程:定位起点 id=10,加临键锁 (5, 10];向右扫描,访问到 id=15,该记录不满足 id < 15,但因范围查询会访问到第一个不满足条件的记录,id=15 也会加临键锁 (10, 15]。

结果:实际锁定范围为 (5, 15]。范围查询的锁定范围包含起点记录的左侧间隙,也包含终点之外的第一条记录,这是最容易低估的一处。

非唯一索引上的范围查询:

SELECT * FROM t WHERE c >= 10 AND c < 15 FOR UPDATE;

推理过程:在 idx_c 上定位起点 c=10,加临键锁 (5, 10];向右扫描至 c=15,加临键锁 (10, 15];对扫描到的记录进行回表,在主键索引上加记录锁。

结果:锁定范围为 (5, 15],且回表读取的行在主键索引上被加锁。非唯一索引的范围查询比唯一索引更容易触及额外的行,因为同一索引值可能对应多行,回表次数更多。

2.5.5 无索引条件导致锁范围扩大

UPDATE t SET d = 1 WHERE d = 5;

列 d 上没有索引时,优化器没有可用的索引路径,只能对聚簇索引做全表扫描。按原则 2,扫描过程中访问到的每一条记录都会被加上临键锁;由于扫描覆盖了全部记录,这些临键锁覆盖了整张表的所有记录与所有间隙。

结果:整张表的数据行与间隙全部被锁定,效果等同于锁表。此时其他会话对该表的任意插入、更新都会被阻塞或进入锁等待。

这是"锁范围扩大"的根本原因:加锁范围由访问路径决定,而访问路径由索引决定。索引无法定位边界时,边界就是全表。 常见的退化情形包括:

  1. 条件列上完全没有索引;
  2. 条件列上有索引,但因隐式类型转换、对列使用函数、前导模糊匹配(LIKE '%x')导致索引失效,仍走全表扫描;
  3. 条件使用了复合索引的非最左前缀列;
  4. UPDATE 的 WHERE 条件引用了被更新列本身,与索引选择性判定交互后走了全表扫描。索引失效的各种情形与改写方式见 mysql: 索引与执行计划。

排查此类问题的第一步不是看锁,而是看执行计划确认访问路径(引用 mysql: 使用命令 第九章的 EXPLAIN 用法)。

2.6 死锁

产生条件

死锁需要同时满足四个条件:

  1. 互斥:资源在某一时刻只能被一个事务持有;
  2. 持有并等待:事务已持有资源,同时等待其他资源;
  3. 不可剥夺:已持有的锁不能被强制夺走,只能由持有者释放;
  4. 循环等待:存在一个事务等待链,链首等待链尾所持有的资源。

数据库层能主动干预的是条件 4。消除循环等待的常规手段是让所有事务按相同的顺序访问资源。

最典型的死锁是两个事务以相反顺序更新同一组行:

事务 A                              事务 B
BEGIN                               BEGIN
UPDATE account SET ... WHERE id = 1
                                    UPDATE account SET ... WHERE id = 2
UPDATE account SET ... WHERE id = 2  -- 等待事务 A 释放 id=2
                                    UPDATE account SET ... WHERE id = 1  -- 等待事务 A 释放 id=1
-- 形成循环等待

MySQL 的检测与处理策略

InnoDB 由参数 innodb_deadlock_detect 控制死锁检测,默认为 ON:

  1. 事务在等待锁时,会检查加入该锁的等待队列是否会导致循环等待;
  2. 一旦发现循环,立即选择一个事务作为牺牲者回滚,并向其客户端返回 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction;
  3. 被选中的通常是代价最小的事务,判定依据是它已修改的行数等权重指标;
  4. 其余事务继续执行,不感知发生过死锁。

死锁检测本身有成本。高并发下若大量事务反复进入同一把锁的等待队列,检测的复杂度会随等待者数量上升,可能造成明显的 CPU 消耗,表现为"没有死锁但吞吐下降"。此时可用 innodb_deadlock_detect = OFF 关闭检测,改为完全依赖锁等待超时打破僵局,代价是死锁会以锁等待超时的形式暴露,响应变慢。

锁等待超时的参数

innodb_lock_wait_timeout 控制单个锁等待的时长,默认为 50 秒,单位为秒:

  1. 等待超过该时长后,当前语句被回滚并返回 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction;
  2. 该参数为语句级回滚,默认只回滚当前语句,事务本身不自动回滚,因此需要应用显式回滚,否则事务会带着部分修改继续存在;
  3. 取值需要与业务的最大可等待时间匹配:过大会让请求长时间挂起并占用连接与线程,过小会让正常的长事务等待被误判为故障。

死锁与锁等待的区别

对比项 死锁 锁等待超时
等待关系 循环等待,互相等待对方释放 单向等待,等待某个持有锁的事务结束
触发机制 由死锁检测主动发现并回滚牺牲者 由等待时间超过 innodb_lock_wait_timeout 触发
错误码 1213 / SQLSTATE 40001 1205 / SQLSTATE HY000
事务影响 事务被完整回滚,可安全重试 默认只回滚当前语句,事务可能仍存活且持有锁
是否必然发生 需要特定交错顺序,有一定的概率性 只要持有者不释放就必然发生
处理重点 统一加锁顺序、缩小事务粒度 定位持锁者并缩短其持锁时间

两者的共同点是都会在 SHOW ENGINE INNODB STATUS 中留下记录(死锁记录在 LATEST DETECTED DEADLOCK 段落,锁等待体现在 TRANSACTIONS 段落的 LOCK WAIT 行),处置流程见 3.2 节。

2.7 一致性读与主从延迟的交互

当事务与锁的讨论扩展到读写分离架构,会引入一类单机不存在的可见性问题。

只读从库上的一致性读要求

在从库上执行查询时,MVCC 的快照保证的是"本事务内可见版本的一致性",但前提是该从库已经重放到某个位点。因此存在两层延迟:

  1. 事务内的一致性:由 ReadView 保证,与单机相同;
  2. 副本间的延迟:从库的 SQL 线程尚未执行到主库的某个提交,从库上无论使用何种隔离级别都读不到该提交。

读写分离带来的可见性问题

典型表现是"写入成功但立即查询不到",链路如下:

应用 ── 写 ──► 主库(提交成功,返回成功)
应用 ── 读 ──► 从库(SQL 线程尚未重放该事务)→ 读到旧值

这与缓存不一致的表现相同,定位时必须先区分数据来源:若读请求走的是从库,问题属于复制延迟而非缓存问题。相关讨论见 缓存基础与一致性 第 2.4.5 节。

应对方向

手段 做法 代价
关键读走主库 写后需要立即读取的数据强制路由到主库 主库读压力上升,读写分离的收益被削弱
会话粘滞 同一会话在写入后的一个时间窗内固定读主库 会话状态需要在应用与网关间传递
等待位点 写入后记录位点或 GTID,读前等待从库追平(MASTER_POS_WAIT、WAIT_FOR_EXECUTED_GTID_SET) 增加一次等待,超时需有兜底策略
半同步复制 提交时等待至少一个从库确认已收到 binlog 提交延迟上升,从库故障时可能退化为异步
业务容忍 对可容忍陈旧的数据不做特殊处理,仅对强可见性要求的数据做路由 需要明确哪些数据必须强可见,边界容易随时间失守

复制模式、半同步的等待位置与延迟来源见 mysql:主从同步原理 第 2.4 节与第 2.7 节。

需要注意的是,RR 级别下的可重复读只保证一个事务内部的读一致,不保证跨事务、跨副本的可见性。把可重复读理解为"读到的都是正确的最新数据"是一个常见误解。

2.8 长事务与未提交事务的危害

长事务指执行时间明显偏长(例如超过 60 秒)或长期不提交的事务。它的危害不是单点的,而是沿 MVCC、锁、复制、备份四条链路同时扩散。

undo 膨胀与快照读变慢

如 2.3 节所述,长事务的 ReadView 会钉住 purge 位点。后果是:

  1. undo 表空间持续增长,历史版本无法清理;
  2. 快照读需要沿版本链回溯更长的距离,读延迟上升;
  3. 事务结束后 purge 需要一次性追赶大量积压,期间 IO 压力集中;
  4. 极端情况下 undo 表空间耗尽磁盘,写入受阻。

锁持有时间与阻塞面

事务持有的锁直到提交或回滚才释放。事务越长,其持锁窗口越大:

  1. 阻塞其他会话的写操作,引发锁等待;
  2. 与其他事务的持锁窗口交叠概率上升,死锁概率随之上升;
  3. 若事务中包含 DDL 或涉及元数据读取,还会长期持有 MDL,阻塞表结构变更与后续读写。

复制与备份的影响

  1. 复制:大事务会生成大体积的 binlog 事件,从库需要等整个事件接收完才能开始重放,且单个大事务无法被并行复制拆分,直接造成主从延迟尖峰;延迟成因分类与治理手段见 mysql: 主从部署与运维实践。
  2. 备份:逻辑备份常依赖 --single-transaction 开启一个可重复读事务以获得一致性快照(用法见 mysql: 使用命令 第八章),该事务的生命周期等于整个导出过程,在此期间 purge 被钉住;若备份期间还有业务长事务,两者叠加会显著加重 undo 压力。备份一致性的实现手段与恢复演练见 mysql: 备份与恢复。

连接池与事务边界的常见错误

错误 表现 后果
事务开启后调用远程接口 事务持锁时间由远程调用的响应时间决定 锁被长时间持有,且远程调用成功而事务回滚时数据不一致
连接归还但事务未提交 连接回到连接池时事务仍处于打开状态 下一个业务复用了未结束的事务,锁与修改被意外延续
手动开启事务后异常路径未回滚 异常抛出后事务未显式结束 事务一直存活到连接超时被服务端回滚,期间持续持锁
自动提交被关闭 连接以 autocommit = 0 建立,忘记提交 每一条语句都开启隐式事务且不提交
框架与手动事务混用 在框架事务方法内又执行了 COMMIT 或 BEGIN 事务边界与框架的传播语义冲突,回滚失效

未提交事务的隐蔽性

未提交事务不一定"正在执行"。一个事务可能已经执行完所有语句,只剩提交动作未发出(例如应用线程在提交前阻塞),此时它已经不再消耗 CPU,但依然持有全部锁与 ReadView。因此巡检必须覆盖"空闲但事务未结束"的会话,识别方式见 3.6 节。

三、实现

3.1 观察与诊断手段

诊断的前提是能回答三个问题:当前有哪些事务、每个事务持有哪些锁、谁在等谁。以下以 MySQL 8.0 的表结构为准,版本差异单独说明。

当前活跃事务

SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_mysql_thread_id,
  trx_rows_locked,
  trx_rows_modified,
  trx_isolation_level,
  trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

关键字段的判读:

字段 含义 判读要点
trx_state 事务状态 RUNNING 为执行中;LOCK WAIT 表示正在等待锁;ROLLING BACK 表示正在回滚
trx_started 事务开始时间 与当前时间相减即事务已存活时长,是判定长事务的直接依据
trx_mysql_thread_id 持有该事务的连接 ID 用于关联进程列表与线程表,是执行终止操作的输入
trx_rows_locked 粗略的锁定行数 数值远大于受影响行数时,通常意味着走了全表扫描或范围锁
trx_rows_modified 已修改的行数 用于估算回滚代价,也是死锁牺牲者选择时的权重参考
trx_query 当前语句 为空表示事务空闲,即"语句已执行完但未提交"
trx_isolation_level 事务的隔离级别 用于确认会话是否按预期级别运行

当前的锁与锁结构

SELECT
  ENGINE_TRANSACTION_ID  AS trx_id,
  THREAD_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;

判读要点:

字段 判读要点
LOCK_TYPE TABLE 为表级锁(含意向锁),RECORD 为行级锁
LOCK_MODE X、S 为记录锁;X, GAP 为间隙锁;X, RECORD 为临键锁;X, GAP, INSERT_INTENTION 为插入意向锁
LOCK_STATUS GRANTED 为已持有,WAITING 为正在等待,等待的行即为阻塞点
LOCK_DATA 锁定的索引值。主键上加锁时显示主键值;表示"最后一个记录之后的间隙"时显示 supremum pseudo-record
INDEX_NAME 锁加在哪个索引上。为 PRIMARY 表示加在聚簇索引;为二级索引名表示加在二级索引,通常同时存在聚簇索引上的对应锁

LOCK_DATA 出现 supremum pseudo-record 是需要重点关注的信号:它表示有事务锁住了该索引的末尾间隙,此后所有更大的插入都会等待。

锁等待链

SELECT
  REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  REQUESTING_THREAD_ID             AS waiting_thread,
  BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx,
  BLOCKING_THREAD_ID               AS blocking_thread
FROM performance_schema.data_lock_waits;

该表每一行表示"某事务正在等待某事务",逐行串联即可构造出等待链:

blk_trx 1201 ──持有──► id=1 的行锁
trx 1202   ──等待──► id=1(由 1201 持有)
trx 1202   ──持有──► id=2 的行锁
trx 1203   ──等待──► id=2(由 1202 持有)
链:1203 → 1202 → 1201,链首为 1201,锁住链首即可解开整条链

sys 库提供了整合视图,输出等待者与被阻塞者的线程、SQL 与锁信息:

SELECT * FROM sys.innodb_lock_waits\G

事务与线程的对应关系

三张表通过标识串联,是定位"哪个应用在制造锁等待"的路径:

information_schema.INNODB_TRX.trx_mysql_thread_id
        │
        ▼
performance_schema.threads.PROCESSLIST_ID  ── 取 PROCESSLIST_USER / PROCESSLIST_HOST / PROCESSLIST_INFO
        │
        ▼
information_schema.PROCESSLIST.ID          ── 取 USER / HOST / TIME / STATE / INFO

由此可以得出:制造锁等待的是哪台主机、哪个账号、执行的是哪条语句。

SELECT
  t.PROCESSLIST_ID   AS conn_id,
  t.PROCESSLIST_USER AS user,
  t.PROCESSLIST_HOST AS host,
  t.PROCESSLIST_TIME AS conn_seconds,
  p.TIME             AS state_seconds,
  p.STATE,
  p.INFO
FROM performance_schema.threads t
LEFT JOIN information_schema.PROCESSLIST p ON p.ID = t.PROCESSLIST_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
ORDER BY p.TIME DESC;

版本差异

MySQL 8.0 移除了 information_schema.INNODB_LOCKS 与 INNODB_LOCK_WAITS,由 performance_schema.data_locks 与 data_lock_waits 取代。MySQL 5.7 及更早版本仍使用前者,字段名为 lock_trx_id、lock_mode、lock_type、lock_table、lock_index、lock_data 与 requesting_trx_id、blocking_trx_id。在混合版本环境中排查需要按版本选择表名。

InnoDB 状态输出中的关键段落

SHOW ENGINE INNODB STATUS 输出了事务与锁的即时快照,重点看三段。

mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/^---/p'
段落 内容 判读方式
LATEST DETECTED DEADLOCK 最近一次死锁的两个事务、各自执行的语句、持有的锁与等待的锁 对比两个事务的加锁顺序:若顺序相反且涉及同一组行,即为顺序不一致导致的死锁;若语句中缺少索引候选,多半是缺索引导致的全表锁定
TRANSACTIONS 当前活跃事务列表,含事务 ID、状态、已用时长、MySQL 线程 ID、正在执行的语句、持有与等待的锁 关注 ACTIVE 且时长很长的条目,以及带 LOCK WAIT 标记的条目;条目末尾会列出该事务持有的每把锁
LOCK WAIT(在 TRANSACTIONS 段内) 某个事务正在等待的锁 与同段中其他事务的持锁信息比对,可还原阻塞关系;该段落不会出现循环等待,因为循环会被检测并转化为死锁记录

判定段落的实用方法:先在 LATEST DETECTED DEADLOCK 中找出模式(顺序不一致、缺索引、范围锁),再确认该模式是否在当前活跃事务中重复出现;仅出现一次的历史死锁通常无需专门治理,反复出现则属于结构性问题。

3.2 死锁与锁等待的处置流程

第一步:确认现象类型

从应用侧的错误码区分:

错误信息 类型 事务状态
Deadlock found when trying to get lock; try restarting transaction 死锁 已被完整回滚,事务不存在
Lock wait timeout exceeded; try restarting transaction 锁等待超时 仅当前语句回滚,事务可能仍存活并持锁

两者都提示重试事务,但语义不同:死锁后重试是安全的(原事务已完整回滚);锁等待超时后需要先确认事务是否已被回滚,否则重试会在一个已持锁的事务上叠加新语句。

第二步:定位持锁者与被阻塞者

按 3.1 节的方法依次执行:查 INNODB_TRX 找出长时间运行与 LOCK WAIT 状态的事务,查 data_locks 找出 LOCK_STATUS = 'WAITING' 的行及其 LOCK_DATA,查 data_lock_waits 重建等待链,最后通过线程表关联到主机与账号。

第三步:还原加锁顺序

从 data_locks 中按 ENGINE_TRANSACTION_ID 分组,即可得到每个事务持有的锁集合与等待的锁。若两个事务在相同的索引值集合上以不同顺序获取锁,即为顺序型死锁。这一步的产出应当是具体的语句序列,而不是"存在死锁"这一结论。

第四步:在测试环境复现

复现是验证修复是否有效的唯一方式。用两个会话按定位到的顺序手工执行语句,验证死锁可稳定复现;修改后再验证死锁不再出现。

第五步:修复

按成因对应处理:

成因 修复方向
加锁顺序不一致 统一访问顺序:按主键排序后再批量更新;或把多行更新合并为按固定顺序的单条语句
无索引导致全表锁定 为条件列补索引,使锁定范围从全表收敛到目标行
事务中包含其他操作 把远程调用、消息发送、文件读写移出事务,缩短持锁窗口
范围锁范围过大 缩小查询范围;评估是否需要降级到 RC 以减少间隙锁
非唯一索引的间隙竞争 评估在业务上把该索引改为唯一索引;或改用带条件的单次更新
唯一键冲突与死锁叠加 检查 INSERT ... ON DUPLICATE KEY UPDATE 或 REPLACE 的使用,这类语句在并发下更容易产生死锁(REPLACE 会先删除后插入,涉及两个事务的锁)

紧急情况下的止损

锁等待已经影响到在线请求、且持锁者无法在可接受时间内结束时,才考虑主动终止。

-- 查看待处理的事务与连接
SELECT trx_id, trx_mysql_thread_id, trx_state, trx_started, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 终止指定连接(会先回滚该连接上未提交的事务)
KILL 12345;

-- 仅终止当前语句,不结束事务
KILL QUERY 12345;

高危 提醒:终止连接与终止事务属于不可逆操作。KILL 会触发该事务的回滚,回滚代价与被修改的行数成正比,大事务回滚可能持续数十秒至数分钟,期间持续占用 IO 并可能加剧主从延迟与 undo 增长;KILL 掉的是连接而非单个锁,若该连接上还叠加了其他业务操作,一并丢失;误杀备份会话或 DDL 会话会造成备份中断、DDL 回滚等次生问题;若被终止的是从库会话,还可能影响复制状态。兜底方式:优先终止等待链的链首(持有锁而不等待他人的事务),而不是终止全部等待者——终止链首即可解开整条链;执行前先确认该连接对应的应用与业务,排除备份、复制、DDL、运维会话;优先尝试 KILL QUERY 而非 KILL,前者只中断当前语句;对确有必要的连接终止,记录连接 ID、账号、主机与当时的 SQL,用于事后追溯;若阻塞源是备份或 DDL,改为等待其自然结束或与执行方协调,不做强制终止。

应用侧的重试设计

死锁与锁等待在并发系统中不可能被完全消除,应用必须能承接这类错误并恢复:

  1. 重试范围:仅对死锁(1213 / 40001)与锁等待超时(1205)重试,不对语法错误、约束冲突、连接错误做无差别重试;
  2. 重试粒度为整个事务:死锁已回滚整个事务,重试必须从事务的第一条语句开始,不能只重试失败的语句;
  3. 重试前必须完整回滚:锁等待超时不自动回滚事务,重试前须显式 ROLLBACK,否则新事务的语句会叠加在旧事务之上;
  4. 重试次数与退避:设置有限次数(如 3 次)并叠加随机退避,避免多个事务同步重试再次碰撞;退避时间应大于一次事务的平均执行时长;
  5. 重试必须幂等:事务内的写入需要能承受整体重放,依据是业务唯一键或状态机,重复执行的判定方式见 消息可靠性与幂等设计;
  6. 重试需要可观测:统计重试次数与最终失败率,重试率上升通常意味着并发冲突加剧,属于需要治理的信号而不是可忽略的噪声。
// 死锁与锁等待的重试骨架:整事务重放、有限次重试、随机退避
public <T> T executeWithRetry(Supplier<T> action, int maxAttempts) {
    for (int attempt = 1; ; attempt++) {
        try {
            return action.get();   // action 内部为完整事务:BEGIN ... COMMIT
        } catch (Exception e) {
            boolean retryable = isDeadlock(e) || isLockWaitTimeout(e);
            if (!retryable || attempt >= maxAttempts) {
                throw e;
            }
            // 退避后再从第一条语句重新开始整个事务
            sleepBackoff(attempt);
        }
    }
}

3.3 事务边界规范

事务边界决定锁的持有时间,是并发问题中收益最高的治理点。

短事务设计

  1. 事务内只做数据库操作,且只包含必须原子生效的语句;
  2. 事务开启后立即执行语句,执行完立即提交,不在事务中等待外部条件(用户输入、定时器、远程响应);
  3. 超过约定时长(例如 100 毫秒)的事务应能通过监控发现,见 3.6 节;
  4. 事务的粒度按业务变更的最小原子单位确定,而不是按"一次请求"确定。

避免在事务中做远程调用与耗时操作

远程调用的响应时间不可控,把调用放在事务内的后果是锁持有时间由外部系统决定,同时引入数据不一致的风险(远程成功而本地回滚)。应移出事务的操作包括:

操作 移出方式
HTTP / RPC 调用 事务提交后执行;或先把结果落库,由异步任务调用
消息发送 采用本地消息表或事务消息,使消息记录与业务变更同一事务提交,投递在事务之后,方案见 消息一致性:事务消息与最终一致性
缓存更新 事务提交后删除缓存,顺序与失效策略见 缓存基础与一致性 第 2.4.1 节
文件读写、日志落盘、报表导出 移出事务,改为异步任务
大批量数据处理 拆分为多个短事务,见下文的批量操作

批量操作的分批提交

一次性更新或删除大量数据会同时造成三类问题:事务过大(undo 与 redo 膨胀)、持锁时间过长(阻塞面大)、主从延迟(大事务无法并行重放)。分批提交是常见处置。

-- 分批删除:每批处理固定行数后提交,批之间让出资源
DELETE FROM t
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM t WHERE created_at < '2026-01-01' LIMIT 2000
  ) AS tmp
);

高危 提醒:把原本单个事务的批量操作改为分批提交,等于放弃整体原子性。中途失败或进程崩溃时,已提交的批次不会回滚,数据会停留在"部分完成"的中间状态;若业务下游依赖"全有或全无",会造成对账差异。兜底方式:为批次设计可重入的判据,例如以状态字段(待处理 / 已处理)或业务唯一键驱动,使任务可从上次中断处继续且重复执行不产生副作用;每批提交后记录进度位点,失败后按位点续跑而不是从头重来;任务完成后做一次数量核对(源表剩余行数与目标修改行数);对必须整体生效的操作不使用分批提交,改为在单事务内完成并接受其代价,或把整批操作改为异步任务并在业务上允许中间态。

分批的批大小需按实测调整,判定依据是单批的持锁时间与主从延迟是否在可接受范围内;批次之间加短间隔可以让复制与 purge 有机会追赶。

隔离级别的选择建议

业务特征 建议级别 理由
常规互联网业务,以单行或小范围读写为主 读已提交(RC) 不加重间隙锁,锁定范围小,死锁概率低,主从一致性更好
需要事务内多次读结果稳定(对账计算、报表快照) 可重复读(RR) 依赖事务内一致的 ReadView
逻辑备份、一致性数据导出 可重复读(RR) --single-transaction 依赖 RR 快照
依赖 STATEMENT 格式 binlog 的复制 可重复读(RR) 语句级复制需要可重复读语义才能保证从库重放一致
极强一致性、并发极低的场景 串行化(SERIALIZABLE) 代价是读写互斥,吞吐显著下降,一般不建议

修改级别的操作方式与影响面见 2.2 节的高危提示。需要强调的是,从 RR 改为 RC 会让依赖"先查询判断、再更新"的逻辑失去保护(两次读之间数据可能已变化),这类逻辑应改为带条件的更新语句,而不是依赖隔离级别。

只读事务的声明

明确只读的事务应显式声明,避免不必要的开销:

START TRANSACTION READ ONLY;

SELECT ...;

COMMIT;

只读事务不会获得事务 ID(因此不参与 ReadView 的活跃事务列表,也不影响 purge),可减少对 undo 清理的干扰;同时不产生 binlog 中的写事件。其限制是不能在事务内执行写操作。

注意:SET autocommit = 0 会让每条语句都成为隐式事务,若连接被长期持有而不提交,会造成持锁与 undo 膨胀。在连接池配置中应确保连接归还前完成提交或回滚。

3.4 应用层的并发控制

当业务需要"读取判断后写入"的原子性时,隔离级别本身不能提供保证,需要显式选择并发控制方式。

悲观锁(SELECT ... FOR UPDATE)

在读取时就锁定目标行,使其他事务无法修改,直到本事务提交。

START TRANSACTION;

-- 必须走索引,否则锁定范围会退化为全表
SELECT qty FROM stock WHERE sku = 'SKU-001' FOR UPDATE;

-- 在已持锁的前提下判断并更新,判断与写入之间不会被其他事务插入修改
UPDATE stock SET qty = qty - 1 WHERE sku = 'SKU-001';

COMMIT;

实现要点:

  1. 必须有索引,且条件应命中索引;否则按 2.5.5 节的规则,锁定范围会扩大到全表;
  2. FOR UPDATE 是当前读,读取最新值,与快照读的语义不同(见 2.3 节);
  3. 在 RR 级别下等值未命中会加间隙锁,可能阻塞其他会话的插入,需要注意其副作用;
  4. 锁在事务结束时释放,因此持锁时间等于事务剩余时长,应尽量缩短;
  5. 需要"获取失败立即返回"或"跳过已锁行"的语义时可用 SELECT ... FOR UPDATE NOWAIT 或 SKIP LOCKED(后者适用于队列类消费场景)。

乐观锁(版本号)

不加锁读取,写入时以版本号为前置条件,通过影响行数判断是否成功。

-- 表结构包含版本列
-- ALTER TABLE stock ADD COLUMN version INT NOT NULL DEFAULT 0;

-- 读取当前值与版本号
SELECT qty, version FROM stock WHERE sku = 'SKU-001';
-- 应用侧判断 qty > 0

-- 带版本条件更新:影响行数为 1 表示成功,为 0 表示期间已被他人修改
UPDATE stock
SET qty = qty - 1, version = version + 1
WHERE sku = 'SKU-001' AND version = ? AND qty > 0;

实现要点:

  1. 更新语句必须同时带版本条件与业务条件,两者缺一都会失效;
  2. 判定成功的唯一依据是影响行数,而不是异常;
  3. 冲突时的处理是重试(重新读取并重新计算),因此实现必须幂等且重试有上限;
  4. 版本列需要随每次修改自增,不能只依赖业务字段比较;
  5. 高冲突场景下重试次数会显著上升,此时乐观锁的成本可能高于悲观锁。

两种方式的取舍

维度 悲观锁(FOR UPDATE) 乐观锁(版本号)
加锁时机 读取时即加锁 写入时才校验
冲突应对 后来的事务等待或超时 失败重试
读并发 同一行上的排他锁读会互相等待 读取不加锁,读并发高
适用冲突概率 高冲突 低冲突
事务时长敏感度 高,持锁时间直接等于事务时长 低,无锁等待
实现复杂度 低 中,需处理重试与幂等
主要风险 锁等待、死锁、锁范围扩大 重试风暴、更新丢失判定错误

选择依据是冲突概率与重试代价:冲突概率低时乐观锁的失败重试很少,收益明显;冲突概率高时大量重试会放大数据库负载,此时悲观锁的排队反而更可控。

幂等与重复提交的防护

并发控制解决的是"同时修改",幂等解决的是"同一请求被执行多次"。两者需要同时具备。

手段 实现 适用
唯一键 业务单号或(业务单号 + 动作)上建唯一索引,重复插入被拦截 创建类操作
状态机 更新时带前置状态条件(WHERE status = '待处理'),只有合法的状态迁移能成功 状态推进类操作
去重表 记录已处理的请求标识,处理前先插入 无自然唯一键的场景
请求令牌 提交前申请一次性令牌,提交时携带并核销 用户重复点击
分布式锁 跨实例互斥,锁的获取与续期实现见 Redis 缓存实践与常见问题 跨进程的临界区,但不宜作为唯一防线

库存扣减类场景的方案对比

库存是"判断加修改"的典型场景,多种方案并存,取舍如下。

方案 实现要点 超卖风险 冲突表现 适用场景
条件更新 UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty > 0,以影响行数判定 无(判断与修改在同一条语句内,且持行锁) 失败者影响行数为 0,直接返回库存不足 单库、要求不超卖、实现最简,多数场景的首选
悲观锁 FOR UPDATE 读取后判断,再更新 无(需保证锁覆盖判断与更新) 后来的操作等待,可能锁等待超时 判断逻辑复杂、需要读取多个字段参与判定
乐观锁 读取版本号,带版本与数量条件更新,失败重试 无 冲突时重试,高并发下重试次数上升 冲突概率低、读取与写入之间有业务计算
缓存预扣减加异步落库 缓存中原子扣减,成功后再异步写库 有(缓存与库不一致期间可能出现偏差) 缓存层承担全部并发 秒杀类瞬时高并发、可容忍最终一致与少量超卖,缓存方案见 缓存基础与一致性
分布式锁 以锁服务串行化同一 SKU 的扣减 取决于锁的可靠性 锁等待 跨库、跨服务的临界区;成本高,一般作为兜底而非首选

结论上的规律是:凡是"判断与修改在同一条更新语句内完成"的方案,都不需要额外的锁机制,其正确性由数据库的行锁与语句原子性保证,冲突时的表现是一个明确的影响行数(0 或 1),而不是异常。这类方案实现成本最低、可靠性最高。跨服务场景下的库存与订单协同见 消息一致性:事务消息与最终一致性 第 3.6 节。

3.5 典型问题案例

3.5.1 死锁:不同顺序更新同一组行

现象:批量转账或批量更新任务在并发下偶发 Deadlock found when trying to get lock。

成因:两个事务以相反顺序访问同一组行。

事务 A:UPDATE account SET balance = balance + 10 WHERE id = 1;
        UPDATE account SET balance = balance - 10 WHERE id = 2;

事务 B:UPDATE account SET balance = balance + 10 WHERE id = 2;
        UPDATE account SET balance = balance - 10 WHERE id = 1;

定位:SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段落中,两个事务的语句涉及相同的行但顺序相反。

处置:

  1. 统一访问顺序:在应用层对涉及的 ID 排序后再逐条更新;
  2. 用单条语句完成同一组行的更新(例如 WHERE id IN (...) 且排序固定),使加锁顺序由数据库决定;
  3. 缩短事务,减少两个事务持锁窗口的重叠。
// 统一加锁顺序:对涉及的标识排序后再依次加锁,消除循环等待
List<Long> ids = new ArrayList<>(accountIds);
Collections.sort(ids);
for (Long id : ids) {
    accountMapper.lockById(id);   // SELECT ... FOR UPDATE
}

3.5.2 锁等待超时:无索引更新

现象:更新某表的语句偶发 Lock wait timeout exceeded,且阻塞范围明显超出预期;INNODB_TRX 中该事务的 trx_rows_locked 数值很大。

成因:更新条件列上没有可用索引,优化器走全表扫描,扫描到的每条记录都被加上临键锁,锁定范围扩大到全表(见 2.5.5 节)。

-- d 列无索引
UPDATE t SET status = 2 WHERE d = 5;

定位:performance_schema.data_locks 中该事务的锁定条目数量与表的总行数量级相当,且 LOCK_TYPE 为 RECORD 的条目覆盖大量不同的 LOCK_DATA 值;EXPLAIN 显示 type = ALL。

处置:

  1. 为条件列补索引,或用已有索引改写条件;
  2. 检查是否存在隐式类型转换(字符串列与数字比较、字符集不一致)导致索引失效;
  3. 大表上的这类更新同时考虑分批执行。
-- 确认访问路径,type 为 ALL 表示全表扫描
EXPLAIN UPDATE t SET status = 2 WHERE d = 5;

3.5.3 幻读:范围插入

现象:同一事务内两次执行相同的范围统计,行数不同。

成因:快照读读到的是生成 ReadView 时的版本,但若中间执行了当前读(例如 UPDATE 或 SELECT ... FOR UPDATE),后续读会看到新提交的数据,形成"幻影行"。

定位:确认事务内是否混用了快照读与当前读;确认隔离级别是 RC 还是 RR。

处置:

  1. 需要"判断与写入"原子化时,不要依赖查询结果,改用带条件的更新语句(使判断与修改在同一语句内完成)或显式加锁的范围读;
  2. 需要防止范围内的新插入时,在 RR 级别下使用 SELECT ... FOR UPDATE 对范围加临键锁,或在 RC 级别下用唯一索引配合业务约束阻止重复插入;
  3. 统计类查询应放在独立的事务或只读事务中执行,避免与写操作混在同一事务。
-- 判断与写入分离:并发下判断依据可能已失效
SELECT COUNT(*) FROM orders WHERE user_id = 100 AND status = 'PENDING';  -- 得到 0
INSERT INTO orders (user_id, status) VALUES (100, 'PENDING');            -- 并发下可能插入多条

-- 改为由当前读锁定范围,或由唯一索引与条件更新保证:
SELECT id FROM orders WHERE user_id = 100 AND status = 'PENDING' FOR UPDATE;

3.5.4 间隙锁导致的插入阻塞

现象:插入语句长时间等待,但对插入目标行的直接更新不受影响;data_locks 中出现 LOCK_MODE = 'X, GAP' 且 LOCK_DATA 为间隙或 supremum pseudo-record。

成因:另一个事务在 RR 级别下执行了未命中的等值查询或范围查询,持有间隙锁;插入时申请插入意向锁与之冲突(见 2.4.4 节)。

定位:在 data_locks 中找到 LOCK_STATUS = 'WAITING' 且 LOCK_MODE 含 INSERT_INTENTION 的行,其 LOCK_DATA 即被锁住的插入位置;再查 data_lock_waits 找到持锁者。

处置:

  1. 缩短持锁事务,尤其是"查询后长时间未提交"的事务;
  2. 若业务不依赖可重复读语义,评估把级别改为 RC 以去掉间隙锁;
  3. 检查持锁事务的查询条件是否可以用唯一索引等值命中替代范围查询,从而把锁从间隙收窄到记录。

3.5.5 隐式提交导致的事务中断

现象:事务中的一部分写入已生效,另一部分未生效,且应用未收到明确的事务失败信号。

成因:部分语句会隐式提交当前事务,使事务边界被意外截断。常见情形:

语句类型 具体语句 说明
DDL ALTER TABLE、CREATE、DROP、TRUNCATE、RENAME、GRANT、REVOKE 执行前隐式提交当前事务
锁表语句 LOCK TABLES、UNLOCK TABLES 隐式提交
管理语句 ANALYZE TABLE、OPTIMIZE TABLE、FLUSH、RESET 隐式提交
复制相关 CHANGE MASTER TO、START SLAVE、STOP SLAVE 隐式提交
事务控制 在事务中再次执行 START TRANSACTION 或 BEGIN 隐式提交前一个事务
客户端行为 autocommit 由 0 改为 1 隐式提交

定位:核对事务内的语句序列,确认是否包含上表中的语句;检查应用的连接池配置是否在会话池化时改变了 autocommit。

处置:

  1. 事务内只保留 DML,DDL 单独在事务外执行;
  2. 不在事务中调用可能触发 DDL 的框架方法(如某些 ORM 的自动建表、表结构校验);
  3. 检查框架的事务传播行为与手动事务控制是否冲突,避免在框架事务方法内再执行显式 COMMIT。

框架层面的边界

Spring 的 @Transactional 通过代理实现,因此同类内部的方法调用不会经过代理,事务注解不生效;rollbackFor 默认只对运行时异常回滚,受检异常需要显式声明;事务方法内捕获异常而不抛出会导致框架认为执行成功而提交。这些行为与具体框架的传播、回滚规则相关,机制说明见 Spring Framework 核心体系,工程结构见 Spring Boot 主脉络。

3.6 监控与治理

锁相关的监控项

-- 行锁等待统计
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 死锁累计次数
SHOW STATUS LIKE 'Innodb_deadlocks';
指标 含义 关注方式
Innodb_row_lock_current_waits 当前正在等待行锁的操作数 瞬时值,持续大于 0 说明存在阻塞,应下钻到具体事务
Innodb_row_lock_waits 累计等待次数 观察增速,与业务量对比判断是否异常
Innodb_row_lock_time 累计等待总时长(毫秒) 与等待次数相除得到平均等待时长
Innodb_row_lock_time_max 单次最长等待时长 用于确认最坏情况
Innodb_deadlocks 累计死锁次数 增长即需要定位,频率上升说明结构性问题
History list length undo 历史版本链长度 持续增长说明有事务钉住 purge,指向长事务

SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 段落与 INNODB_TRX 中的事务存活时长也应作为监控对象,采集方式与告警阈值设计可参考 linux: 监控与告警 与 linux: SLO 与告警设计。

长事务的巡检方式

-- 超过 60 秒仍存活的事务:包含空闲但未提交的事务
SELECT
  trx_id,
  trx_mysql_thread_id        AS conn_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_rows_locked,
  trx_rows_modified,
  trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

巡检需要区分三类事务,处置方式完全不同:

类别 识别特征 处置
业务长事务 有明确的应用主机与语句,反复出现 从代码层面缩短事务边界,属于治理对象
空闲未提交事务 trx_query 为空,连接存在但无语句执行 多为应用未提交或未回滚,需检查连接池与异常路径
系统事务 来自备份、DDL、复制、监控采集等运维会话 属于预期行为,不做干预;备份事务的存活时长即为备份耗时

清理边界必须明确:在未确认事务来源前不做终止操作。

高危 提醒:批量终止长事务(例如按巡检结果统一 KILL)会把备份、DDL、数据迁移、监控采集一并杀掉,造成备份中断、DDL 回滚、迁移任务失败,且大事务回滚会持续产生 IO 压力与主从延迟;终止动作本身不可撤销。兜底方式:先按账号与主机过滤出应用会话,排除备份与运维账号;逐条确认对应业务后再终止,不做批量终止;优先用 KILL QUERY 中断语句;对反复出现的业务长事务,改为从代码侧修复事务边界,而不是持续用终止手段对抗;每次终止记录连接 ID、账号、主机、SQL 与处理人,形成可追溯的记录;处置流程的分级与可逆性设计可参考 linux: 变更与发布管理。

上线前的 SQL 与事务边界评审清单

检查项 判定方式 不通过的后果
更新与删除语句是否走索引 EXPLAIN 确认 type 不是 ALL,且 key 不为空 锁定范围扩大到全表,阻塞面失控
条件列是否存在隐式转换或函数包裹 检查列类型与参数类型、字符集是否一致 索引失效,退化为全表扫描
事务内是否包含远程调用、消息发送、文件读写 逐条检视事务方法体 持锁时间不可控,长事务与不一致风险
事务内是否包含 DDL 或隐式提交语句 检视语句序列,见 3.5.5 节 事务边界被截断,出现部分生效
批量操作是否有分批与续跑设计 确认批大小、进度记录与幂等判据 大事务造成主从延迟,中途失败无法恢复
多行更新是否统一加锁顺序 确认是否对标识排序后访问 顺序型死锁
更新是否使用带条件的单条语句 检查是否存在"先查询判断、再更新" 并发下判断失效,出现超卖或重复写入
关键写入是否有幂等保证 确认唯一键或状态机条件 重试或重复提交产生重复副作用
是否声明只读事务 确认只读查询是否用 START TRANSACTION READ ONLY 无谓的事务 ID 与 purge 干扰
死锁与锁等待是否有重试 确认重试范围、次数、退避与幂等 报错直接抛给用户,业务成功率下降
连接池是否保证归还前结束事务 检查提交与回滚的异常分支 未提交事务被复用,锁与修改意外延续
新增索引是否与既有查询的加锁范围匹配 评估索引变更对既有加锁范围的影响 索引变更后锁定范围改变,阻塞与死锁表现变化

巡检与健康检查的通用流程可参考 linux: 巡检与健康检查,故障处置的经验归集可参考 linux: 故障案例库。

四、总结

起源层面,并发读写产生脏读、不可重复读、幻读三类问题,三者针对的对象不同:脏读针对未提交的修改,不可重复读针对已存在行的内容变化,幻读针对结果集的边界变化。消除问题的强度与并发度成反比,实现路径分为加锁与多版本两种,锁的代价是并发度,多版本的代价是存储与清理开销。InnoDB 的组合是行级锁负责写与写的互斥,MVCC 负责读与写互不阻塞。

原理层面可归纳为四条主线。

其一,ACID 的实现手段是明确的:undo log 支撑原子性并兼顾 MVCC 的旧版本读取,redo log 通过预写式日志支撑持久性,锁与 MVCC 共同支撑隔离性,而一致性由前三者叠加业务约束保证。提交的代价集中在日志刷盘(由 innodb_flush_log_at_trx_commit 与 sync_binlog 控制)与 redo、binlog 的两阶段提交上;回滚的代价与被撤销的修改量成正比。

其二,隔离级别的差异集中在两处:快照生成时机(RR 事务内复用一份 ReadView,RC 每次快照读重新生成)与加锁范围(RR 使用临键锁,RC 不使用间隙锁)。这两处差异决定了不可重复读与幻读是否出现,也决定了死锁概率与主从一致性的表现。可重复读只保证事务内部的读一致,不保证跨副本的可见性。

其三,加锁规则可由两条原则(基本单位是临键锁、只锁访问到的对象)与两条优化(唯一索引等值命中退化为记录锁、等值未命中退化为间隙锁)推导得出,另有一条边界行为(唯一索引范围查询会访问到第一个不满足条件的记录)。加锁范围由访问路径决定,因此索引缺失或失效会使锁定范围扩大到全表,这是锁问题的首要排查方向。间隙锁不与其他间隙锁冲突但会阻塞插入意向锁,是"插入被阻塞"的直接原因。

其四,长事务的危害沿四条链路扩散:钉住 purge 导致 undo 膨胀与快照读变慢、持锁时间延长导致阻塞与死锁概率上升、大事务造成复制延迟、备份期间的长快照事务加重 undo 压力。死锁由循环等待造成,由死锁检测回滚代价最小的事务解决;锁等待超时是单向等待超限,只回滚当前语句,两者需要区分处理。

实现层面的关键结论如下。诊断路径是从事务(INNODB_TRX)到锁(data_locks)到等待关系(data_lock_waits)再到线程与主机(threads、PROCESSLIST),SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 与 TRANSACTIONS 段落提供即时快照。处置流程是确认现象类型、定位持锁者、还原加锁顺序、在测试环境复现、按成因修复,终止操作只作为最后手段且优先终止等待链的链首。事务边界规范的收益最高:事务内只做数据库操作,远程调用与消息发送移出事务,批量操作分批提交(放弃整体原子性时须有续跑与核对设计),明确只读的事务显式声明为只读。并发控制的结论是"判断与修改在同一条更新语句内完成"的方案不需要额外锁机制,冲突概率低时用乐观锁、高时用悲观锁,幂等与并发控制需要同时具备。

边界层面需要明确三点:本文讨论的是单实例单库内的本地事务,跨数据源的原子性由分布式事务方案承担;缓存与数据库的一致性窗口属于缓存失效顺序问题,不应与数据库事务隔离混为一谈;读写分离下的"写后读不到"来自复制延迟,其表现与缓存不一致相同,定位时应先区分读路径的数据来源。相关引用见 mysql: 使用命令(操作与诊断命令)、mysql:关于 MySQL 服务配置(参数配置)、mysql: 视图(视图与只读查询组织)、mysql:主从同步原理(复制与延迟)、消息一致性:事务消息与最终一致性(跨服务一致性)、缓存基础与一致性(缓存一致性窗口)。