1
2
3
4
5
6
7
8
9

事务 A 读到了事务 B 未提交的修改。

时间线：
  T1: 事务B：UPDATE balance SET bal = bal - 100 WHERE id=1;  (未提交)
  T2: 事务A：SELECT bal FROM balance WHERE id=1;  → 读到 -100 后的值
  T3: 事务B：ROLLBACK;
  → A 读到了"从未存在"的值

几乎所有现代数据库都默认不允许脏读。

1
2
3
4
5
6
7

事务 A 同一行读两次，结果不同（其他事务已提交）。

时间线：
  T1: 事务A：SELECT bal FROM balance WHERE id=1;  → 1000
  T2: 事务B：UPDATE balance SET bal = 900 WHERE id=1; COMMIT;
  T3: 事务A：SELECT bal FROM balance WHERE id=1;  → 900
  → A 两次读结果不同

1
2
3
4
5
6
7

事务 A 同一查询两次，结果集行数不同（其他事务插入/删除了匹配行）。

时间线：
  T1: 事务A：SELECT * FROM users WHERE age > 18;  → 10 行
  T2: 事务B：INSERT INTO users (age) VALUES (20); COMMIT;
  T3: 事务A：SELECT * FROM users WHERE age > 18;  → 11 行
  → A 多了一行"幻影"

1
2
3
4
5
6
7
8
9

两个事务都基于读到的旧值更新，后写的覆盖前写的。

  T1: 事务A：读 bal = 1000
  T2: 事务B：读 bal = 1000
  T3: 事务A：写 bal = 1000 + 100 = 1100
  T4: 事务B：写 bal = 1000 + 200 = 1200
  → A 的更新丢失

SQL 标准没列这个，但所有数据库都要处理。

1
2
3
4
5
6
7

读：加共享锁 S
写：加排他锁 X
S-X 互斥，X-X 互斥

问题：写事务持有 X 锁期间，所有读都被阻塞
  - 长事务写入 → 大量 SELECT 等待
  - 实际不可用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

核心：每行有多个版本，每个事务看到自己"快照"

UPDATE 不修改原行，而是：
  1. 把原行标记为"旧版本"
  2. 写入新行（新版本）
  3. 不同事务根据"可见性规则"看到不同版本

  事务A（开始早）→ 看到旧版本
  事务B（开始晚）→ 看到新版本

→ 读和写不冲突！
→ 性能远高于锁方案

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

代价 1：存储膨胀
  - 旧行版本不能立即删
  - 等到所有"可能看到旧行"的事务结束后才能清理
  - PostgreSQL 这点尤其严重（详见第 7 节）

代价 2：写放大
  - 一次 UPDATE = 写一行 + 标记旧行 + 更新索引
  - 比直接"原地改"成本高

代价 3：长事务问题
  - 长事务持有"旧版本"，导致 Undo/WAL 不能清理
  - 拖累整个实例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

核心机制：
  - 每行有"块头 ITL（事务槽）"，记录最后修改它的事务
  - Undo Segment 存"旧版本"
  - SCN（System Change Number）：全局递增的事务编号

可见性判断：
  - 事务开始时记录一个"快照 SCN"
  - 读行时：
    - 行的 ITL SCN ≤ 快照 SCN → 可见
    - 行的 ITL SCN > 快照 SCN → 不可见，去 Undo Segment 找旧版本
    - 一直往回找，直到找到 ≤ 快照 SCN 的版本

1
2
3
4
5
6
7
8

查询流程：
  SELECT balance FROM accounts WHERE id = 1;

  1. 读到当前行的 balance = 900，ITL SCN = 200
  2. 我的快照 SCN = 150
  3. 200 > 150 → 不可见
  4. 通过 ITL 找到 Undo Segment 中 SCN = 100 的旧版本 balance = 1000
  5. 100 ≤ 150 → 可见，返回 1000

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

核心机制：
  - 每行隐藏两个列：DB_TRX_ID（最后修改事务 ID）、DB_ROLL_PTR（指向 Undo 链）
  - Undo Log 中存旧版本，通过 DB_ROLL_PTR 串成链
  - ReadView：事务开始时创建的"快照"

