ozine's book library

主题

数据库事务：让一组修改成立，也让并发判断成立 ​

给账户 A 扣 30，再给账户 B 加 30，这两步必须一起成功。把它们放进事务，可以防止“扣了没加”。但如果两个请求都先读到 A 还有 100，再各自按这个旧值写余额，仅仅加上 BEGIN 和 COMMIT，仍不保证业务正确。

事务要解决两层问题：一组修改能不能一起提交；并发事务的读和写，能不能保持业务约束。本文从这两个问题讲 ACID、隔离级别、MVCC 和锁。引擎边界固定为 PostgreSQL 18、MySQL 8.4 InnoDB。实际实验只在 PostgreSQL 18.6 的 single-user 离线模式运行，验证回滚、提交、保存点和条件更新；并发时序与锁行为按官方文档解释，未冒充实测。

ACID 分别承诺什么 ​

属性它解决的问题应用仍要负责什么
Atomicity，原子性一个事务内的数据库修改作为整体提交或撤销别把事务内更新和事务外更新混用；邮件、HTTP 调用不会自动回滚
Consistency，一致性有效事务应把数据库从满足约束的状态带到另一有效状态写对业务规则，建立唯一、外键、CHECK 等约束；数据库不知道所有业务含义
Isolation，隔离性控制并发事务能看到什么、彼此怎样干扰选合适级别，处理冲突，避免基于旧数据做无保护写入
Durability，持久性提交成功的变化按配置约定承受故障理解刷盘、存储硬件、同步复制和备份恢复的边界

这里的 C 不是“所有副本立即一样”，I 也不是“每个事务独占数据库”。事务的具体保证来自引擎、隔离级别、日志与持久化配置，不能用四个字母盖过这些条件。PostgreSQL 的 WAL 要求相关日志先于数据页持久化；synchronous_commit=off 可提前确认提交，因此允许崩溃丢失最近已确认事务。事务执行完一次，不足以证明断电持久性。[1][2][3]

原子性还有引擎定义的例外。PostgreSQL 的序列 nextval 已发出的值不会因为事务回滚而收回，因此 serial/identity 主键出现空号并不代表丢了业务记录。本文实测在事务内取到序列值 1，回滚后再次取值为 2。序列提供编号，不提供“成功订单的连续计数”。[4]

先实际观察整体撤销 ​

初始余额是 1|100、2|100：

sql
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
ROLLBACK;
SELECT id, balance FROM accounts ORDER BY id;

本次实际结果：

text
1|100
2|100

把结束语句改为 COMMIT，实际结果变成 1|70、2|130。事务把两次写入放进一个提交边界，回滚时两次都撤销。这只验证了单会话的数据库行为，没验证中途断电，也没有外部转账动作参与。

保存点 SAVEPOINT 把事务里的一段标出来。PostgreSQL 事务遇到语句错误后通常进入失败状态，不能假装忽略错误继续提交；可以整体回滚，也可以 ROLLBACK TO 已有保存点，再继续后续合法操作。实测中先标保存点，再扣 500 触发非负余额 CHECK，回到保存点后给账户 2 加 1，最终得到 1|70、2|131。保存点不是独立提交的小事务。[5][6]

三种常见异常，要看谁在什么时刻提交 ​

异常一个明确的并发历史危险在哪里
脏读T1 把余额改成 20，未提交；T2 读到 20；T1 回滚T2 使用了从未正式成立的数据
不可重复读T1 第一次读余额为 100；T2 改为 70 并提交；T1 再读变成 70同一事务中，两次读同一记录不一致
幻读T1 查“待处理订单”得到 5 行；T2 插入匹配订单并提交；T1 重查得到 6 行按条件得到的记录集合变化
序列化异常两事务各自检查后修改不同记录，合起来违反全局约束每次单独读都合理，合起来不等价于任何串行顺序

幻读关心满足条件的集合改变，不只限于 INSERT：把原来不匹配的记录更新到匹配范围，也可能改变集合。不可重复读与幻读是帮助理解现象的分类，并非穷尽所有并发错误。经典论文专门指出，仅靠这三种现象描述隔离会遗漏重要情况。[4:1][7]

四个隔离级别，先看最低语义，再看具体引擎 ​

SQL 隔离级别通常按以下“不得出现什么”介绍。表中“可发生”表示级别没有禁止，引擎可以提供更强保证：

