数据库基础体系 · 第 8/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MVCC、锁与死锁：可见性、锁粒度、等待图和线上诊断

MVCC、锁和死锁解决的是并发控制中的不同问题：

• MVCC决定一个事务能看见哪些版本的数据；
• 锁限制哪些并发操作可以同时进行；
• 死锁描述锁等待关系形成环路后的不可继续状态；
• 等待图把“谁持有锁、谁等待锁”表示成有向图，是诊断死锁和长时间阻塞的基础。

它们不是相互替代的机制。MVCC可以减少读写之间的阻塞，但写写冲突、锁定读、索引范围保护和约束检查仍然需要锁。

本文以 PostgreSQL 当前稳定版本公开语义和 MySQL 8.4 InnoDB 公开语义为范围。除非特别说明，示例均明确数据库、存储引擎、事务边界和隔离级别。

---

一、先建立三个层次：版本、锁与等待

1.1 版本回答“我应该看见什么”

设某行在事务历史中产生了多个版本：

v0 -- T1 更新 --> v1 -- T2 更新 --> v2

一个事务读取该行时，不一定读取物理上最新的 v2，而是根据自己的快照判断：

1. 哪些创建该版本的事务已经提交；
2. 哪些创建该版本的事务在自己的快照中不可见；
3. 哪些删除或替换该版本的事务对自己可见；
4. 最终沿着版本链找到第一个可见版本。

因此，“数据库里当前值是多少”和“这个事务此刻能读到什么值”不是同一个问题。

1.2 锁回答“谁可以同时修改或锁定”

锁通常保护某种资源，例如：

• 表；
• 索引记录；
• 索引键之间的间隙；
• 某一行的逻辑对象；
• 序列化检查所需的谓词范围；
• 数据库内部元数据。

锁有两个基本属性：

• 锁模式：例如共享、排他、意向、更新或范围保护；
• 锁对象：例如某张表、某个索引记录或某个键范围。

两个事务是否冲突，取决于它们的锁模式和锁对象是否满足冲突关系。

1.3 等待回答“为什么现在不能继续”

如果事务 T1 持有资源 R 的排他锁，事务 T2 请求同一资源的冲突锁，那么：

T2 --等待--> T1

如果 T1 也在等待 T2 持有的资源：

T1 --等待--> T2
T2 --等待--> T1

这就是最简单的死锁。

需要注意，等待不一定是死锁。如果等待链是：

T3 --> T2 --> T1

且 T1 最终会提交，等待可以解除；只有等待图中存在环，才构成死锁。

---

二、MVCC：同一行为什么可以同时存在多个逻辑版本

2.1 MVCC的核心目标

MVCC，即 Multi-Version Concurrency Control，多版本并发控制，核心做法是：

写入不直接让所有读者立即看到新值，而是保留足够的旧版本信息，让每个事务依据自己的快照读取一致的数据。

它主要解决读写并发问题。

例如：

初始值：balance = 100

T1：UPDATE account SET balance = 80
T2：SELECT balance FROM account

如果 T1 尚未提交：

• 采用锁阻塞的实现，T2 可能等待 T1 释放锁；
• 采用 MVCC 一致性读的实现，T2 通常可以读取提交前的旧版本 100。

但这不代表 MVCC允许两个事务无条件地同时修改同一行。两个写事务仍然必须对同一逻辑数据进行协调。

2.2 快照不是“复制一份完整数据库”

快照通常不是把整个数据库复制一遍，而是记录一个可见性边界。一个抽象快照可以表示为：

S = (active, next)

其中：

• active：创建快照时仍在运行的事务集合；
• next：当时尚未分配或尚未进入可见范围的事务边界；
• 其余已经提交的事务通常可以被视为候选可见事务。

对一个由事务 x 创建的版本 v，可以抽象地写成：

visible(v, S) =
    committed(x)
    且 x 不在 S.active
    且 x 的事务序号早于 S.next

实际数据库还要处理：

• 当前事务自己写入的版本；
• 删除版本；
• 回滚事务；
• 子事务；
• 版本链；
• 清理旧版本；
• 未提交事务提交后的可见性变化。

因此，上式是帮助理解的形式化模型，不是 PostgreSQL 或 InnoDB 内部结构的完整定义。

2.3 可见性判断的关键不在“物理顺序”

常见错误是认为：

物理文件中最后写入的版本就是最新版本，所以查询应该读它。

实际上，数据库必须先根据快照判断版本是否可见，再决定是否沿版本链回溯。一个后来提交的版本，对已经建立的旧快照可能仍然不可见。

---

三、PostgreSQL中的MVCC：元组版本与语句快照

3.1 PostgreSQL通过元组版本实现可见性

PostgreSQL表中的一行不是简单地原地覆盖。更新通常会产生新的元组版本，旧版本在不再被任何事务需要后，才可以由 VACUUM 清理。

每个元组包含与事务可见性有关的系统信息，最重要的是：

• xmin：创建该元组版本的事务；
• xmax：删除或替换该元组版本的事务，尚未删除时通常为空或等价状态。

抽象地看：

旧元组：xmin = 10, xmax = 20
新元组：xmin = 20, xmax = 空

如果事务 T20 更新了旧元组，那么旧元组对某些快照不可见，新元组对另一些快照可见。

这不是应用表中显式定义的列，通常通过系统列可以观察到：

SELECT
    xmin,
    xmax,
    ctid,
    id,
    balance
FROM account
WHERE id = 1;

前提是表中存在 id 和 balance 列。ctid 是物理元组位置，不应作为稳定业务标识；更新后它可能改变。