ReadView 内容：
  - m_ids：当前活跃（未提交）事务 ID 列表
  - min_trx_id：m_ids 最小值
  - max_trx_id：下一个要分配的事务 ID
  - creator_trx_id：创建 ReadView 的事务 ID

可见性判断：
  - 行的 trx_id < min_trx_id → 该事务已提交 → 可见
  - 行的 trx_id >= max_trx_id → 该事务在我之后开始 → 不可见
  - 行的 trx_id 在 m_ids 中 → 该事务未提交 → 不可见
  - 行的 trx_id == creator_trx_id → 自己改的 → 可见
  - 否则 → 可见

1
2
3
4
5
6
7

RC vs RR 的实现差异：
  - Read Committed：每条 SELECT 都创建新 ReadView
    → 总是看到最新已提交数据
    → 但同一事务内同一行可能看到不同值（不可重复读）
  - Repeatable Read：事务内只创建一次 ReadView（第一次读时）
    → 整个事务看到的数据快照一致
    → 不会发生不可重复读

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

核心机制：
  - 每行有 xmin（创建事务 ID）和 xmax（删除/更新事务 ID）
  - 没有独立 Undo，旧行就留在 Heap 里
  - clog（commit log）：记录每个事务的状态（committed/aborted/in-progress）

可见性判断：
  - 行的 xmin 已提交且 < 当前快照 → 可见
  - 行的 xmax 已提交且 ≤ 当前快照 → 已被删除，不可见
  - 复杂规则见 PostgreSQL 源码 HeapTupleSatisfiesMVCC

特点：
  - 不需要 Undo Log
  - 旧 tuple 直接留在 heap 中（"表膨胀"）
  - VACUUM 后台清理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

核心机制：
  - 行版本存在 TempDB（不是本表）
  - 两种模式：
    RCSI（Read Committed Snapshot Isolation）：
      - Read Committed 默认走行版本
      - 每条语句一个快照
    SI（Snapshot Isolation）：
      - 类似 RR，整个事务一个快照
      - 需要应用显式启用

启用：
  ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;  -- RCSI
  ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON;  -- SI

注意：
  - SQL Server 默认 RC 是"读加 S 锁"，不是 MVCC
  - 启用 RCSI 后才变成 MVCC 行为

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

InnoDB 在 RR 级别下，普通 SELECT 用 MVCC（快照读），不会幻读
但 UPDATE/DELETE/SELECT FOR UPDATE 用当前读，要靠锁防幻读

Next-Key Lock = Gap Lock + Record Lock

示例：
  表：id (1, 5, 10, 15, 20)
  索引：id 主键

  事务A：SELECT * FROM t WHERE id BETWEEN 5 AND 15 FOR UPDATE;
  → 锁住 id ∈ [5, 15] 的所有行（Record Lock）
  → 同时锁住 (15, 20) 之间的"间隙"（Gap Lock）
  → 防止其他事务插入 id ∈ (10, 15) 或 (15, 20)

  事务B：INSERT INTO t VALUES (12);
  → 阻塞（Gap Lock 拦截）

1
2
3
4

Gap Lock 的副作用：
  - 锁范围比"实际行"大
  - 高并发插入场景容易死锁
  - 唯一索引等值查询会"退化"（不需要 Gap Lock）

1
2
3
4
5
6
7
8

PG 的 RR 完全基于 MVCC，没有 Gap Lock：
  - 整个事务一个快照
  - 重复读不会变化（不会不可重复读）
  - 但其他事务插入的新行，事务后期能看到（幻读）

  事务A：SELECT * FROM t WHERE id > 10;  → 3 行
  事务B：INSERT INTO t VALUES (15); COMMIT;
  事务A：SELECT * FROM t WHERE id > 10;  → 4 行 ← 幻读！

1
2
3
4

PG 怎么防幻读？
  → 用 Serializable 级别（SSI 实现）
  → 通过 SIREAD 锁 + 冲突检测
  → 性能代价大

1
2
3
4
5
6

SI（Snapshot Isolation）：类似 PG 的 RR
  - 不会不可重复读
  - 但会幻读
  - 写冲突时报错（需要重试）