标准级别脏读不可重复读幻读序列化异常
Read Uncommitted，读未提交可发生可发生可发生可发生
Read Committed，读已提交禁止可发生可发生可发生
Repeatable Read，可重复读禁止禁止可发生可发生
Serializable，可串行化禁止禁止禁止禁止

“Serializable”要求已提交事务的效果等价于某种一个接一个执行的顺序，不要求引擎真的一次只运行一个事务，也不自动承诺所有外部系统的实时顺序。[4:2]

常见问题PostgreSQL 18MySQL 8.4 InnoDB
默认级别Read CommittedRepeatable Read
请求 Read Uncommitted行为映射为 Read Committed允许读未提交数据的低隔离行为
普通快照 SELECT 在 RC 下每条命令建立自己的快照每次一致性读建立新快照
普通快照 SELECT 在 RR 下基于事务首次非事务控制语句建立的快照；避免幻读同一事务的一致性读通常使用首次一致性读建立的快照；也能显式创建快照
需要读后更新行锁、原子条件更新或 Serializable 等锁定读、原子条件更新等；锁定读与普通一致性读不可混为一谈
Serializable 的重要代价跟踪读写依赖，可能因序列化冲突回滚更强的锁定行为，含特定条件下把普通 SELECT 变为共享锁定读，可能增加等待

PostgreSQL RR 已经禁止幻读，但仍可能有写偏差。InnoDB RR 下普通一致性读的旧快照，与锁定读/UPDATE 使用的状态并非同一套读取规则；不能看到 REPEATABLE READ 就以为事务里每种语句都读取同一个冻结画面。[8][9][10]

没有幻读，为什么还可能把业务做错 ​

设值班表里 A、B 都在岗，规则是至少留一人。两个事务各自看到两人在岗，于是一个让 A 下班，另一个让 B 下班。它们写不同的行，旧快照里的读也都稳定，最后却没人值班。下面是解释写偏差的假设并发时序，并非本次双会话运行记录。[7:1][4:3]

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
sequenceDiagram
    participant T1 as 事务 T1
    participant DB as 值班表 A=true B=true
    participant T2 as 事务 T2
    T1->>DB: 读快照，在岗人数=2
    T2->>DB: 读快照，在岗人数=2
    T1->>DB: 更新 A=false
    T2->>DB: 更新 B=false
    T1->>DB: 提交
    T2->>DB: 提交
    Note over DB: 若两者都可提交，最终人数=0
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
sequenceDiagram
    participant T1 as 事务 T1
    participant DB as 值班表 A=true B=true
    participant T2 as 事务 T2
    T1->>DB: 读快照，在岗人数=2
    T2->>DB: 读快照，在岗人数=2
    T1->>DB: 更新 A=false
    T2->>DB: 更新 B=false
    T1->>DB: 提交
    T2->>DB: 提交
    Note over DB: 若两者都可提交，最终人数=0

解决方法必须保护“至少一人在岗”这个判断。可以在共同的业务范围行上串行化修改；也可以使用 PostgreSQL Serializable，并在冲突时重试整个事务。只锁自己将要修改的那一行，仍没有覆盖另一事务检查的共同条件。具体方案还要考虑吞吐和争用，不能把“加行锁”当成万能答案。

MVCC 保存的是版本，快照决定哪个版本能被看见 ​

MVCC 是多版本并发控制。更新发生时，引擎保留让旧读者能继续读的历史信息，再根据快照和事务状态判断可见性。PostgreSQL 主要在堆表中保留元组版本，之后由 VACUUM 等机制清理不再需要的版本；InnoDB 通过记录中的事务信息和 undo 重建旧版本。这两种实现不能互相代换。[11][12][13]

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
flowchart LR
    U["事务 T2 更新并提交"] --> N["新版本：余额 70"]
    U --> O["旧版本或重建信息：余额 100"]
    S1["T1 的旧快照"] --> O
    S2["提交后建立的新快照"] --> N
    O --> C["没有快照再需要旧版本时<br/>才具备清理条件"]
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
flowchart LR
    U["事务 T2 更新并提交"] --> N["新版本：余额 70"]
    U --> O["旧版本或重建信息：余额 100"]
    S1["T1 的旧快照"] --> O
    S2["提交后建立的新快照"] --> N
    O --> C["没有快照再需要旧版本时<br/>才具备清理条件"]

同一事务通常能看到自己先前的写入，即使这些写入尚未提交。这里的“旧快照”主要限制其他事务的变化，不是把当前事务自己的修改也藏起来。PostgreSQL 与 InnoDB 文档都明确了这点。[4:4][9:1]