3.2 PostgreSQL READ COMMITTED是“每条语句一个快照”

PostgreSQL 默认隔离级别是 READ COMMITTED。在这个级别下：

每条 SQL 语句开始时建立自己的快照。

示例：

-- PostgreSQL
CREATE TABLE account (
    id      integer PRIMARY KEY,
    balance integer NOT NULL
);

INSERT INTO account VALUES (1, 100);

连接 A：

BEGIN;

UPDATE account
SET balance = 80
WHERE id = 1;

-- 暂不提交

连接 B：

BEGIN;

SELECT balance FROM account WHERE id = 1;

在普通 SELECT 下，B通常读到：

100

因为A的更新尚未提交，B的快照看不到A创建的新版本。

接着，A提交：

COMMIT;

B再次执行：

SELECT balance FROM account WHERE id = 1;

这一次通常读到：

80

原因不是B的事务重新开始了，而是第二条 SELECT 建立了新的语句快照。

3.3 PostgreSQL REPEATABLE READ是“事务级快照”

在 PostgreSQL 中：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

事务第一次需要快照时建立事务级快照，后续普通查询继续使用同一可见性边界。

因此，连接 B：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM account WHERE id = 1;
-- 读到 100

即使连接 A随后提交了 balance = 80，B再次执行：

SELECT balance FROM account WHERE id = 1;

仍然可能读到：

100

这正是可重复读的含义：同一事务中的一致性读不因为其他事务后来提交而改变。

但“快照稳定”不等于“所有并发写都可以成功”。如果B试图更新一个已经被其他事务修改且与其快照冲突的行，PostgreSQL可能报告序列化失败，要求应用回滚并重试。

3.4 PostgreSQL的普通读和锁定读不同

普通查询：

SELECT balance
FROM account
WHERE id = 1;

通常是MVCC一致性读，不需要等待另一个事务对该行持有的普通行锁。

锁定读：

SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;

则表示：

1. 读取一个符合条件的可见行；
2. 请求该行的更新锁；
3. 如果其他事务持有冲突行锁，则等待；
4. 在 READ COMMITTED 下，等待结束后可能重新检查并处理已经变化的行。

因此，FOR UPDATE不是“更强的普通SELECT”，而是把读取和后续修改意图绑定到锁协议中。

---

四、InnoDB中的MVCC：Undo版本链与一致性读

4.1 InnoDB通常在索引记录上加锁，在Undo中保存旧版本

MySQL 8.4中，InnoDB通过以下机制提供事务并发控制：

• 聚簇索引记录保存当前版本；
• 更新前的旧值通过Undo日志保留；
• 一致性读根据Read View判断当前版本是否可见；
• 如果当前版本不可见，就沿Undo版本链读取较老版本；
• 旧Undo在不再被活跃事务需要后才能清理。

因此，InnoDB的旧版本通常不是表中并排存放的普通记录，而是通过Undo信息重建。

4.2 InnoDB普通SELECT通常是一致性非锁定读

在InnoDB中，以下语句是典型的一致性读：

SELECT balance
FROM account
WHERE id = 1;

而以下语句是锁定读：

SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;

或：

SELECT balance
FROM account
WHERE id = 1
FOR SHARE;

锁定读读取当前版本，并且请求锁；普通一致性读读取符合Read View的版本，通常不因为普通行锁而等待。

4.3 MySQL REPEATABLE READ与READ COMMITTED的快照差异

InnoDB常见的行为可以概括为：

• REPEATABLE READ：事务内一致性读通常复用同一个Read View；
• READ COMMITTED：每次一致性读通常建立新的Read View；
• 锁定读读取当前版本，不按普通一致性读的旧快照返回历史值。

示例：

连接 A：

START TRANSACTION;

UPDATE account
SET balance = 80
WHERE id = 1;

连接 B，在InnoDB和默认 REPEATABLE READ 下：

START TRANSACTION;

SELECT balance FROM account WHERE id = 1;
-- 可能返回 100

A提交：

COMMIT;

B再次执行普通查询：

SELECT balance FROM account WHERE id = 1;
-- 在同一一致性读视图下仍可能返回 100

但B执行：

SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;

这是锁定读，会尝试读取当前版本并请求锁。它不是把普通快照中的 100 加上一把锁，而是使用另一套“当前读”语义。

4.4 “当前读”和“一致性读”必须区分

在InnoDB中，以下语句通常属于当前读或包含当前读语义：

SELECT ... FOR UPDATE;
SELECT ... FOR SHARE;
UPDATE ...;
DELETE ...;

它们需要基于当前索引记录状态进行并发判断，而不是仅仅返回旧版本。

一个常见误解是：

MVCC让UPDATE也能读取旧版本，所以UPDATE不需要等待。

实际上，UPDATE必须决定它要修改的当前记录，并且需要取得写锁。如果另一个事务已经修改该记录，UPDATE可能等待。

---

五、隔离级别：可见性规则与异常现象

隔离级别描述的是事务之间允许观察到哪些并发现象，而不是一个跨数据库完全相同的实现协议。至少需要区分：

• 脏读：读到其他事务尚未提交的数据；
• 不可重复读：同一事务两次读取同一逻辑对象得到不同已提交结果；
• 幻读：同一条件查询，前后得到的符合条件的行集合发生变化；
• 丢失更新：两个事务的写结果互相覆盖；
• 写偏斜：两个事务分别修改不同记录，但组合后的业务不变量被破坏。

5.1 MVCC不能自动消除写偏斜

考虑业务规则：

值班医生至少有一人在线。

表中有：

doctor_id | on_call
----------+--------
1         | true
2         | true