要防幻读 → Serializable

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

真 Serializable：
  - 表级锁 / 范围锁
  - 性能极差，几乎不用
  - 2PL（Two-Phase Locking）的严格实现

SSI（Serializable Snapshot Isolation）：
  - PostgreSQL 9.1+、SQL Server（部分）
  - 基于 MVCC + 冲突检测
  - 性能远好于传统 Serializable
  - 但仍有写冲突代价

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

默认隔离级别选择：
  - Oracle / SQL Server / PG → Read Committed（默认）
  - MySQL InnoDB → Repeatable Read（默认）

需要"严格串行化"：
  - 高一致性场景（金融）→ Serializable
  - 否则 RC/RR 已经够用

PG SSI 用法：
  SET default_transaction_isolation = 'serializable';
  -- 业务代码需要重试机制（写冲突会报错）

1
2
3
4
5
6

每条 SELECT 创建一个新快照，看到当时最新已提交数据。

  事务A：
    SELECT * FROM t WHERE id = 1;  -- 假设看到 value = 100
    [其他事务改并提交 value = 200]
    SELECT * FROM t WHERE id = 1;  -- 看到 value = 200

1
2
3
4

InnoDB 在 RC 级别下：
  - 每条 SELECT 创建新 ReadView
  - 不能 Next-Key Lock（退化到 Record Lock）
  - 不能防不可重复读

1
2
3
4
5
6
7
8

SQL Server 默认 RC 是"读加 S 锁"：
  - SELECT 期间持 S 锁
  - 写事务被阻塞
  - 不是 MVCC

启用 RCSI 后变成 MVCC（语句级快照）：
  ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;
  -- 这是 SQL Server 性能优化关键步骤

1
2
3
4
5
6
7

PG 没有 Undo Segment，旧 tuple 留在 heap：
  UPDATE → 插入新 tuple + 标记旧 tuple xmax
  旧 tuple 仍在 heap 中，占用空间

  长期高频 UPDATE：
    原表 1GB → 实际有效数据 200MB + 死 tuple 800MB
    → 性能下降（扫描慢、缓存命中率低）

1
2
3
4
5
6
7

PG 自动清理死 tuple 的后台进程：
  - 默认启用
  - 每 N 行变更触发一次 VACUUM
  - 关键参数：
    autovacuum_vacuum_threshold = 50（基础行数）
    autovacuum_vacuum_scale_factor = 0.2（变更比例）
    → 满足条件：50 + 表行数 × 0.2

1
2
3
4
5
6
7
8
9

-- 手动触发
VACUUM ANALYZE accounts;     -- 普通 VACUUM + 更新统计
VACUUM FULL accounts;        -- 锁表 + 重写表（回收磁盘空间，慢）
REINDEX TABLE accounts;      -- 重建索引

-- 监控
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

PG 长事务的连锁问题：
  1. 旧 tuple 不能被 VACUUM 清理（事务可能还要看到）
  2. clog 不能截断
  3. 复制槽（replication slot）可能阻塞 WAL 删除
  4. 表空间持续膨胀

排查：
  SELECT pid, age(now(), xact_start) AS duration, query
  FROM pg_stat_activity
  WHERE state = 'idle in transaction';

  → 长事务杀掉：SELECT pg_terminate_backend(pid);

1
2
3
4
5

-- MySQL 看最近一次死锁
SHOW ENGINE INNODB STATUS\G

-- PostgreSQL 看日志
-- 默认会记录：ERROR: deadlock detected

1
2
3
4
5
6

-- MySQL 看锁等待
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM performance_schema.data_locks;

-- PostgreSQL 看锁
SELECT * FROM pg_locks WHERE NOT granted;

1
2
3
4
5
6
7
8
9

-- MySQL
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- PostgreSQL
SELECT pid, xact_start, query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

1
2
3
4

记住三句话：
  1. RC 是语句级快照，RR 是事务级快照，Serializable 是真串行
  2. PG 没有 Undo，旧 tuple 留在 heap → 必须配置 autovacuum
  3. MySQL InnoDB 默认 RR + Next-Key Lock 防幻读，但代价是死锁风险