MVCC 减少普通读与写之间的直接阻塞，却不意味着没有锁。两个写者改同一行仍需协调，DDL 会拿表级锁，唯一约束检查也可能等待；显式 SELECT ... FOR UPDATE 更是在要求锁。长事务不断保留旧快照，还可能拖住历史版本清理，扩大存储和扫描成本。[14][9:2]

行锁、间隙锁、临键锁、意向锁各保护什么 ​

先把引擎标签贴上。PostgreSQL 有行级锁及多种表级锁，但没有让应用直接套用的 InnoDB gap/next-key 锁规则；PostgreSQL Serializable 的谓词锁用于检测依赖，不是 InnoDB 那种阻挡范围内插入的间隙锁。[14:1][4:5]

锁或机制本文对应的引擎保护对象及用途
记录锁，常称行锁InnoDB锁索引记录；即使表没显式索引也有内部索引可供锁定
行级共享/更新锁PostgreSQL阻止与该行上锁模式冲突的修改或锁定读；不同模式兼容性不同
间隙锁 gap lockInnoDB保护索引记录之间的空隙，主要防止往该空隙插入
临键锁 next-key lockInnoDB一条记录锁加该记录之前的间隙锁，常用来保护范围
意向锁 IS/IXInnoDB 表级表明事务准备在表内持有共享/排他记录锁，让表级锁冲突检查不必遍历所有行锁
Serializable 的 SIREAD 谓词锁PostgreSQL记录读范围以识别危险读写依赖，通常不直接阻塞写入，可能触发事务回滚

InnoDB 的间隙锁是抑制插入的锁。不同事务持有的纯间隙锁可以兼容，不能照搬普通共享/排他记录锁的直觉。意向排他锁 IX 之间也可兼容；“两事务都准备改表内一些行”并不等于“必须互斥整张表”。[15]

用索引键 10、20、30 理解临键范围：保护记录 20 的一个典型 next-key 区间是 (10,20]，同时阻止插入 15 和冲突地修改 20。实际拿哪些区间，要看隔离级别、使用的索引、访问范围、唯一查找是否命中及语句种类。唯一索引的完整等值查找命中一条记录时，InnoDB 可以只锁记录、不锁前方间隙；部分唯一键条件不能自动套用这个例外。RC 下大部分搜索与扫描的间隙锁会关闭，但外键检查、重复键检查等仍有例外。[15:1][16][8:1]

因此“有索引就是行锁，没索引就是表锁”不准确。InnoDB 可能因为缺少合适索引扫描并锁住大量记录及范围，看起来几乎阻住整张表，但这与锁机制自动升级为一个表锁不是同一回事。应看实际扫描路径与锁等待信息。

先把条件与修改合成一次动作 ​

余额扣减常见错误是“先 SELECT，应用算新余额，再无条件 UPDATE 旧计算结果”。另一事务可以在两步之间改变余额。能表达成单行原子条件更新时，先考虑这样写：

sql
UPDATE accounts
SET balance = balance - 80
WHERE id = 1 AND balance >= 80
RETURNING id;

单会话实测中账户 1 只有 70，这条语句返回零行，余额仍是 70。应用必须把零影响行数作为业务分支处理，而不是只看 SQL 有没有报错。这个例子实际证明的是条件不足时不扣款；并发条件重新检查等保证来自引擎文档，未在离线模式实验。[4:6]

复杂的读后写流程可以在同一事务里先锁所需行，保持锁直到提交。取舍是锁持有期间别人会等待，期间不要发慢 HTTP 请求、等待用户输入或做长计算。跨多行的不变量仍要明确锁住什么共同范围。

死锁是等待形成了环 ​

T1 先锁 A，T2 先锁 B；T1 再要 B，T2 再要 A。双方都不能前进。

正在绘制图示…

可切换适合窗口或原始尺寸
查看图源代码
flowchart LR
    T1["T1：持有 A"] -->|等待 B| T2["T2：持有 B"]
    T2 -->|等待 A| T1
放大图示

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码
flowchart LR
    T1["T1：持有 A"] -->|等待 B| T2["T2：持有 B"]
    T2 -->|等待 A| T1

PostgreSQL 与 InnoDB 都能检测死锁并中止其中一个事务，让其他事务继续；选择谁做牺牲者不能由应用假定。InnoDB 的检测可配置关闭，关闭后要依赖锁等待超时等机制。死锁检测与超时不是同一件事：等待超时也可能只是对方很慢，并没有环。[14:2][17]

