Junie's Blog

先查后写依然报重复冲突，SQLite 并发写入踩坑记录

全文共 1626 字预计阅读 6 分钟

在 Auto Email Sender 的诊断日志里，我偶然抓到了好几条刺眼的错误堆栈：

sqlite3.IntegrityError: UNIQUE constraint failed: llm_adapter_cache.endpoint, llm_adapter_cache.model_name

出问题的是一张用于保存 LLM 模型能力自适应探测结果的本地缓存表。

我当时第一反应是：“这怎么可能？我在写入前明明写了非常严密的检查代码啊！”

翻开当年的代码实现，逻辑简单清晰得像教科书：

# 当年自以为稳健的代码
cached = db.query(LLMAdapterCache).filter_by(endpoint=ep, model_name=model).first()
if cached:
    cached.config = new_config
    cached.updated_at = now()
else:
    new_record = LLMAdapterCache(endpoint=ep, model_name=model, config=new_config)
    db.add(new_record)
db.commit()

“先查，有就改，没有就插。” 只要是个程序员都写过类似的代码。

既然 db.query 已经确认过记录不存在了，为什么几毫秒后的 db.add 依然会一头撞死在唯一索引约束（Unique Constraint）上？

经典的 TOCTOU 时间峡谷

答案是：这行代码在单线程下岁月静好，但在并发面前千疮百孔。

通俗比喻：抢占自习室座位的尴尬

为什么“先查一下有没有，没有就新建”在并发时一定会撞车？ 想象一下你去自习室占座：

  1. 你（线程 A）看了看 1 号桌，发现空着没人；
  2. 几乎在同一秒，另一个人（线程 B）也看了一眼 1 号桌，发现也空着；
  3. 你去洗手间洗了把脸，回来把书包放桌上（成功写入）；
  4. 另一个人拿着两秒前“桌子没人”的过期印象，也把电脑放到了 1 号桌上。

这时候两人肯定要吵架了——对应到数据库里，就是当场抛出 UNIQUE constraint failed。 “你查到的没被占”只代表历史，不代表下一毫秒没人插队。 在多任务并发的世界里，检查和写入如果不绑成一个原子操作，中间就是万丈深渊。

软件在批量生成邮件草稿时，前端会并发启动好几个任务，同时向同一个大模型网关发起调用。 这就制造了一个经典的并发竞态条件——在安全与并发领域被称为 TOCTOU（Time-of-Check to Time-of-Use，检查时间与使用时间的不一致）。

我们把时间轴放大到毫秒级：

时间戳协程 Worker A协程 Worker B
T1执行 SELECT 查询：此时数据库为空，返回 None
T2执行 SELECT 查询：此时 Worker A 还没写呢，同样返回 None！
T3执行 INSERT 并 commit：写入成功！
T4拿着 T2 查到的过期结论，也执行 INSERT 并 commit！
T5欢天喜地收工💥 崩溃：UNIQUE constraint failed！
检查只代表“历史”，不代表“未来”

Worker B 在 T2 时刻查到的“记录不存在”，是一个绝对诚实的客观历史事实。 但这个事实在 T3 时刻被 Worker A 悄悄修改了！ “查询”与“写入”是两条独立的数据库操作，中间隔着万丈深渊。没有锁或原子事务保护的“先查后写”，等同于裸奔。

抓异常再查？小心事务直接死锁报废

面对这种报错，不少人的第一反应是“加个 try-catch”：

try:
    insert_record()
except IntegrityError:
    db.rollback()
    # 既然别人已经插入了，那我重新查出来做更新
    record = query_again()
    update_record(record)

这种写法虽然勉强能跑，但在工程实现上极度恶心：

  1. 控制流异常反模式： 唯一索引冲突在并发场景下是高频必然事件，拿沉重的异常回滚来当常规业务分支，代码丑陋且极易拖慢性能；
  2. SQLAlchemy 事务污染： 一旦底层的 flush() 爆了 IntegrityError，整个 Session 就会陷入 inactive/failed 状态。在嵌套事务或外部复杂业务中，一个草率的 rollback() 会顺带把外层还没提交的其它正常数据一起冲进下水道！
