AI Agent 工具重试为何重复写入？Python + SQLite 幂等键与结果回放实战

原创已于 2026-09-14 15:38:52 修改 · 97 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

于 2026-09-12 10:17:06 首次发布

Agent 调用“创建任务”工具后超时，重试却生成了两条一样的任务。这类故障不一定是模型忘了自己做过什么：可能第一次写入已经提交，只是调用方没有收到结果。

让模型在提示词里记住“不要重复操作”，无法补上数据库提交与响应到达之间的空隙。下面把副作用与可回放结果写在同一个 SQLite 事务里，验证重复调用、参数冲突、异常回滚和两连接竞争。

一、重试要复用操作身份

operation_key 应标识一次业务操作，而非某次网络尝试。调度器应在执行前生成并持久保存它；后续重试继续使用原键。模型重新生成的 tool call ID 是否稳定，需要单独核实，不能直接当作业务幂等键。

本例用 tenant 与 operation_key 共同定位操作，另存工具名、版本和参数。相同键且参数相同，返回已有结果；相同键但参数变化，拒绝执行；真正的新操作使用新键。输入只允许非空字符串，没有提供通用 JSON 归一化算法。

二、为什么两张表要一起提交

jobs 是实际创建的本地任务，receipts 保存参数和结果。先写 jobs 再单独提交 receipts，中间失败仍然可能重复创建；先提交成功标志再写 jobs，则可能留下没有真实任务的“成功”。

本例使用 BEGIN IMMEDIATE，在同一个事务内检查旧回执、创建任务并保存结果。SQLite 同一时刻只允许一个写事务，竞争可能等待或报 busy；这不是无限并发保证。机制见 SQLite 事务文档。

三、完整可运行示例

保存为 idempotent_tool_demo.py，执行 python idempotent_tool_demo.py。需要 Python 3.12 或更新版本以使用 autocommit 参数；本轮实际环境是 Python 3.13.13、SQLite 3.51.2。实验只创建临时数据库，不调用模型或外部服务。

这里显式使用 autocommit=True 加 SQL 的 BEGIN、COMMIT、ROLLBACK。不要把它与其他事务模式下的 Connection.commit() 用法混在一起，参考 Python sqlite3 事务控制。

"""Local SQLite effects only; does not call an LLM or external service."""
import json
import platform
import sqlite3
import tempfile
import threading
from concurrent.futures import ThreadPoolExecutor
from pathlib import Path


def connect(path):
    return sqlite3.connect(path, autocommit=True, timeout=5)


def setup(path):
    con = connect(path)
    try:
        con.executescript('''
            CREATE TABLE jobs(id INTEGER PRIMARY KEY, tenant TEXT NOT NULL, title TEXT NOT NULL);
            CREATE TABLE receipts(
                tenant TEXT NOT NULL, operation_key TEXT NOT NULL,
                payload TEXT NOT NULL, result TEXT NOT NULL,
                PRIMARY KEY(tenant, operation_key)
            );
        ''')
    finally:
        con.close()


def create_job(path, tenant, operation_key, title, fail_at=None):
    if not all(type(x) is str and x for x in (tenant, operation_key, title)):
        raise ValueError('non-empty strings required')
    payload = json.dumps({'tool': 'create_job', 'version': 1, 'title': title},
                         sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    con = connect(path)
    try:
        con.execute('BEGIN IMMEDIATE')
        old = con.execute('SELECT payload, result FROM receipts WHERE tenant=? AND operation_key=?',
                          (tenant, operation_key)).fetchone()
        if old is not None:
            if old[0] != payload:
                raise ValueError('operation key reused with different arguments')
            con.execute('COMMIT')
            return json.loads(old[1])
        cur = con.execute('INSERT INTO jobs(tenant, title) VALUES(?, ?)', (tenant, title))
        result = {'job_id': cur.lastrowid, 'title': title}
        if fail_at == 'before_receipt':
            raise RuntimeError('injected failure before receipt')
        con.execute('INSERT INTO receipts VALUES(?, ?, ?, ?)',
                    (tenant, operation_key, payload, json.dumps(result, ensure_ascii=False)))
        con.execute('COMMIT')
        if fail_at == 'after_commit':
            raise TimeoutError('injected lost acknowledgement after commit')
        return result
    except BaseException:
        if con.in_transaction:
            con.execute('ROLLBACK')
        raise
    finally:
        con.close()


def count_jobs(path):
    con = connect(path)
    try:
        return con.execute('SELECT COUNT(*) FROM jobs').fetchone()[0]
    finally:
        con.close()


def must_raise(kind, call):
    try:
        call()
    except kind:
        return
    raise AssertionError(f'expected {kind.__name__}')


def main():
    print(f'Python {platform.python_version()} / SQLite {sqlite3.sqlite_version}')
    with tempfile.TemporaryDirectory() as tmp:
        path = Path(tmp) / 'demo.sqlite3'
        setup(path)
        first = create_job(path, 'team-A', 'op-1', '整理采集报告')
        assert count_jobs(path) == 1
        print('PASS first call: one job')
        assert create_job(path, 'team-A', 'op-1', '整理采集报告') == first
        assert count_jobs(path) == 1
        print('PASS retry through new connection: same receipt, still one job')
        must_raise(ValueError, lambda: create_job(path, 'team-A', 'op-1', '改了内容'))
        assert count_jobs(path) == 1
        print('PASS same key with different arguments: rejected')
        must_raise(RuntimeError, lambda: create_job(path, 'team-A', 'op-2', '第二个任务', 'before_receipt'))
        assert count_jobs(path) == 1
        print('PASS failure before receipt: effect rolled back')
        create_job(path, 'team-A', 'op-2', '第二个任务')
        assert count_jobs(path) == 2
        print('PASS retry after rollback: one new job')
        must_raise(TimeoutError, lambda: create_job(path, 'team-A', 'op-3', '第三个任务', 'after_commit'))
        assert count_jobs(path) == 3
        recovered = create_job(path, 'team-A', 'op-3', '第三个任务')
        assert recovered['job_id'] == 3 and count_jobs(path) == 3
        print('PASS lost acknowledgement: committed result replayed')
        create_job(path, 'team-B', 'op-1', '整理采集报告')
        assert count_jobs(path) == 4
        print('PASS separate tenant scope: independent operation')
        barrier = threading.Barrier(2)
        def contender():
            barrier.wait(timeout=5)
            return create_job(path, 'team-A', 'op-4', '并发任务')
        with ThreadPoolExecutor(max_workers=2) as pool:
            futures = [pool.submit(contender) for _ in range(2)]
            results = [f.result(timeout=10) for f in futures]
        assert results[0] == results[1] and count_jobs(path) == 5
        print('PASS two concurrent connections: one effect, same result')
    print('8 checks passed')


if __name__ == '__main__':
    main()

2026-09-12 的实际输出：

Python 3.13.13 / SQLite 3.51.2
PASS first call: one job
PASS retry through new connection: same receipt, still one job
PASS same key with different arguments: rejected
PASS failure before receipt: effect rolled back
PASS retry after rollback: one new job
PASS lost acknowledgement: committed result replayed
PASS separate tenant scope: independent operation
PASS two concurrent connections: one effect, same result
8 checks passed