事务 T1：

BEGIN;
SELECT COUNT(*)
FROM doctor
WHERE on_call = true;
-- 结果为 2
-- T1决定把医生1设为离线

事务 T2同时执行：

BEGIN;
SELECT COUNT(*)
FROM doctor
WHERE on_call = true;
-- 结果也为 2
-- T2决定把医生2设为离线

然后：

-- T1
UPDATE doctor SET on_call = false WHERE doctor_id = 1;
COMMIT;

-- T2
UPDATE doctor SET on_call = false WHERE doctor_id = 2;
COMMIT;

最终没有医生在线。每个事务单独看都满足“提交前至少有两人在线”，但组合结果违反了业务不变量。

解决方式可能是：

• 在更强的可串行化语义下运行；
• 锁定用于判断不变量的共享资源；
• 使用单独的计数行并锁定它；
• 把约束转成数据库可以直接验证的约束。

仅仅使用MVCC快照不能自动证明业务规则安全。

5.2 PostgreSQL Serializable不是简单的“所有读都加锁”

PostgreSQL的 SERIALIZABLE 使用Serializable Snapshot Isolation（SSI）相关机制，跟踪可能导致不可串行化的读写依赖，必要时让事务失败并返回序列化错误。

这和“给所有查询加排他锁”不同：

• 普通读不一定阻塞写；
• 数据库可能在提交或执行过程中发现危险依赖；
• 某个事务可能收到序列化失败；
• 应用必须回滚整个事务并重试。

5.3 MySQL隔离级别与范围锁有实现边界

InnoDB在REPEATABLE READ下通常使用Next-Key Lock保护索引记录及其间隙；READ COMMITTED会减少间隙锁的使用，但外键检查和重复键检查等场景仍可能需要间隙相关保护。

因此，不能把“READ COMMITTED没有范围锁”当成无条件规则。应结合：

• 查询是否是锁定读；
• 是否走索引；
• 是否存在唯一等值定位；
• 是否涉及插入、外键或唯一性检查；
• 使用的具体InnoDB语义。

---

六、锁粒度：锁的不是抽象“行”，而是具体资源

6.1 粒度的定义

锁粒度是锁作用对象的大小和范围。例如：

数据库
  └── 表
        └── 分区
              └── 索引
                    └── 索引记录
                          └── 键范围或间隙

粒度越粗：

• 锁管理成本可能更低；
• 冲突范围更大；
• 并发度通常更低。

粒度越细：

• 并发度通常更高；
• 锁数量、内存和管理成本可能增加；
• 锁升级、锁表膨胀或大量等待的风险可能提高。

“行锁”也不是一个足够精确的说法。不同数据库锁的实际对象不同。

6.2 PostgreSQL的表级锁与行级锁

PostgreSQL同时存在表级锁和行级锁。

常见表级锁模式包括：

• ACCESS SHARE：普通 SELECT使用；
• ROW SHARE：包含某些锁定读；
• ROW EXCLUSIVE：INSERT、UPDATE、DELETE使用；
• 更强的模式用于DDL或显式锁定。

表锁模式之间有冲突矩阵。例如普通查询获取的ACCESS SHARE会与需要排他访问的某些DDL冲突，但普通读通常不会与普通DML的表级锁互相阻塞。

行级锁则用于协调对具体行的修改和锁定读。可以显式请求：

SELECT *
FROM account
WHERE id = 1
FOR UPDATE;

PostgreSQL的行级锁通常不会直接阻塞普通数据读取，因为普通读取通过MVCC访问可见版本；但它会影响其他需要冲突行锁的操作。

6.3 PostgreSQL的谓词锁不是普通行锁

在可串行化事务中，PostgreSQL可能使用谓词锁相关机制来表示“某个查询读取过某个范围”。它的目的不是像FOR UPDATE那样阻止所有读写，而是检测可能破坏串行化的依赖。

因此：

行锁：阻止或协调具体行上的冲突操作
谓词锁：记录范围读取，用于串行化依赖检测

不能看到锁表里出现锁就断言“某个物理行被排他锁锁住了”。

6.4 InnoDB的锁通常落在索引记录上

InnoDB的一个关键事实是：

记录锁通常是索引记录锁，而不是单纯的堆表行锁。

这会导致几个重要结果。

如果查询有合适的唯一索引：

SELECT *
FROM account
WHERE id = 1
FOR UPDATE;

并且id是唯一索引，通常只需要锁定目标索引记录。

如果条件没有合适索引：

SELECT *
FROM account
WHERE status = 'PENDING'
FOR UPDATE;

InnoDB可能扫描大量索引记录，并对扫描过程中涉及的记录或间隙加锁。锁定范围和影响面可能远大于应用开发者按业务理解的“匹配行”。

索引不仅影响查询速度，也影响锁的范围和冲突概率。这正是执行计划与锁诊断必须结合的原因。

6.5 InnoDB的记录锁、间隙锁和Next-Key Lock

以有序索引键为例：

10, 20, 30

在索引空间中可以抽象出：

(-∞, 10), 10, (10, 20), 20, (20, 30), 30, (30, +∞)

InnoDB中常见的锁类型包括：

• Record Lock：锁定一个索引记录；
• Gap Lock：锁定记录之间的间隙，不锁定已有记录本身；
• Next-Key Lock：记录锁加前面的间隙锁，保护一个索引区间。

例如范围锁定：

SELECT *
FROM orders
WHERE order_id >= 10
  AND order_id < 30
FOR UPDATE;

在合适的索引上执行时，可能锁住相关索引记录及其范围，使其他事务无法在受保护范围内插入会改变查询结果的记录。