动作为什么有帮助做不到什么
同类操作按统一键顺序加锁减少 A→B 与 B→A 反向等待不能消除所有隐藏索引、外键与其他路径的冲突
缩短事务，及时提交降低锁重叠的时间不能替代正确隔离
为更新条件建合适索引少扫描、少锁不必要的记录热点行仍是热点
记录死锁与等待信息找到真正的语句、资源和顺序单靠一条报错看不出完整关系
有界退避并重试完整事务冲突消失后可以重新做业务判断不能无限重试，更不能把外部副作用重复执行

PostgreSQL 的序列化失败 SQLSTATE 是 40001，死锁是 40P01。重试要包括原来的读取、判断和全部写入，不能仅把失败的最后一条 UPDATE 再发一次。另一些错误只在特定业务逻辑下适合重试，不能把所有唯一冲突都当作瞬时故障。[18]

MySQL InnoDB 死锁通常回滚整个事务；锁等待超时的回滚范围还受设置影响，默认行为不能直接等同死锁。驱动返回错误后，应用应明确结束旧事务，再按错误类别决定是否重来。[19]

最后一种情况比死锁更棘手：发送 COMMIT 后连接断了。应用不知道服务器是提交成功后回包丢失，还是根本没有提交。此时盲目重做扣款可能造成重复，应该通过业务唯一键、幂等记录和结果查询消除不确定性。事务解决数据库内部提交，应用仍需要管理客户端观察不到结果的窗口。

验证范围 ​

本篇真实运行结果为：

text
rollback=ok commit=ok savepoint=ok conditional-debit=ok expected-error=ok sequence-gap=ok

校验脚本同时检查数据库输出和预期 CHECK 错误，而非把进程退出零简单当作成功。没有测试双会话脏读、幻读、写偏差、死锁、间隙锁，也没有测试掉电持久性。那些结论分别来自以下固定版本官方资料与原始论文。single-user 模式官方就明确不提供真实进程间锁行为，不能用它补造并发结论。

参考资料 ​

完整复现材料 ​

前提：PostgreSQL 18.6 的 postgres/initdb、Python 3。新建可丢弃的数据目录，无其他进程使用，不连接生产数据库。将以下三个文件放在同一目录：

sh
export POSTGRES=/path/to/postgresql-18.6/bin/postgres
export PGDATA="$(mktemp -d)/data"
/path/to/postgresql-18.6/bin/initdb -D "$PGDATA" -A trust --no-locale -E UTF8
python3 verify_transactions.py

transactions.sql 含一个故意失败的扣款。run_single.py 发现 SQL ERROR 会退出 1，即使后面保存点恢复成功；verify_transactions.py 确认这是预期错误且全部结果正确，才输出成功。适配脚本只适合这些不在字符串或函数体中使用分号的固定测试，不是通用 SQL 解析器。

transactions.sql ​

sql
\set ON_ERROR_STOP on
CREATE TEMP TABLE accounts(id integer PRIMARY KEY,balance integer NOT NULL CHECK(balance>=0));
INSERT INTO accounts VALUES(1,100),(2,100);
\echo initial
SELECT id,balance FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts SET balance=balance-30 WHERE id=1;
UPDATE accounts SET balance=balance+30 WHERE id=2;
ROLLBACK;
\echo after-rollback
SELECT id,balance FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts SET balance=balance-30 WHERE id=1;
UPDATE accounts SET balance=balance+30 WHERE id=2;
COMMIT;
\echo after-commit
SELECT id,balance FROM accounts ORDER BY id;
BEGIN;
SAVEPOINT optional_step;
UPDATE accounts SET balance=balance-500 WHERE id=1;
ROLLBACK TO SAVEPOINT optional_step;
UPDATE accounts SET balance=balance+1 WHERE id=2;
COMMIT;
\echo after-savepoint-recovery
SELECT id,balance FROM accounts ORDER BY id;
\echo atomic-conditional-debit
UPDATE accounts SET balance=balance-80 WHERE id=1 AND balance>=80 RETURNING id;
SELECT id,balance FROM accounts ORDER BY id;
CREATE TEMP SEQUENCE demo_seq START 1;
BEGIN;
SELECT nextval('demo_seq') AS issued_inside_transaction;
ROLLBACK;
\echo sequence-after-rollback
SELECT nextval('demo_seq') AS next_issued;

run_single.py ​