数据库早就为我们准备了银弹

既然这是一个单纯的“不存在则插入、存在则覆盖”的场景，凭什么要在应用层做两次往返？SQLite 早在 3.24.0 就原生支持了现代 SQL 的 Upsert 语法（ON CONFLICT DO UPDATE）！

用原生 Upsert 把裁决权交还给数据库内核

让数据库在底层的一条原子语句里完成判断，才是最体面的架构选择。

在 SQLAlchemy Core 中，我们直接调用 SQLite 的方言语法：

from sqlalchemy.dialects.sqlite import insert
 
stmt = insert(LLMAdapterCache).values(
    endpoint=ep,
    model_name=model,
    config=new_config,
    updated_at=func.now()
)
 
# 核心：一条原子语句搞定冲突合并
upsert_stmt = stmt.on_conflict_do_update(
    index_elements=['endpoint', 'model_name'], # 冲突裁决的唯一键
    set_={
        'config': stmt.excluded.config,
        'updated_at': stmt.excluded.updated_at
    }
)
 
db.execute(upsert_stmt)
db.commit()

这一行重构带来的蜕变是立竿见影的：

  • 真正的原子性（Atomicity）： 插入和更新发生在同一个 B-Tree 事务锁内部，任何并发进来的请求都无法插入虚假的“时间缝隙”；
  • 网络与 I/O 减半： 彻底干掉了多余的 SELECT 查询，一条 SQL 搞定全流程；
  • 日志清爽： 告别了丑陋的 IntegrityError 异常追踪，把真正不可预期的数据库故障留给告警。
警惕 ORM Identity Map 的“幽灵旧值”

当你使用 Core 级别的 on_conflict_do_update 绕过 ORM 直接执行底层 SQL 时，要格外留神： 如果当前 Session 在此之前已经加载过对应的 ORM 对象，SQLAlchemy 的一级缓存（Identity Map）里存的依然是旧数据。 如果后续代码还要复用这个 ORM 对象，记得显式调用 db.refresh(obj) 或 db.expire(obj)，免得读到过期的内存幽灵。

什么时候不可以用覆盖式 Upsert？

最后必须要泼一盆冷水：原生 Upsert 绝不是“先查后写”的万能替代品。

在这次的 Bug 里，我们之所以能放飞自我地用 ON CONFLICT DO UPDATE，是因为：

  • 这是张自适应缓存表，新配置无条件覆盖旧配置（Last-Write-Wins）是完全符合业务预期的；
  • 冲突后没有多表级联依赖，也没有严格的状态机扭转。

但如果你的业务是：

  • 银行账户扣款/加款： 必须在原子层面做数值累加 balance = balance - 100，绝不能直接盲目覆盖；
  • 发信任务状态机： 任务必须按 pending -> sending -> sent 单向流转，一个处于 sent 的任务绝不能被迟到的并发请求重新 Upsert 回 sending！

在这类场景下，乐观锁版本号（Version Column）或严格的 WHERE status = 'pending' 条件更新才是正解。

总结

在编写涉及并发操作的存储层代码时，请时刻警惕脑海里的“单线程温室”：

  1. 别相信“我刚才查过了”： 在分布式和多线程世界里，过去发生的每一件事都在被光速改写；
  2. 原子性必须下沉到存储引擎： 能在一条 SQL（Upsert / CAS / Conditional Update）里解决的事情，永远不要试图在应用层内存里跨步解决；
  3. 分清幂等覆盖与状态流转： 缓存适合无脑覆盖，状态机则需要条件阻断。选对武器，才能在并发的惊涛骇浪中稳坐钓鱼船。

评论