421 lines
15 KiB
Markdown
421 lines
15 KiB
Markdown
# 第 4-2 课：Python 数据库事务与数据访问层
|
||
|
||
## 一、本课定位
|
||
|
||
第一课已经完成 PostgreSQL 连接、游标、参数化查询和资源释放。本课先系统复习事务的核心概念，再学习 Python/Psycopg 中的事务边界，以及如何把 SQL 从业务逻辑中分离。数据库基础不会展开成完整 SQL 课程，但事务是后续 ORM 和 Web 业务正确性的基础，必须讲清楚。
|
||
|
||
本课会写入远程练习数据库：示例创建专用表 `course_bank_account`，只重置并操作 `COURSE-` 前缀数据；练习使用另一张专用表 `course_wallet_account`，只操作 `PRACTICE-` 前缀数据。不要连接生产数据库。
|
||
|
||
## 二、本课目标
|
||
|
||
完成本课后，你将能够：
|
||
|
||
1. 说明事务是什么以及为什么需要事务；
|
||
2. 结合转账解释原子性、一致性、隔离性和持久性；
|
||
3. 区分提交、回滚、自动提交和事务失败状态；
|
||
4. 解释常见并发异常和事务隔离级别；
|
||
5. 解释 Psycopg 默认事务行为；
|
||
6. 使用连接上下文自动提交和回滚；
|
||
7. 理解为什么异常必须传播出事务上下文；
|
||
8. 使用 `executemany()` 批量执行同一条参数化 SQL；
|
||
9. 使用 `FOR UPDATE` 锁定待修改数据；
|
||
10. 使用 Repository 隔离 SQL；
|
||
11. 把业务规则放在 Service 中；
|
||
12. 让一次业务操作共享同一连接和事务；
|
||
13. 对照 JDBC、MyBatis 和 Spring 事务理解 Python实现。
|
||
|
||
## 三、事务是什么
|
||
|
||
事务（Transaction）是数据库中的一个工作单元：它包含一条或多条操作，这些操作应该作为一个不可分割的整体完成。
|
||
|
||
转账至少包含两条更新：
|
||
|
||
```text
|
||
账户A扣款200元
|
||
账户B入账200元
|
||
```
|
||
|
||
如果第一条成功、第二条失败，却保留了第一条结果，钱就凭空减少了。事务要求最终只能出现两种结果：
|
||
|
||
```text
|
||
全部成功 → 提交（COMMIT）
|
||
任一步失败 → 回滚（ROLLBACK），恢复到事务开始前
|
||
```
|
||
|
||
对应的SQL概念是：
|
||
|
||
```sql
|
||
BEGIN;
|
||
|
||
UPDATE account SET balance = balance - 200 WHERE account_no = 'A';
|
||
UPDATE account SET balance = balance + 200 WHERE account_no = 'B';
|
||
|
||
COMMIT;
|
||
```
|
||
|
||
中间发生错误时执行：
|
||
|
||
```sql
|
||
ROLLBACK;
|
||
```
|
||
|
||
使用Psycopg时通常不需要手写`BEGIN`。驱动会按连接状态自动开始事务，代码负责正确划定提交和回滚边界。
|
||
|
||
## 四、事务的ACID特性
|
||
|
||
ACID是事务需要满足的四类核心性质。
|
||
|
||
### 4.1 原子性（Atomicity）
|
||
|
||
事务内的操作要么全部成功，要么全部失败。转账中的扣款和入账不能只保留其中一步。
|
||
|
||
本课的失败示例会先扣除50元，再主动抛出异常。最终余额不变，就是在验证原子性。
|
||
|
||
### 4.2 一致性（Consistency）
|
||
|
||
事务执行前后，数据都必须满足数据库约束和业务规则。例如：
|
||
|
||
- 余额不能小于0；
|
||
- 转账前后两个账户的余额总额不应无故变化；
|
||
- 目标账户必须存在；
|
||
- 主键、非空和检查约束仍然成立。
|
||
|
||
一致性不是只靠数据库自动保证。数据库约束、事务、锁和Service中的业务校验要共同工作。
|
||
|
||
### 4.3 隔离性（Isolation）
|
||
|
||
多个事务并发执行时，一个事务不应随意看到另一个尚未完成事务的中间状态。
|
||
|
||
假设账户A余额为1000元，两个请求同时转出800元。如果二者都先读取到1000，再分别扣款，就可能发生超额转账。事务隔离级别和行锁用于控制这种并发影响。
|
||
|
||
隔离不等于所有事务完全串行。隔离越强，并发冲突通常越少，但等待和资源成本可能越高。
|
||
|
||
### 4.4 持久性（Durability）
|
||
|
||
事务成功提交后，结果应该持久保存。程序退出或连接关闭后，再使用新连接查询，仍然能够看到已提交的余额。
|
||
|
||
本课使用独立连接回查成功转账结果，就是在直观验证持久性。
|
||
|
||
## 五、提交、回滚与事务状态
|
||
|
||
### 5.1 提交
|
||
|
||
`COMMIT`确认事务中的修改。提交成功后，其他事务才能按照隔离规则观察到这些结果，当前事务也不能再整体撤销。
|
||
|
||
### 5.2 回滚
|
||
|
||
`ROLLBACK`取消当前事务中尚未提交的修改。它不是反向执行一条新的补偿SQL，而是让数据库放弃本次事务的未提交结果。
|
||
|
||
### 5.3 PostgreSQL事务失败状态
|
||
|
||
PostgreSQL事务中的某条SQL失败后，当前事务通常进入失败状态。即使后面的SQL本身正确，也会收到类似错误：
|
||
|
||
```text
|
||
current transaction is aborted, commands ignored until end of transaction block
|
||
```
|
||
|
||
中文含义是：当前事务已经失败，在事务结束前忽略后续命令。此时必须回滚，或让Psycopg的连接上下文因异常退出并自动回滚。
|
||
|
||
### 5.4 自动提交
|
||
|
||
自动提交（Autocommit）表示每条独立SQL完成后立即提交。它适合部分不需要多语句原子性的操作，但无法把扣款和入账自动组合成一个事务。
|
||
|
||
Psycopg默认`autocommit=False`。本课保持默认行为，不开启自动提交。
|
||
|
||
## 六、隔离级别与并发异常
|
||
|
||
常见并发异常如下：
|
||
|
||
| 并发现象 | 含义 |
|
||
|---|---|
|
||
| 脏读（Dirty Read） | 读取到其他事务尚未提交的数据；对方回滚后，读到的数据从未真正成立 |
|
||
| 不可重复读（Non-repeatable Read） | 同一事务两次读取同一行，期间被其他事务提交修改，结果不同 |
|
||
| 幻读（Phantom Read） | 同一事务两次执行相同范围查询，结果行数量因其他事务提交而变化 |
|
||
| 丢失更新（Lost Update） | 两个事务基于同一旧值更新，后提交的结果覆盖前一个结果 |
|
||
|
||
SQL标准定义四个主要隔离级别：
|
||
|
||
| 隔离级别 | 基本含义 | PostgreSQL说明 |
|
||
|---|---|---|
|
||
| `READ UNCOMMITTED` | 理论上允许读取未提交数据 | PostgreSQL内部按`READ COMMITTED`处理 |
|
||
| `READ COMMITTED` | 每条语句读取执行开始前已提交的数据 | PostgreSQL默认级别 |
|
||
| `REPEATABLE READ` | 事务内多次查询基于稳定快照 | 提交时仍可能出现并发冲突 |
|
||
| `SERIALIZABLE` | 尽量表现得像事务串行执行 | 冲突时可能要求应用重试事务 |
|
||
|
||
隔离级别不能代替所有业务并发控制。本课在默认`READ COMMITTED`下使用`SELECT ... FOR UPDATE`锁住即将修改的账户行。
|
||
|
||
## 七、JDBC与Psycopg事务对照
|
||
|
||
| Java常见写法 | Psycopg写法 | 含义 |
|
||
|---|---|---|
|
||
| `connection.setAutoCommit(false)` | 默认连接首次操作自动进入事务 | 开始事务工作 |
|
||
| `connection.commit()` | 正常离开连接`with` | 提交 |
|
||
| `connection.rollback()` | 异常离开连接`with` | 回滚 |
|
||
| `try-with-resources` | `with psycopg.connect(...)` | 管理连接生命周期 |
|
||
| Mapper/DAO | Repository | 封装SQL和结果转换 |
|
||
| Service | Service | 组织业务规则 |
|
||
| `@Transactional` | 外层连接/事务上下文 | 划定事务边界 |
|
||
|
||
Python没有Spring默认提供的声明式`@Transactional`。直接使用Psycopg时，需要显式组织事务上下文；后续SQLAlchemy和FastAPI课程会进一步统一Session生命周期。
|
||
|
||
## 八、Psycopg默认事务行为
|
||
|
||
Psycopg遵循DB-API习惯：连接默认不是自动提交模式。包括`SELECT`在内的数据库操作通常都会启动事务。
|
||
|
||
```python
|
||
with psycopg.connect(**database_config) as connection:
|
||
connection.execute("UPDATE ...")
|
||
```
|
||
|
||
正常离开`with`时提交；块内异常传播出去时回滚；最后关闭连接。
|
||
|
||
### 8.1 异常不能在事务内部被吞掉
|
||
|
||
错误写法：
|
||
|
||
```python
|
||
with psycopg.connect(**database_config) as connection:
|
||
try:
|
||
connection.execute("UPDATE ...")
|
||
raise TransferError("后续失败")
|
||
except TransferError:
|
||
print("失败")
|
||
```
|
||
|
||
异常已经在`with`内部被捕获，连接上下文看到的是“正常结束”，可能提交前面的更新。
|
||
|
||
正确边界：
|
||
|
||
```python
|
||
try:
|
||
with psycopg.connect(**database_config) as connection:
|
||
connection.execute("UPDATE ...")
|
||
raise TransferError("后续失败")
|
||
except TransferError as error:
|
||
print(error)
|
||
```
|
||
|
||
异常先离开`with`，触发回滚，然后才由外层处理。
|
||
|
||
### 8.2 手动提交和回滚
|
||
|
||
连接上下文适合“整个代码块就是一个事务”的情况，也可以显式控制：
|
||
|
||
```python
|
||
connection = psycopg.connect(**database_config)
|
||
|
||
try:
|
||
connection.execute("UPDATE ...")
|
||
connection.execute("UPDATE ...")
|
||
connection.commit()
|
||
except Exception:
|
||
connection.rollback()
|
||
raise
|
||
finally:
|
||
connection.close()
|
||
```
|
||
|
||
这与JDBC手动事务非常接近。但只要业务边界能自然表达为代码块，优先使用`with`，可以减少遗漏回滚或关闭连接的风险。
|
||
|
||
### 8.3 连接with与事务with的区别
|
||
|
||
本课使用：
|
||
|
||
```python
|
||
with psycopg.connect(**database_config) as connection:
|
||
...
|
||
```
|
||
|
||
它同时管理事务和连接生命周期。Psycopg还提供：
|
||
|
||
```python
|
||
with connection.transaction():
|
||
...
|
||
```
|
||
|
||
后者只划定一个事务或保存点范围，不负责创建连接。它适合长连接或连接池场景。本阶段后续课程结合连接池时再深入使用。
|
||
|
||
## 九、Repository与Service职责
|
||
|
||
本课采用：
|
||
|
||
```text
|
||
main
|
||
↓ 创建连接并划定事务
|
||
Service
|
||
↓ 组织转账规则
|
||
Repository
|
||
↓ 执行参数化SQL
|
||
PostgreSQL
|
||
```
|
||
|
||
Repository不应在每个方法中调用`commit()`，否则扣款刚提交、入账却失败时，外层已经无法回滚整个业务操作。
|
||
|
||
```python
|
||
class AccountRepository:
|
||
def __init__(self, connection):
|
||
self.connection = connection
|
||
|
||
def change_balance(self, account_no, amount):
|
||
self.connection.execute(...)
|
||
# 这里不提交。
|
||
```
|
||
|
||
Service也不负责创建连接，它复用同一个Repository，从而保证多个SQL处于同一事务。
|
||
|
||
## 十、FOR UPDATE、锁与死锁
|
||
|
||
转账前先查询余额：
|
||
|
||
```sql
|
||
SELECT balance
|
||
FROM course_bank_account
|
||
WHERE account_no = %s
|
||
FOR UPDATE
|
||
```
|
||
|
||
`FOR UPDATE`会锁定选中的行，直到事务提交或回滚。它能防止两个并发事务同时读取相同旧余额后分别扣款。
|
||
|
||
这种“先锁定，再判断和修改”的做法属于悲观锁：代码假设并发冲突可能发生，因此提前取得排他性的行锁。
|
||
|
||
锁会持续到事务提交或回滚。事务范围过大，会让其他请求等待更久，因此事务中不应夹杂耗时的网络请求、人工操作或无关计算。
|
||
|
||
### 10.1 死锁
|
||
|
||
如果事务一先锁A再锁B，事务二同时先锁B再锁A，双方可能互相等待。数据库会检测死锁并中止其中一个事务。
|
||
|
||
降低死锁风险的常用办法：
|
||
|
||
- 多个事务按照统一顺序锁定资源，例如始终按账号升序；
|
||
- 缩短事务时间；
|
||
- 只锁真正需要修改的行；
|
||
- 应用捕获死锁或序列化失败，并按策略重试整个事务。
|
||
|
||
## 十一、批量操作
|
||
|
||
多条数据执行相同SQL时，可以使用：
|
||
|
||
```python
|
||
with connection.cursor() as cursor:
|
||
cursor.executemany(
|
||
"INSERT INTO course_bank_account VALUES (%s, %s, %s)",
|
||
accounts,
|
||
)
|
||
```
|
||
|
||
它比手工拼接多条SQL安全，也明确表达“同一语句、不同参数”。它不等于无限制地一次提交海量数据；生产中仍要根据数据量分批。
|
||
|
||
## 十二、完整示例
|
||
|
||
示例文件为[transaction_repository_example.py](./transaction_repository_example.py)，包含：
|
||
|
||
- `AccountRepository`：建表、初始化、查询和更新；
|
||
- `TransferService`：金额检查、余额检查、扣款和入账；
|
||
- 成功事务：转账200元并自动提交；
|
||
- 失败事务：先扣50元再抛出异常，验证自动回滚；
|
||
- 独立连接回查：证明提交和回滚的最终状态。
|
||
|
||
## 十三、准备配置
|
||
|
||
在第二课目录执行：
|
||
|
||
```powershell
|
||
Copy-Item .\config.example.toml .\config.toml
|
||
```
|
||
|
||
填写第一课使用的同一套专用练习数据库配置。`config.toml`已被项目`.gitignore`排除。
|
||
|
||
## 十四、运行方法与预期结果
|
||
|
||
激活已安装Psycopg的Conda环境：
|
||
|
||
```powershell
|
||
conda activate python-test
|
||
python .\transaction_repository_example.py
|
||
```
|
||
|
||
关键结果应为：
|
||
|
||
```text
|
||
初始余额：
|
||
COURSE-A001｜小明｜余额：1000.00
|
||
COURSE-A002｜小红｜余额：500.00
|
||
成功转账 200 元后：
|
||
COURSE-A001｜小明｜余额：800.00
|
||
COURSE-A002｜小红｜余额：700.00
|
||
失败事务已回滚：模拟第二步失败，验证前一步更新会被回滚。
|
||
失败事务回滚后：
|
||
COURSE-A001｜小明｜余额：800.00
|
||
COURSE-A002｜小红｜余额：700.00
|
||
```
|
||
|
||
重复运行时，示例会先删除`COURSE-`前缀数据并重新初始化，因此结果保持一致。
|
||
|
||
## 十五、常见错误
|
||
|
||
### 15.1 Repository内部提交
|
||
|
||
这会破坏跨多个SQL的原子性。提交和回滚应由业务事务边界统一控制。
|
||
|
||
### 15.2 在with内部捕获业务异常
|
||
|
||
异常没有传播给连接上下文，可能导致错误提交。先让异常离开事务块，再在外层捕获。
|
||
|
||
### 15.3 失败后继续使用同一事务
|
||
|
||
PostgreSQL语句失败后，当前事务通常进入失败状态。在回滚前继续执行SQL，会收到`current transaction is aborted`一类错误。
|
||
|
||
### 15.4 使用float表示金额
|
||
|
||
二进制浮点数可能产生精度误差。课程金额使用`Decimal("200.00")`，数据库使用`NUMERIC(12, 2)`。
|
||
|
||
### 15.5 先查询再更新却没有锁
|
||
|
||
单用户测试可能正常，但并发时可能发生余额覆盖或超额扣款。本课使用`FOR UPDATE`锁定账户行。
|
||
|
||
### 15.6 清理范围过大
|
||
|
||
不要使用无条件`DELETE`或`TRUNCATE`。示例和练习只删除指定前缀的课程数据。
|
||
|
||
## 十六、课堂练习
|
||
|
||
打开[practice.py](./practice.py)，实现独立的钱包转账练习。题目已经明确：
|
||
|
||
1. 配置读取；
|
||
2. Repository方法；
|
||
3. Service业务规则；
|
||
4. 批量初始化；
|
||
5. 成功事务；
|
||
6. 失败回滚；
|
||
7. 回查、输出和异常处理。
|
||
|
||
## 十七、本课小结
|
||
|
||
- 事务把多条数据库操作组织成一个工作单元；
|
||
- ACID分别是原子性、一致性、隔离性和持久性；
|
||
- 提交确认修改，回滚取消尚未提交的修改；
|
||
- PostgreSQL事务中的SQL失败后，通常必须先回滚才能继续；
|
||
- 隔离级别控制并发事务互相可见的范围；
|
||
- 事务边界应该覆盖完整业务操作；
|
||
- Repository封装SQL，但不擅自提交；
|
||
- Service组织业务规则，但复用外部连接；
|
||
- 异常必须先离开事务上下文才能触发自动回滚；
|
||
- `executemany()`适合相同SQL的多组参数；
|
||
- `FOR UPDATE`用于锁定即将修改的数据；
|
||
- 金额应使用`Decimal`与数据库`NUMERIC`。
|
||
|
||
## 十八、验收标准
|
||
|
||
- 能使用转账说明为什么需要事务；
|
||
- 能结合示例解释ACID四个特性；
|
||
- 能区分提交、回滚和自动提交；
|
||
- 能简要说明脏读、不可重复读、幻读和丢失更新；
|
||
- 能解释连接上下文何时提交、何时回滚；
|
||
- 能说明为什么Repository不能随意提交；
|
||
- 标准示例重复运行且结果一致；
|
||
- 练习的成功转账同时更新两个钱包；
|
||
- 模拟失败后第一条更新被完整回滚；
|
||
- SQL全部参数化；
|
||
- 只操作课程专用表和指定前缀数据；
|
||
- 未提交真实`config.toml`。
|