这不是“把所有未来可能的行提前创建出来”，而是锁住索引顺序中的区间。

6.6 唯一等值查询与范围查询的差异

假设：

CREATE UNIQUE INDEX ux_account_id ON account(id);

对于：

SELECT *
FROM account
WHERE id = 10
FOR UPDATE;

数据库可以通过唯一索引精确定位记录，通常不需要像非唯一范围扫描那样保护广泛的间隙。

但对于：

SELECT *
FROM account
WHERE id >= 10
  AND id < 20
FOR UPDATE;

数据库需要处理一段索引范围，锁影响面明显不同。

这个差异也说明了为什么线上诊断不能只看SQL文本，还要看：

EXPLAIN

以及真实执行时使用的索引、扫描行数和访问范围。

---

七、锁模式与冲突：判断等待发生在哪里

7.1 共享锁与排他锁的抽象模型

用最简单的资源锁模型表示：

请求/持有	共享锁 S	排他锁 X
共享锁 S	可兼容	冲突
排他锁 X	冲突	冲突

因此：

• 多个事务可以同时持有S；
• 任何事务持有X时，其他事务的S和X都需要等待；
• S升级为X时，可能与其他S发生冲突。

数据库实际锁模式远多于S/X。例如表锁还可能有意向锁，行锁可能有不同强度，范围锁还涉及间隙兼容关系。S/X矩阵只用于建立直觉，不能替代具体数据库的官方冲突矩阵。

7.2 意向锁为什么存在

如果事务要锁定表中某一行，数据库需要让表级锁管理器知道：

这张表内部有事务准备持有行级锁

意向锁就是一种层级协调信号。它使数据库可以快速判断：

• 某个事务是否可能在表内持有行锁；
• 表级排他操作是否必须等待；
• 表锁和行锁如何在层级上协调。

意向锁不等同于“已经锁住整张表”。看到表上有意向锁，不能直接推断整张表不可访问。

7.3 锁转换和锁升级会制造新的冲突

典型流程：

T1：先持有S
T2：先持有S
T1：请求把S升级为X
T2：也请求把S升级为X

两个事务的升级请求互相等待，可能形成死锁。

在实际系统中，类似问题也可能来自：

• 先读取再更新；
• 先检查库存再扣减；
• 多个SQL分别获取不同模式的锁；
• 同一个事务先锁定一部分对象，之后再扩大锁范围。

---

八、死锁：从资源等待到等待图环路

8.1 形式化定义

定义等待图：

G = (V, E)

其中：

• V是事务或锁等待参与者集合；
• E是有向边；
• Ti -> Tj表示Ti正在等待Tj释放一个冲突资源。

如果图中存在环：

T1 -> T2 -> ... -> Tn -> T1

则不存在仅通过其中任一事务继续等待就能自然解除的路径，这就是死锁。

更严格地说，死锁还需要这些事务持续持有阻塞其他事务的资源，并且没有事务可以在当前状态下继续到达释放资源的阶段。

8.2 两行反向加锁的完整例子

以 PostgreSQL或InnoDB的行级写锁为例，准备数据：

CREATE TABLE account (
    id      integer PRIMARY KEY,
    balance integer NOT NULL
);

INSERT INTO account VALUES (1, 100), (2, 100);

连接 A：

BEGIN;

UPDATE account
SET balance = balance - 10
WHERE id = 1;

此时：

A 持有 account.id=1 的写锁

连接 B：

BEGIN;

UPDATE account
SET balance = balance - 20
WHERE id = 2;

此时：

B 持有 account.id=2 的写锁

连接 A继续：

UPDATE account
SET balance = balance + 10
WHERE id = 2;

A等待B：

A --等待--> B

连接 B继续：

UPDATE account
SET balance = balance + 20
WHERE id = 1;

B等待A：

A --等待--> B
B --等待--> A

图中出现环，数据库的死锁检测器会选择一个事务作为牺牲者，使它回滚并释放锁。另一个事务随后可以继续。

8.3 为什么“先扣一行、再加另一行”容易死锁

两个事务执行相同业务，但访问顺序相反：

T1：锁 A，再锁 B
T2：锁 B，再锁 A

如果它们的执行时间交错，就会形成环。

如果统一访问顺序：

T1：总是先锁 id 较小的行，再锁 id 较大的行
T2：也总是先锁 id 较小的行，再锁 id 较大的行

可能的过程变为：

T1：锁 A
T2：请求 A，等待
T1：锁 B，完成并提交
T2：取得 A，再锁 B，完成

这是等待，但不是死锁，因为只有单向等待链。

这类“按全局顺序访问资源”的方法不是消除所有锁等待，而是破坏环形成的必要条件之一。

8.4 死锁的常见来源

1. 多对象访问顺序不一致

事务1：UPDATE user，再 UPDATE wallet
事务2：UPDATE wallet，再 UPDATE user

2. 唯一键和二级索引维护

一次更新可能同时修改：

• 聚簇索引记录；
• 二级索引记录；
• 唯一性检查涉及的索引范围；
• 外键相关记录。

不同SQL的索引访问顺序和数据值分布可能导致交叉等待。

3. 范围锁与插入意向冲突

一个事务锁定范围：

SELECT *
FROM orders
WHERE amount >= 100
FOR UPDATE;

另一个事务尝试插入落入受保护索引范围的记录，可能等待间隙或Next-Key锁。

如果后续两个事务又分别需要对方已持有的其他资源，就可能产生环。

4. 锁定读与普通更新混用

事务A：

SELECT ... FOR UPDATE;
UPDATE ...

