外观
16 - 接口重试后为何出现重复数据:数据库事务与连接生命周期
摘要
扣减成功、增加失败,重试又执行一次,数据为什么乱了?本文用 SQLite 的有界教学实验解释事务边界、参数化 SQL 和连接关闭,并区分数据库原子性、并发冲突与接口幂等,帮助你判断失败后的真实状态。
版本范围:Python 3.14
sqlite3,示例支持 Python 3.13;实际 SQLite 运行库版本以sqlite3.sqlite_version为准。事务实验用显式 SQL 和autocommit=True,不依赖默认模式。独立示例已在 Windows 上使用 CPython 3.14.0(SQLite 3.50.4)与 3.13.7 执行通过。
1. 场景:没有收到成功响应,能不能再执行一次
教学场景中,一次转移操作先从账户 A 扣减三十,再给账户 B 增加三十。若第一条语句已经提交,第二条因约束失败,账户总量就发生错误。作者捕获异常后重试整个函数,A 又被扣了一次。问题首先是两条语句没有进入同一个事务,其次是没有确认上一轮执行到了哪一步。
另一个情况是两条语句已在一个事务里正确提交,但 HTTP 响应传输失败。客户端看到超时并重试,第二次又完成一笔转移。数据库事务没有失效:每次执行都是原子的;缺失的是对同一逻辑操作重复执行的控制。
保持同一笔30的转移,先按失败时机画状态:第一条已独立提交、第二条失败,丢的是整体原子性;两条一起提交、响应失败,丢的是调用方对结果的确认;同一意图再执行一次,缺的是幂等。判断顺序因此是“修改在什么提交边界内→失败前到哪一步→本次是不是原操作重试”。事务、操作标识与连接关闭分别回答一项,不能用“已经加事务”跳过后两项。
2. 连接、游标与事务不是同一个生命周期
connection(连接)是应用与数据库交互的会话对象,承载通信、会话状态和事务控制。cursor(游标)用于执行语句与获取结果,具体行为由驱动实现。transaction(事务)将一组数据库操作放进共同提交或回滚边界。连接可以执行多个事务,一个游标也不等于一个新事务。
SQLite 是嵌入式数据库,程序通过库访问本地文件或内存库,不需要外部服务器。本篇使用 :memory:,每个普通连接通常获得独立内存数据库,不能拿两个连接默认指向同一名称来假设共享同一份数据。生产 PostgreSQL、MySQL 的连接则通常有网络、认证和服务器资源,成本模型不同。
事务常用 ACID 概括:atomicity(原子性)强调整体提交或回滚,consistency(一致性)与约束及业务不变量相关,isolation(隔离性)控制并发事务相互观察,durability(持久性)涉及提交后的保存保证。不同数据库与配置对隔离和持久性的实现边界不同,不能从这四个词推出所有数据库行为相同。 可进一步阅读ACID与转账事务:ACID定义支持事务保证与边界。
本篇故意构造违反余额检查约束的第二条语句,观察第一条修改是否保留。它证明的是选定边界下的原子性,不测量崩溃持久性,不测试多连接隔离,也不模拟真实金融系统。教学状态和数据库文件都不离开进程。
3. Python 驱动模式与 SQLite 自己的事务状态
Python 3.12 起,sqlite3.connect 提供 autocommit 属性控制接口。Python 3.14 文档中的默认值仍为 LEGACY_TRANSACTION_CONTROL,不是无条件的 False。旧的 isolation_level 机制与新属性各有适用范围。默认行为可以在后续版本改变,因此正式代码应明确选择模式。sqlite3 事务控制文档分开说明两条控制路径。
autocommit=False 采用符合 DB-API 思路的事务管理,驱动尽量保持事务打开,调用 commit() 或 rollback() 后再次建立事务。autocommit=True 则启用 SQLite 的自动提交模式;Python 连接的 commit() 与 rollback() 在这种模式下不负责常规事务控制。注意 Python 属性与 SQLite 底层当前状态并不完全是同一个观察口径。
先预测同一连接上两次UPDATE能不能一起撤销,关键条件不在连接身份,而在事务是否已经开始。示例明确autocommit=True,以SQL BEGIN、COMMIT、ROLLBACK控制边界:没有BEGIN,每条独立修改各自提交;有BEGIN,修改要等待最终提交或回滚。Python connection.commit()/rollback()在本模式下不负责常规事务控制,不能替换手写SQL后仍沿用原预测。
传统模式下驱动什么时候自动开始事务、哪些语句触发管理,需要按具体 Python 版本与文档判断。with connection 的退出行为也依赖模式与当前事务。只复制一个 with 代码块,却没有说明连接如何创建,会把最重要的前提藏起来。
4. 最小实验:独立提交与整组回滚
保存为 examples/16_transactions.py,执行:
powershell
python docs/python/examples/16_transactions.py
python -c "import sqlite3; print(sqlite3.sqlite_version)"完整代码如下:
python
"""独立示例:SQLite 显式事务、约束失败回滚与参数化 SQL。"""
import sqlite3
from contextlib import closing
import json
def transfer(connection, amount):
connection.execute("BEGIN IMMEDIATE")
try:
connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'a'", (amount,))
connection.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'b'", (amount,))
connection.execute("COMMIT")
except BaseException:
if connection.in_transaction:
connection.execute("ROLLBACK")
raise
def idempotent_transfer(connection, operation_id, amount):
payload = json.dumps({"amount": amount}, sort_keys=True)
connection.execute("BEGIN IMMEDIATE")
try:
previous = connection.execute(
"SELECT payload, result FROM operations WHERE operation_id = ?", (operation_id,)
).fetchone()
if previous is not None:
if previous[0] != payload:
raise ValueError("same operation ID with different payload")
result = json.loads(previous[1])
else:
connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'a'", (amount,))
connection.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'b'", (amount,))
result = connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall()
result = [list(row) for row in result]
connection.execute("INSERT INTO operations VALUES (?, ?, ?)",
(operation_id, payload, json.dumps(result)))
connection.execute("COMMIT")
return result
except BaseException:
if connection.in_transaction:
connection.execute("ROLLBACK")
raise
if __name__ == "__main__":
# autocommit=True 保证示例只使用自己显式开启的 SQL 事务。
with closing(sqlite3.connect(":memory:", autocommit=True)) as connection:
connection.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK(balance >= 0))")
connection.executemany("INSERT INTO accounts VALUES (?, ?)", [("a", 100), ("b", 0)])
# 反例:两条语句各自提交,第二条失败后,第一条已经生效。
connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (30, "a"))
try:
connection.execute("UPDATE accounts SET balance = -1 WHERE name = ?", ("b",))
except sqlite3.IntegrityError:
pass
print("bad:", connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
connection.execute("UPDATE accounts SET balance = 100 WHERE name = ?", ("a",))
connection.execute("BEGIN")
try:
connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (30, "a"))
connection.execute("UPDATE accounts SET balance = -1 WHERE name = ?", ("b",))
connection.execute("COMMIT")
except BaseException as exc:
connection.execute("ROLLBACK") # 所有失败都清理,只消费本例预期的约束错误。
if not isinstance(exc, sqlite3.IntegrityError):
raise
print("rolled back:", True)
rows = connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall()
assert rows == [("a", 100), ("b", 0)]
print("good:", rows)
suspicious_name = "a' OR 1=1 --"
rows = connection.execute("SELECT name FROM accounts WHERE name = ?", (suspicious_name,)).fetchall()
assert rows == []
print("parameterized:", rows)
# 反例:每次转移都是原子的,响应丢失后的重试仍重复副作用。
for attempt in range(2):
transfer(connection, 30)
if attempt == 0:
try:
raise ConnectionError("response lost after commit")
except ConnectionError:
pass # 客户端只知道没有成功响应,接着发第二次尝试。
assert connection.execute("SELECT balance FROM accounts WHERE name='a'").fetchone()[0] == 40
print("unsafe retry:", connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
connection.execute("UPDATE accounts SET balance = CASE name WHEN 'a' THEN 100 ELSE 0 END")
connection.execute("CREATE TABLE operations(operation_id TEXT PRIMARY KEY, payload TEXT NOT NULL, result TEXT NOT NULL)")
result = idempotent_transfer(connection, "operation-1", 30)
try:
raise ConnectionError("response lost after committed idempotent operation")
except ConnectionError:
retried = idempotent_transfer(connection, "operation-1", 30)
assert result == retried == [["a", 70], ["b", 30]]
assert connection.execute("SELECT count(*) FROM operations").fetchone()[0] == 1
try:
idempotent_transfer(connection, "operation-1", 20)
except ValueError:
print("same ID different payload:", "rejected")
else:
raise AssertionError("conflicting payload accepted")
assert not connection.in_transaction
print("safe retry:", retried, "operations:", 1, "transaction open:", connection.in_transaction)在运行前把余额写成三站轨迹。初始100/0,反例第一条提交后70/0,第二条约束失败也留在70/0;正例第一条执行后尚未提交,第二条失败触发ROLLBACK,回到100/0。相同输入与失败语句,只有提交范围改变。代码断言与实测核对这些状态;带SQL片段的名字作为值绑定,没有匹配结果。SQLite运行库版本来自环境,不要求所有读者相同。
反例捕获约束错误只是让实验继续观察状态,不是生产错误处理建议。正例对所有失败先清理,只消费本例有意制造的 IntegrityError;未知失败重新抛出。真实代码还要考虑回滚自身失败怎样保留原异常、连接能否继续使用,以及提交状态是否可确认。
5. 原子性怎样覆盖多条语句
当没有显式事务,第一条扣减已完成提交,第二条报错不能撤销它。每条语句自身的原子性与多条语句组成操作的原子性不同。用同一连接执行并不自动证明多条语句有同一边界,连接存活时间也不等于事务范围。
正例先执行 BEGIN,随后第一条修改尚未完成最终提交,第二条失败后执行 ROLLBACK 撤销该事务的修改。约束错误通常使失败语句不能生效,但事务之后处于什么状态仍要按数据库和错误类别判断;这里明确执行回滚,不依赖偶然状态。
图表将在阅读到此处时加载…
业务不变量也需要验证。比如实际转移时 UPDATE 命中了零行,SQL 没有报错,不代表账户存在;或并发改变余额,检查之后的状态已不相同。可以让条件更新包含余额与版本条件,并检查受影响行数,或采用数据库相应隔离与锁策略。具体语法和冲突行为必须按数据库方言测试。
不要在事务里等待用户输入、外部 HTTP 或长时间 CPU 计算。它们可能延长锁与连接占用,加剧并发等待;外部副作用又无法由数据库回滚撤销。把可提前完成的工作放到事务外,事务里保持必要的状态检查与修改。跨系统一致性需要额外协议,不能通过一个本地 with 实现。
6. 参数化 SQL 把值与语句结构分开
parameterized query(参数化查询)将 SQL 结构和数据值分别交给驱动。示例 WHERE name = ? 与参数元组使引号、注释标记都作为名字内容处理。反例是字符串拼接 WHERE name = '...用户输入...',输入可能改变条件结构,造成注入或语法错误。
占位符只能表达值,不能随意替代表名、列名和排序方向。需要动态标识符时,应从可信的固定映射选择合法结构,而不是将用户输入直接拼进 SQL。不同驱动使用的占位符形式也不同,SQLite 的 ? 不能泛化为所有 Python 数据库驱动语法。
单个参数元组要写 (value,);(value) 只是括号表达式,不是单项元组。值是字符串时,错误传参可能被当成字符序列,引发绑定数量不匹配。这类错误属于驱动参数接口,不是数据库突然改变数据类型。
参数化并不能解决全部问题。合法的大范围查询仍可能耗时,合法路径仍可能访问未授权数据,合法字符串仍可能违反业务长度。它保护语句与值的结构边界,权限、容量、索引和业务校验继续独立负责。
7. with connection 不等于关闭连接
Python sqlite3.Connection 的上下文管理器主要处理事务提交或回滚,不负责自动关闭连接;在 autocommit=True 时相应退出操作也不构成本篇手写事务的管理。官方 连接上下文管理器说明明确指出它不隐式关闭连接。
再沿同一操作追资源:ROLLBACK结束本次事务,但连接对象仍可以活着;close结束连接使用责任,却不能替你决定某笔业务应成功提交。示例让contextlib.closing负责close,内部SQL负责事务。若改用外层closing、内层连接上下文,也要先选择驱动模式,再解释每个with的退出协议。这个模型能迁移到连接池:事务结束之后还应记录归还,而不是看见一次rollback就判断池槽位已经可用。
生产网络数据库常用 connection pool(连接池)复用连接,借出后必须在有限范围内归还。若异常分支没有归还、结果流尚未消费或事务仍打开,池中可用连接减少,请求开始等待。增加池容量只是改变阈值,不能修复生命周期泄漏,还可能压垮数据库。
归还池之前应保证事务与会话状态符合池的约定。隔离级别、临时表、用户变量和错误事务状态是否被重置,取决于驱动和池实现,需按版本核验。连接共享也有线程限制;SQLite 默认 check_same_thread=True 会阻止跨创建线程使用,关闭这项检查不自动让业务操作线程安全。
8. 事务与幂等为什么必须分别设计
如果上一轮事务已经提交,仅响应丢失,再次执行就可能重复修改。数据库没有办法从两次普通 INSERT 判断它们是不同用户操作还是同一操作重试。应用需要 operation ID(操作标识),将它与请求语义绑定,并在数据库里用唯一约束或其他受验证机制记录处理状态。
可靠幂等不是“先 SELECT 看有没有,再 INSERT”。两个并发请求可能都查到没有,再分别插入;必须让唯一性与状态更新进入数据库可保证的边界。若相同标识却携带不同参数,也应拒绝或明确处理,不能把旧结果随意返回。
需要同时修改业务表和记录操作结果时,两者应处于合理的一致性边界。提交后返回旧结果可以避免重复执行,但保存多久、失败状态是否可重试、进行中请求怎样等待,都属于接口设计。本文只解释机制,不把一行唯一键当成完整生产幂等组件。
外部消息与数据库提交的协调更复杂。提交成功但发消息失败、消息成功但提交失败,会产生不一致;可进一步研究 outbox(事务内保存待发送消息记录)等模式。它们也需要投递重试和消费者幂等,并非消除所有失败的魔法。
8.1 SQLite 的写锁边界与网络数据库不同
SQLite 的并发写入能力与本地文件锁、日志模式和事务方式有关。不能因为读取可以并发,就认为多个事务能同时修改任意记录而互不影响。BEGIN DEFERRED、BEGIN IMMEDIATE 等开启方式影响什么时候获取相应写入意图或锁,具体冲突应查 SQLite 事务文档,而不是套用某个网络数据库的行锁模型。
写入遇到忙状态时,连接超时与重试策略决定等待和失败行为,但增大等待不修复过长事务。先检查是谁持有写事务、是否在事务内做外部工作、是否及时提交,再决定容量与等待参数。WAL(Write-Ahead Logging,预写日志)模式可以改变读写协作方式,但不意味着任意数量写者能无限并行;开启后还要关注检查点和文件生命周期。
本文内存单连接实验没有锁冲突,所以不能证明生产并发隔离。若需要实验,应使用临时文件和两条明确连接,安排可控时序,让一条连接持有事务,另一条有限等待;记录实际 SQLite 版本和日志模式。不要在生产数据库人为制造长事务来确认课堂推断。
8.2 保存点不是独立提交
savepoint(保存点)为事务内部建立可回退位置,适合局部操作需要撤销的情况。回滚到保存点可以撤销其后的部分修改,释放保存点通常并不等于外层事务已完成持久提交。外层最终回滚时,先前的局部成功也可能一起撤销。
把函数内部每次执行都包成独立 BEGIN,如果调用方已在事务中,可能产生嵌套事务错误或意外边界。可复用数据访问函数应说明是否由调用方管理事务;需要局部回退时,再按数据库支持设计保存点。库函数擅自提交调用方的事务尤其危险,会提前固定其他尚未完成的修改。
例如接口业务调用“更新统计”和“记录操作结果”两个函数,若第一个函数自行提交,第二个失败后外层回滚已经无法恢复整体状态。看上去每个函数都有事务代码,整体操作却不原子。评审时应从业务操作向内追踪边界,而不是统计 commit 出现次数。
8.3 大结果集会延长资源持有
查询执行完成与结果消费完成不是同一时点。游标或流式结果可能在消费过程中保持连接、事务或服务器资源。把结果生成器返回给 HTTP 层之后,慢客户端会延长消费时间,数据库连接因此不能及时归还。这个等待链跨越存储与响应,分别看代码可能都没有明显慢操作。
可以选择限定结果数量、分批取出并及时释放数据库资源,或用短事务读取快照后再发送。每种方案都有内存、延迟与一致性代价:全量物化减少连接持有却增加内存,分页减少单次数据但可能看到变化,长快照保持一致却占用资源。正确策略依据业务需要,而不是把“流式查询”视为无条件优化。
出错时需要关闭未完成结果并归还连接,取消请求也要覆盖这条路径。修复后观察池中持有时长和等待数是否恢复,不能只证明单次 SQL 更快。排障的实际目标是恢复完整资源链的稳定行为。
8.4 提交时的失败比语句失败更难处理
语句约束失败通常能明确定位到事务内的一步,提交阶段连接中断则可能让客户端无法判断最终结果。对网络数据库,不能把所有提交异常统一解释成已回滚;需按驱动、服务端日志和操作标识确认状态。无法确认时应进入可恢复的业务流程,而不是无限重试。
SQLite 本地实验没有网络,因此没有验证这个不确定状态。它提供了原子性的基础模型,后端应用还需要在实际数据库上验证连接失效与提交边界。文章将两种证据分开,是为了避免正确的本地实验被过度推广到生产协议。
这些阶段都应保留明确的失败语义,便于安全恢复。
9. 排障与面试延伸
数据库异常后能直接重试吗? 要知道事务是否回滚、连接是否可用、提交状态是否确定,以及操作是否幂等。连接超时不等于提交失败,错误类别和发生阶段决定下一步。
同一连接上的两条语句一定是一个事务吗? 不一定,由驱动模式和显式边界决定。示例反例就是同一连接但独立提交。
连接池耗尽该怎么查? 观察借出数、等待队列、持有时长、未完成结果和异常路径归还责任,再关联数据库事务与锁等待。不要只看池上限或总请求耗时。
实际排障先保留请求标识和数据库错误码,再找提交、回滚与归还时点。SQL 日志应脱敏,并控制记录量;不要为了排查把参数中的凭据、个人信息与整段大文本全部输出。修复后同时验证数据不变量和连接占用恢复,才能形成闭环。
同一转账的第二种失败:提交后丢失响应
提交成功后丢失响应:把重试前后状态列出来
现在只把失败移到COMMIT之后,预测ROLLBACK还能否恢复100/0。实验真实提交后由教学代码抛ConnectionError模拟响应丢失,并非SQLite网络故障。普通重试再次转30,最后40/60;总额守恒,却多执行了一次业务。幂等版把operation_id、参数、业务修改与原结果同边界保存,同ID同参数得到70/30且操作表只有一条;金额改变则拒绝。对照表的关键量从“总额是否守恒”扩展为“同一意图产生几次效果”。
此处 BEGIN IMMEDIATE 为 SQLite 的写者串行化边界,错误路径只在真实 in_transaction 为真时回滚,最后断言事务退出。它不等于所有数据库都应使用同一语法;并发连接与锁等待的可运行实验进一步放在Deadline、幂等与并发重试。响应超时不会自动撤销已提交结果,调用方应凭操作标识查状态,而不是换一个新标识重试。
独立能力任务:分别重现“第二条语句失败”“提交后响应丢失”“同键异参数”三条路径。验收检查余额、操作表数量和事务状态,说明哪种路径可回滚、哪种只能查询原结果。真实连接池的借出与归还实证见连接池、ORM 与迁移,事务退出和连接归还应分别记录。
10. 结尾
事务保证一次执行内部的提交边界,连接管理保证资源归还,幂等保证同一逻辑操作重试时不会重复产生效果。
- 显式说明驱动事务模式,语句原子性、操作原子性与提交状态分别判断。
- 参数化 SQL 保护结构边界,连接关闭与事务退出是不同责任。
- 重试先确认状态并建立幂等,连接池等待要追踪持有与归还链。