事务与隔离级别：从 ACID 到 MVCC

0. 元信息

• 主题路径：docs/topics/db-and-sql/subtopics/transaction-and-isolation/README.md
• 主分类：计算机基础
• 辅助分类：Web 与后端
• 适合对象：完成父主题前置阶段的开发者
• 建议周期：1～3 周，每周 8～12 小时
• 前置知识：relational-model-and-sql
• 最终目标：能用双会话复现异常、锁等待和死锁，并给出正确事务边界

1. 学习路线

事务 → WAL/恢复 → 锁与 MVCC → snapshot → 隔离异常 → 重试

2. 阶段周数分配

本子主题按实验推进，不固定周数。每完成一个概念就留下 SQL、输入数据、输出和解释。

3. 九阶段表（精简）

4. 第一周任务

精简版不固定 Day 1～7。执行约定是 PostgreSQL 16+、psql -v ON_ERROR_STOP=1、脚本可在空库重复运行。

5. 阶段通用验收

以 §3 的可观察标准和 §9.4 题目为验收。

6. 最终验收

独立完成一份最小实验、三组数据、一份解释和一次 PostgreSQL/MySQL 行为对照。

7. 综合项目

并入父主题“小型电商数据库”，本子主题负责 事务并发控制 的设计、实验与报告。

本主题贡献

• 在父主题 db-and-sql 的综合项目「小型电商数据库」中，本主题（事务与隔离级别）负责 事务并发控制：在 ACID + WAL + MVCC 之上为每条业务路径选定隔离级别，复现读已提交 / 可重复读 / 序列化下的脏读 / 不可重复读 / 幻读 / 写偏差，并给出死锁处理与重试策略。
• 把 §1 路线「事务 → WAL/恢复 → 锁与 MVCC → snapshot → 隔离异常 → 重试」落到 4 个核心场景：库存扣减（写写冲突）、订单 + 详情（读写冲突）、余额转账（lost update）、排行榜（写偏差）；用 psql 双会话 + SELECT pg_current_xact_id() + SELECT txid_current() 复现每条异常；用 pg_locks / pg_stat_activity 看锁等待与死锁图；用 BEGIN; SELECT ... FOR UPDATE; ... COMMIT 模板防止 lost update。
• 与 db-and-sql 其它子主题对接：复用 relational-model-and-sql 的 schema 与 seed；为 index-and-query-plan 提供 snapshot 在 RR 下的可见性证据（heap fetches 解读）；给父主题报告输出 4 个隔离级别的预期 / 实际差异矩阵。

交付物清单：

1. tx/anomalies.md：≥ 5 条隔离异常的复现脚本，含 PG default_transaction_isolation 切换（READ COMMITTED / REPEATABLE READ / SERIALIZABLE）与最小复现 SQL，含精确时间戳与 xmin/xmax 截屏；
2. tx/locks.sql：在 4 张核心表上跑 pg_locks 视图并落表，证明行锁 vs 谓词锁 vs 索引 Gap Lock 的存在与缺失（PG 默认 RR 谓词锁靠 SSI）；
3. tx/deadlock.sql：≥ 2 条制造循环等待的 SQL，触发 PG 40P01 deadlock detected 后展示回滚日志与重试代码（Java / Go / Python 任一语言 pseudo-code）；
4. tx/pg_vs_mysql.md：PostgreSQL Serializable SSI 与 MySQL InnoDB RR（含 gap lock / next-key lock）在写偏差上的差异报告，附两边执行计划 + 锁视图。

验收标准：

• 5 条异常在双会话下 100% 复现，含精确时间戳与 xmin/xmax 截屏；
• 死锁 SQL 在 PG 上稳定触发 40P01，配套重试代码在 5 次循环内全部成功；
• 至少 1 条写偏差（write skew）用 PG Serializable SSI 复现并解释「snapshot 不可见但谓词冲突」机制；
• MySQL RR 下相同写偏差会出现 next-key lock 等待；与 PG 行为差异点写入报告。

8. 推荐资料

见 §9.3。先用 PostgreSQL 官方文档和课程实验，书用于补理论。

9. 学习资料汇聚（v0.3 自包含）

9.1 背景与动机

事务把并发操作变成受约束的状态转换。真实系统还要处理数据规模、失败和并发。我们用 PostgreSQL 实验观察语义，再用 MySQL 核对实现差异。

9.2 概念地图

flowchart LR
  A[需求与数据] --> B[关系与约束]
  B --> C[SQL]
  C --> D[Planner / Transaction]
  I[Index / Statistics] --> D
  D --> E[执行与并发]
  E --> F[结果 / 延迟 / 锁等待]
  F --> G[验证与修改]
  G --> C

核心概念：ACID、WAL、lock、snapshot、MVCC、isolation、deadlock。图中从业务不变量出发，经 SQL、计划或事务到可观察结果，再用相同数据验证修改。

9.3 基础知识讲解

9.3.1 经典论文

9.3.2 经典书籍

9.3.3 优秀博客与文档

9.3.4 核心人物

9.3.5 开发与学习方法

课程补充：CMU 15-445 适合数据库实现，Berkeley CS186 适合系统课程与实验。Andy Pavlo 的课堂讲解用于连接论文、实现与计划输出。

9.4 经典问题与经典案例

9.5 学习难点

概念难点

思维难点

工程难点

9.6 技术标准与接口

9.6.1 Entity

9.6.2 Scope

本子主题处理 事务并发控制。SQL 标准描述通用语义，产品文档描述 planner、storage、locking 等实现。不要把 PostgreSQL 扩展当成所有数据库的共同保证。

9.6.3 Structure

必须掌握的接口：DDL/DML、约束、SELECT/JOIN、事务控制、EXPLAIN (ANALYZE, BUFFERS)、system catalog 与统计视图。具体重点是：ACID、WAL、lock、snapshot、MVCC、isolation、deadlock。

9.6.4 Ecosystem

PostgreSQL 是主实验环境。MySQL 用于 InnoDB 和 optimizer 对照。SQLite 适合单文件实验。Oracle 与 SQL Server 用来了解企业产品的方言、执行计划和并发差异。JDBC、ODBC、DB-API 是常见客户端接口。

9.6.5 Depth Tiers

本计划要求达到 L3。

9.6.6 Source

• PostgreSQL 官方文档：引用快照 2026-07-30。
• MySQL Reference Manual：引用快照 2026-07-30。
• ISO SQL 标准条目：引用快照 2026-07-30。

10. 常见误区

• 用单个正常样例证明理解；
• 混淆逻辑结果与物理执行顺序；
• 忽略 NULL 与三值逻辑；
• 把数据库默认行为当业务规范；
• 不保存版本、DDL 和种子数据；
• 看到索引就断言查询会更快；
• 只看平均耗时，不看计划和数据分布；
• 在生产直接试破坏性 SQL；
• 长事务里做网络调用；
• 跨数据库迁移时只检查语法；
• 用 ORM 隐藏生成 SQL 后不再检查；
• 修改后不重跑相同正确性断言。

11. 所有知识点分类（统一规则）

1. 编程语言
2. 数据结构与算法
3. 计算机基础
4. 工程技术
5. Web 与后端
6. 前端与客户端
7. 数据与人工智能
8. 项目与职业能力
9. 安全与可靠性

本计划归属：计算机基础 主 + Web 与后端 辅。