事务B：

UPDATE ...
SELECT ... FOR UPDATE;

虽然业务上看似操作同一组对象，但锁获取顺序不同。

5. DDL与DML交叉

PostgreSQL中的某些DDL需要强表锁；MySQL中的元数据锁也可能让DDL等待长事务，甚至形成复杂阻塞链。

这类等待不一定是行锁死锁，但仍应放进等待图分析。

---

九、死锁和锁等待不是一回事

9.1 锁等待

锁等待的结构是：

T2 -> T1

只要T1提交、回滚或释放冲突锁，T2就可以继续。

长时间锁等待可能由以下原因造成：

• T1执行慢；
• T1持有事务后等待应用输入；
• T1开启了事务但忘记提交；
• T1在执行大量扫描或批量更新；
• T1被网络、磁盘或外部服务拖住；
• T1自身正在等待另一个事务。

9.2 死锁

死锁的结构是：

T1 -> T2 -> T1

数据库通常会主动检测并回滚一个事务，而不是无限等待。

9.3 锁等待超时

锁等待超时是另一种情况：

• 等待图可能没有环；
• 等待时间超过参数限制；
• 数据库中止等待方的语句或事务，具体行为依产品和配置而定。

例如InnoDB的锁等待超时和死锁处理不是同一个机制。死锁通常会自动回滚被选中的事务；锁等待超时默认不必然等价于整个事务回滚，相关行为还受innodb_rollback_on_timeout等配置影响。

应用不能把以下错误简单当成同一种错误：

死锁
锁等待超时
语句执行超时
连接断开
事务被取消

恢复动作和重试边界可能不同。

---

十、线上诊断的第一原则：先找等待者，再找阻塞者

诊断锁问题时，至少要回答五个问题：

1. 谁在等待？
2. 等待的资源是什么？
3. 谁持有冲突资源？
4. 持锁事务为什么还没有结束？
5. 等待关系是否形成环？

只看“慢SQL”通常不够，因为等待中的SQL可能几乎没有CPU消耗。

---

十一、PostgreSQL线上诊断

11.1 查看当前活动和等待事件

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    xact_start,
    query_start,
    now() - xact_start  AS xact_age,
    now() - query_start AS query_age,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start NULLS LAST;

重点字段：

• state：连接是否活跃、空闲或处于事务中；
• wait_event_type：等待类别；
• wait_event：具体等待点；
• xact_start：事务开始时间；
• query_start：当前语句开始时间；
• query：当前或最近执行的SQL。

一个特别危险的状态是：

idle in transaction

这表示事务已经没有正在执行的语句，但事务尚未结束。它可能继续持有行锁、表锁和快照，阻塞清理或其他事务。

11.2 使用pg_blocking_pids找直接阻塞者

SELECT
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    a.xact_start,
    a.query_start,
    pg_blocking_pids(a.pid) AS blocking_pids,
    a.query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

pg_blocking_pids(pid)返回直接阻塞指定进程的进程ID。它比仅凭pg_locks手工连接更适合快速定位，但要理解：

• 返回的是直接阻塞者，不是完整传递链；
• 一个等待者可能有多个阻塞者；
• 进程状态随时变化，需要在相近时间点采集；
• 需要足够权限才能看到其他会话的完整信息。

11.3 查看锁表

SELECT
    l.pid,
    a.usename,
    a.state,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation_name,
    l.page,
    l.tuple,
    l.transactionid,
    l.virtualxid,
    a.xact_start,
    a.query_start,
    a.query
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a
       ON a.pid = l.pid
ORDER BY l.granted, l.pid;

字段解释：

• granted = true：锁已经取得；
• granted = false：锁请求正在等待；
• locktype：关系、事务、虚拟事务、元组等锁类型；
• relation：关系对象；
• tuple：某些行级锁信息中的元组位置；
• transactionid：等待另一个事务结束的事务锁。

不要机械地把pg_locks中的每一行都理解成“某一行的锁”。PostgreSQL的锁信息还可能代表事务ID、虚拟事务、表或其他内部资源。

11.4 直接列出等待者与阻塞者

下面的查询把等待会话和直接阻塞会话连接起来：

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocked.wait_event_type,
    blocked.wait_event,
    blocker.pid AS blocker_pid,
    blocker.state AS blocker_state,
    blocker.xact_start AS blocker_xact_start,
    blocker.query AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = bp.blocker_pid;

诊断时应继续检查阻塞者：

SELECT
    pid,
    state,
    xact_start,
    query_start,
    now() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE pid = <阻塞者PID>;

不要直接把“杀掉阻塞者”当作默认修复。终止连接可能回滚一个很大的事务，造成大量WAL、锁释放和恢复压力。

11.5 查看死锁日志

PostgreSQL检测到死锁后通常会中止一个事务，并在服务器日志中记录死锁相关信息。生产环境应配置合适的：

log_lock_waits = on
deadlock_timeout = '1s'

含义是：

• deadlock_timeout：等待超过该时间后，系统会进行死锁检查；同时也影响锁等待日志触发时机；
• log_lock_waits：记录超过相应等待阈值的锁等待。

这两个参数不是“把死锁超时时间改大就能修复死锁”的开关。调得过大，问题出现后日志会晚；调得过小，则可能增加检查和日志噪声。

11.6 PostgreSQL的恢复动作

如果确认某个会话长期持锁且可以安全终止，可以使用：

SELECT pg_cancel_backend(<pid>);

它请求取消当前语句，事务是否结束取决于语句和客户端行为。

更强的操作是：

SELECT pg_terminate_backend(<pid>);