代码块 · 42 行 · 点击收起 / 展开
python
"""Run the deliberately simple fixtures shipped with these articles.
Input: ordinary SQL and optional psql \\set/\\pset/\\echo directives.
This is a fixture adapter, not a general SQL parser. No fixture may contain
semicolons inside quoted values, comments, function bodies, or identifiers.
"""
import os
from pathlib import Path
import re
import subprocess
import sys

sql = Path(sys.argv[1]).read_text()
lines = []
for line in sql.splitlines():
    if line.startswith('\\echo '):
        label = line[len('\\echo '):].replace("'", "''")
        lines.append("SELECT '" + label + "';")
    elif line.startswith('\\'):
        continue
    else:
        lines.append(line)
commands = []
for part in '\n'.join(lines).split(';'):
    part = part.strip()
    if not part:
        continue
    # COPY gives legible values; it does not change the SELECT's semantics.
    if re.match(r'(?i)^(SELECT|WITH)\b', part):
        part = "COPY (" + part + ") TO STDOUT WITH (DELIMITER '|', NULL 'NULL')"
    commands.append(part + ';\n\n')
run = subprocess.run(
    [os.environ.get('POSTGRES', 'postgres'), '--single', '-j',
     '-D', os.environ['PGDATA'], 'postgres'],
    input=''.join(commands), text=True, capture_output=True)
print(run.stdout, end='')
print(run.stderr, end='', file=sys.stderr)
if run.returncode:
    sys.exit(run.returncode)
# The stand-alone backend may recover from a SQL error and exit zero.
# An ERROR is still a failed fixture; expected failures are checked outside.
if re.search(r'\b(ERROR|FATAL|PANIC):', run.stderr):
    sys.exit(1)

verify_transactions.py ​

python
from pathlib import Path
import subprocess
import sys

here = Path(__file__).resolve().parent
runner = here.parent / 'common' / 'run_single.py'
# In a standalone copy, put run_single.py next to this verifier.
if not runner.exists():
    runner = here / 'run_single.py'
p = subprocess.run([sys.executable, str(runner), str(here/'transactions.sql')],
                   capture_output=True, text=True)
text = p.stdout.replace('backend> ', '')
assert p.returncode == 1, (p.returncode, p.stderr)
assert 'violates check constraint "accounts_balance_check"' in p.stderr
for expected in [
    'initial\n1|100\n2|100',
    'after-rollback\n1|100\n2|100',
    'after-commit\n1|70\n2|130',
    'after-savepoint-recovery\n1|70\n2|131',
]:
    assert expected in text, (expected, text)
assert '1|70\n2|131' in text
assert 'issued_inside_transaction' not in text  # SELECT is emitted through COPY
assert 'sequence-after-rollback\n2' in text
print('rollback=ok commit=ok savepoint=ok conditional-debit=ok expected-error=ok sequence-gap=ok')

  1. PostgreSQL 18 Transactions Tutorial。 ↩︎

  2. PostgreSQL 18 WAL Introduction。 ↩︎

  3. PostgreSQL 18 WAL 配置与同步提交。 ↩︎

  4. PostgreSQL 18 Transaction Isolation。 ↩︎ ↩︎ ↩︎ ↩︎ ↩︎ ↩︎ ↩︎

  5. PostgreSQL 18 SAVEPOINT。 ↩︎

  6. PostgreSQL 18 ROLLBACK TO SAVEPOINT。 ↩︎

  7. Berenson 等，A Critique of ANSI SQL Isolation Levels，1995。 ↩︎ ↩︎

  8. MySQL 8.4 InnoDB Isolation Levels。 ↩︎ ↩︎

  9. MySQL 8.4 Consistent Nonlocking Reads。 ↩︎ ↩︎ ↩︎

  10. MySQL 8.4 Locking Reads。 ↩︎

  11. PostgreSQL 18 MVCC Introduction。 ↩︎

  12. PostgreSQL 18 Routine Vacuuming。 ↩︎

  13. MySQL 8.4 InnoDB Multi-Versioning。 ↩︎

  14. PostgreSQL 18 Explicit Locking。 ↩︎ ↩︎ ↩︎

  15. MySQL 8.4 InnoDB Locking。 ↩︎ ↩︎

  16. MySQL 8.4 Next-Key Locking。 ↩︎

  17. MySQL 8.4 Deadlock Detection。 ↩︎

  18. PostgreSQL 18 Serialization Failure Handling。 ↩︎

  19. MySQL 8.4 InnoDB Error Handling；死锁处理建议。 ↩︎

保留各书来源与许可 · 示例运行条件以正文为准