它终止数据库会话，通常会导致其事务回滚。

风险包括：

• 回滚时间可能接近原事务执行时间；
• 大事务回滚期间仍可能影响系统；
• 客户端可能立即重连并再次制造问题；
• 终止错误会话可能破坏业务操作的原子性预期。

正确做法是先保存：

• 阻塞链；
• 会话用户和应用名；
• 当前SQL；
• 事务年龄；
• 相关日志；
• 执行计划和调用方信息。

---

十二、MySQL 8.4 InnoDB线上诊断

12.1 查看InnoDB状态

SHOW ENGINE INNODB STATUS\G

重点查看输出中的：

• TRANSACTIONS；
• LATEST DETECTED DEADLOCK；
• 事务状态；
• 锁等待；
• 等待锁的索引、表和记录信息。

LATEST DETECTED DEADLOCK通常只保留最近一次检测到的死锁信息，不能替代持续采集。

12.2 查看当前事务

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.innodb_trx;

重要字段：

• trx_state：事务状态；
• trx_started：事务开始时间；
• trx_wait_started：开始等待时间；
• trx_mysql_thread_id：可与连接信息关联；
• trx_query：事务当前查询；
• trx_rows_locked：锁住的记录数近似信息；
• trx_rows_modified：修改记录数。

事务长时间处于运行状态并不一定是问题，但长事务会：

• 延长锁持有时间；
• 延长旧版本保留时间；
• 增加Undo和清理压力；
• 使普通一致性读使用更老的快照。

12.3 使用Performance Schema查看锁

MySQL 8.4可使用Performance Schema中的锁表：

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

查看锁等待关系：

SELECT
    dw.REQUESTING_ENGINE_TRANSACTION_ID,
    dw.BLOCKING_ENGINE_TRANSACTION_ID,
    dw.REQUESTING_THREAD_ID,
    dw.BLOCKING_THREAD_ID
FROM performance_schema.data_lock_waits AS dw;

将等待关系与锁详情连接：

SELECT
    w.REQUESTING_ENGINE_TRANSACTION_ID,
    req.OBJECT_SCHEMA AS requested_schema,
    req.OBJECT_NAME   AS requested_table,
    req.INDEX_NAME    AS requested_index,
    req.LOCK_TYPE     AS requested_lock_type,
    req.LOCK_MODE     AS requested_lock_mode,
    w.BLOCKING_ENGINE_TRANSACTION_ID,
    blk.OBJECT_SCHEMA AS blocking_schema,
    blk.OBJECT_NAME   AS blocking_table,
    blk.INDEX_NAME    AS blocking_index,
    blk.LOCK_TYPE     AS blocking_lock_type,
    blk.LOCK_MODE     AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS req
  ON req.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS blk
  ON blk.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

具体字段和可见范围受Performance Schema配置、权限以及当前版本实现影响。诊断时应以实例实际表结构为准：

DESCRIBE performance_schema.data_locks;
DESCRIBE performance_schema.data_lock_waits;

12.4 查看连接与事务对应关系

SELECT
    t.PROCESSLIST_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_DB,
    t.PROCESSLIST_COMMAND,
    t.PROCESSLIST_TIME,
    t.PROCESSLIST_STATE,
    t.PROCESSLIST_INFO
FROM performance_schema.threads AS t
WHERE t.TYPE = 'FOREGROUND';

可以将innodb_trx.trx_mysql_thread_id与threads.PROCESSLIST_ID关联，进一步找到：

• 应用用户；
• 客户端地址；
• 当前SQL；
• 线程存在时间；
• 是否处于Sleep但事务未结束。

12.5 查看Metadata Lock

行锁诊断找不到阻塞原因时，应检查元数据锁：

SELECT
    OBJECT_TYPE,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_DURATION,
    LOCK_STATUS,
    OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING'
   OR LOCK_STATUS = 'GRANTED';

例如，一个长事务对表执行过查询但尚未提交，DDL可能需要等待该表的元数据锁。此时SQL看起来像“ALTER TABLE很慢”，根因却是长事务没有结束。

12.6 MySQL的恢复动作

如果确认需要终止连接：

KILL <processlist_id>;

或只取消当前语句：

KILL QUERY <processlist_id>;

风险与PostgreSQL类似，尤其要注意：

• 事务回滚可能需要较长时间；
• KILL QUERY不一定结束整个事务；
• 连接池可能重新发送同一请求；
• 直接杀掉持锁事务可能造成回滚风暴。

---

十三、执行计划为什么是锁诊断的一部分

锁问题不能脱离访问路径分析。

13.1 相同的WHERE条件可能锁住不同范围

MySQL示例：

SELECT *
FROM orders
WHERE customer_id = 100
FOR UPDATE;

如果有索引：

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

InnoDB可以沿customer_id索引定位相关范围。

如果没有索引，可能扫描聚簇索引中的大量记录。锁定读的访问路径和扫描范围会扩大，其他事务更容易被阻塞。

检查计划：

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100
FOR UPDATE;

还应关注实际执行情况，例如扫描行数、过滤比例和使用的索引。估算行数很小但实际扫描很多，可能意味着统计信息或基数估计不准确。

13.2 PostgreSQL的索引加速不等于锁粒度永远变成一行

在PostgreSQL中，索引可以减少访问数据页的范围，但行级锁仍由实际返回或处理的元组决定；此外，表锁、谓词锁和其他资源仍可能参与并发控制。

检查计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM account
WHERE id = 1
FOR UPDATE;

ANALYZE会真正执行语句，生产环境应注意：

• 查询是否包含写入或锁定；
• 是否会等待；
• 是否会产生副作用；
• 是否应先用不执行的EXPLAIN观察计划。

13.3 批量操作会放大持锁时间

以下语句可能一次处理很多记录：

UPDATE orders
SET status = 'DONE'
WHERE status = 'PENDING';

即使单行锁粒度很细，事务持有大量锁的时间也可能很长。其他事务可能在不同记录上等待，最终形成：

大量等待者 -> 一个大事务

批量任务可以考虑按稳定主键分批，但分批并不自动安全：

• 分批条件必须稳定；
• 每批事务边界要明确；
• 不能因为降低单批大小就忽略业务原子性；
• 需要评估批次之间是否允许其他事务插入或修改。

---

十四、MVCC的代价：旧版本、清理与长事务

MVCC不是免费获得并发性的机制。

14.1 旧版本必须保留到安全时间点

如果仍有事务可能读取旧版本，数据库不能立即回收它。

PostgreSQL中，长事务会阻止旧元组被VACUUM安全清理，可能导致：

• 表膨胀；
• 索引膨胀；
• 查询需要检查更多死元组；
• autovacuum压力增大。

InnoDB中，长事务会延长旧Undo版本的保留时间，可能导致：

• Undo空间增长；
• Purge推进受阻；
• 一致性读需要沿更长版本链回溯；
• 事务系统压力增加。

因此，以下模式很危险：

BEGIN
执行一次查询
应用等待用户操作
数分钟后再执行更新
COMMIT

事务边界不应覆盖不可控的外部等待。

14.2 “空闲事务”也可能有并发影响

应用可能认为：

当前没有SQL执行，所以不会影响数据库

但数据库判断的是事务是否结束，而不是当前是否有语句。

只要事务仍然开放，它可能继续持有：

• 行锁；
• 表锁；
• 元数据锁；
• 快照；
• 事务ID可见性边界。

---

十五、常见误解与反例

15.1 “MVCC意味着读完全不加锁”

反例：

SELECT *
FROM account
WHERE id = 1
FOR UPDATE;

这是显式锁定读。

此外，数据库还需要锁来处理：

• 更新和删除；
• 唯一性检查；
• 外键检查；
• DDL与元数据；
• 范围插入保护；
• 可串行化依赖；
• 事务提交协调。

正确说法是：

MVCC使许多普通读不必等待写锁，但不消除所有锁。

15.2 “读不到未提交值，就不会发生并发异常”

反例是前面的写偏斜：两个事务都只读取已提交数据，仍然可能共同破坏业务不变量。

可见性正确不等于业务操作可串行化。

15.3 “锁的是表中的行”

在InnoDB中，通常更准确的说法是：

锁的是访问路径上的索引记录或索引范围。

在PostgreSQL中，除了行和表，还可能涉及事务锁、谓词锁、元数据等对象。

15.4 “超时就是死锁”

错误。超时可能只是：

T2 等待 T1，而 T1 正在执行一个很慢但最终会提交的事务

诊断必须检查等待图是否有环。

15.5 “加索引只会让查询更快”

索引通常可以减少扫描和锁影响范围，但也会带来：

• 写入时维护索引的额外成本；
• 唯一索引检查；
• 更多索引页竞争；
• 不同访问路径之间的锁顺序差异。

索引优化必须同时看读性能、写放大和锁行为。

15.6 “发现阻塞就杀最长的事务”

最长事务不一定是根阻塞者，也不一定可以安全终止。应先沿等待图确认：

等待者 -> 直接阻塞者 -> 上游阻塞者

优先识别真正持有冲突资源、且处于异常状态的根节点。

15.7 “重试就能解决所有死锁”

重试只能处理“这次事务被数据库回滚”的结果，不能消除持续产生死锁的访问顺序。

如果每次请求都按不同顺序锁定资源，重试可能只是把死锁延迟到下一次。

---

十六、应用如何正确处理死锁与序列化失败

数据库可能主动终止一个事务，以打破死锁或保证串行化。应用必须认识到：

事务失败后，事务内已经执行的逻辑不能继续当作成功状态使用。

正确的结构是：

开始事务
  执行业务SQL
  提交
如果发生可重试的死锁或序列化失败：
  回滚
  重新开始一个全新的事务

伪代码：

for attempt in range(max_retries):
    try:
        with db.transaction():
            lock_or_update_rows_in_canonical_order()
            perform_business_logic()
        break
    except DeadlockError:
        if attempt == max_retries - 1:
            raise
        sleep_with_jitter()
    except SerializationFailure:
        if attempt == max_retries - 1:
            raise
        sleep_with_jitter()

关键要求：

1. 必须回滚失败事务；
2. 重试必须重新建立事务和快照；
3. 事务内副作用不能在数据库回滚后重复执行；
4. 外部消息发送、扣款请求等操作需要幂等键或可靠消息机制；
5. 重试次数和退避时间要有限制；
6. 记录事务失败原因，而不是笼统记录“数据库错误”。

MySQL的死锁错误、锁等待超时和PostgreSQL的序列化失败是不同错误类别，应用应按驱动提供的错误码或异常类型判断，而不是只匹配错误字符串。

---

十七、从现象到根因的诊断流程

17.1 现象分类

先区分：

普通查询变慢
写入变慢
锁等待
死锁错误
锁等待超时
CPU升高
磁盘或WAL/Undo压力升高

不同现象可能共享一个根因，也可能完全无关。

17.2 采集同一时间窗口内的信息

至少采集：

• 等待中的SQL；
• 阻塞者SQL；
• 事务开始时间；
• 当前语句开始时间；
• 等待资源类型；
• 表名和索引名；
• 执行计划；
• 数据库死锁日志；
• 应用请求ID、用户和连接信息。

只在问题结束后查看一次状态，可能已经丢失关键关系。

17.3 还原资源访问顺序

假设两个事务：

-- 事务A
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'A';
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'B';

-- 事务B
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'B';
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'A';

需要把SQL还原成：

A：锁库存A -> 请求库存B
B：锁库存B -> 请求库存A

这比只看两条报错日志更接近根因。

17.4 确认锁对象，而不是只看业务对象

业务上都说“锁订单”，但数据库可能实际涉及：

• 订单主键索引；
• 状态二级索引；
• 外键索引；
• 唯一索引；
• 表级元数据；
• 事务ID。

尤其是InnoDB，必须结合INDEX_NAME、LOCK_TYPE、LOCK_MODE和LOCK_DATA分析。

17.5 检查事务是否包含外部等待

如果持锁事务正在：

等待HTTP响应
等待用户确认
执行远程RPC
等待消息队列
处理大文件

则数据库看到的是一个未提交、持续持锁的事务。根因可能在应用事务边界，而不在SQL本身。

---

十八、设计取舍：减少冲突，而不是盲目取消锁

18.1 缩短事务只能减少持锁时间，不能改变冲突语义

把事务从：

BEGIN
查询
调用外部服务
计算
更新
COMMIT

改成：

调用外部服务
BEGIN
查询
更新
COMMIT

通常可以缩短锁持有时间，但必须确认外部调用结果不会导致数据库状态无法回滚或重复执行。

18.2 统一资源访问顺序

对于多个账户、库存项或订单的事务操作，应定义稳定顺序，例如：

按主键升序
按租户ID、资源ID组合升序
按业务规定的固定层级顺序

所有代码路径都必须遵守，包括：

• 正常流程；
• 补偿流程；
• 定时任务；
• 管理后台；
• 批处理；
• 重试逻辑。

只改一个调用方通常不够。

18.3 用合适索引缩小锁定范围

对于需要锁定的查询：

SELECT ...
FROM orders
WHERE tenant_id = ?
  AND order_id = ?
FOR UPDATE;

如果业务上要求唯一定位，应让索引与条件匹配，例如：

CREATE UNIQUE INDEX ux_orders_tenant_order
ON orders(tenant_id, order_id);

但不能仅因为“有索引”就假定只锁一行。仍需确认：

• 索引是否被选中；
• 条件是否完整；
• 是否发生隐式类型转换；
• 是否存在函数导致无法使用索引；
• 实际扫描范围是什么。

18.4 把业务不变量显式化

如果一个规则非常重要，不要只依赖“先查询、再决定、再更新”的应用逻辑。可以考虑：

• 数据库唯一约束；
• CHECK约束；
• 排他约束；
• 计数器行；
• 显式锁定协调行；
• 可串行化事务；
• 乐观版本号；
• 幂等命令。

不同方案的并发、失败和重试语义不同，应根据业务边界选择。

18.5 乐观并发控制也仍然需要数据库条件更新

一种常见的应用层乐观控制：

UPDATE account
SET balance = 80,
    version = version + 1
WHERE id = 1
  AND version = 7;

应用检查影响行数：

• 1：版本匹配，更新成功；
• 0：其他事务已经修改，当前更新失败，需要重新读取或报告冲突。

这可以减少显式锁定读，但UPDATE本身仍需要写锁；它不是“完全无锁”。

---

十九、一个完整的判断框架

面对一条并发异常，可以按以下顺序推导：

第一步：确定读取类型

问：

这是普通一致性读，还是FOR UPDATE/FOR SHARE等锁定读？

因为两者的版本可见性和等待行为不同。

第二步：确定隔离级别和快照边界

问：

快照按语句建立，还是按事务复用？
锁定读是否使用当前版本？
是否启用了SERIALIZABLE？

没有隔离级别和数据库产品边界，不能准确推断第二次查询读到什么。

第三步：确定实际访问路径

问：

走了哪个索引？
扫描了多少记录？
是否包含范围？
是否发生全表或大范围扫描？

锁影响的是实际访问资源，不是SQL文本中的抽象条件。

第四步：画出等待边

把每个等待关系写成：

等待事务 -> 持锁事务

并标出资源：

T2 --等待 index=account_pkey, id=1 --> T1

第五步：检查是否成环

• 无环：阻塞或长等待；
• 有环：死锁；
• 结构变化：可能是多个等待者、锁释放或新事务加入。

第六步：选择恢复方式

• 根阻塞者很快结束：观察并优化事务；
• 根阻塞者异常空闲：评估取消或终止；
• 已发生死锁：回滚并重试失败事务；
• 持续重复死锁：统一锁顺序、缩小范围、修正索引和事务边界；
• 业务不变量被破坏：提高隔离或引入显式协调资源。

---

MVCC决定事务看到哪个版本，锁决定冲突操作是否可以继续，等待图决定这些等待是否已经形成无法自行解除的环。只有把快照、锁对象、索引访问路径和事务生命周期放在同一条因果链上，才能正确解释“为什么读到旧值”“为什么普通读不阻塞而更新会阻塞”“为什么看起来只改一行却锁住一大片范围”，以及线上死锁到底应该重试、终止会话，还是修正事务设计。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：数据库事务完整指南：ACID、隔离级别、异常现象与正确边界
• 下一篇：数据库连接与连接池：容量、超时、排队、泄漏和故障恢复
• 延伸：SQL 执行计划与查询优化：基数估计、Join 算法和慢查询诊断

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。