首页
学习
活动
专区
圈层
工具
发布
社区首页 >专栏 >SQLite RETURNING 已返回,为什么另一个连接查不到这条记录?

SQLite RETURNING 已返回,为什么另一个连接查不到这条记录?

原创
作者头像
逻界软件科技工作室
发布于 2026-11-22 15:44:39
发布于 2026-11-22 15:44:39
140
举报

假设任务入库后,Python 接口用 INSERT ... RETURNING 取到任务编号就通知另一个进程去取。对方用这个编号查询,却拿到空结果。更绕的是,同一个写连接再查一次,状态的大小写和空格居然也跟刚拿到的返回值对不上。只需要一张普通 SQLite 表、一个 AFTER INSERT 触发器和两个连接,就能把这两个现象分开复现。

返回行来自这条写语句

看代码之前,先定一个具体场景。写入时传入带空格的大写 ' READY ',表上的 AFTER INSERT 触发器随后把它变成 'ready'。RETURNING id, status 能拿到分配或插入的编号,不过本例里的状态仍是 ' READY '。紧接着在写连接里用 SELECT 查同一行,状态已经是 'ready'。

SQLite 文档把 RETURNING 的值限定在顶层写语句看到的值。触发器和外键动作引起的额外变化不会作为额外返回行,AFTER 触发器后续改过的字段也不会反馈给这条语句(资料1)。接口需要把归一化后的状态回给调用方,就在写连接上把写语句做完,再按主键查询。示例查了整张小表,实际查一条任务用 WHERE id = ? 即可。

SQLite 从 3.35.0 开始支持 RETURNING(资料4)。Python 侧看 sqlite3.sqlite_version,它显示当前解释器实际加载的 SQLite 库版本(资料3)。同一台机器上,Python 3.14.3 加载的是 SQLite 3.26.0,下面的脚本会明确停止。完成实验的是 Python 3.13.14,加载 SQLite 3.53.1。换个 Python 版本号,未必换了它背后的库。

SQLite RETURNING 的返回值与显式提交后的查询
SQLite RETURNING 的返回值与显式提交后的查询

图中两栏分别看返回表达式的值和显式提交后的可见性。本例的读连接每次查询都读完并关闭游标,没有保留上一轮读事务。本文使用 AI 辅助写作及配图。

一个连接看到了,另一个连接还在等提交

把下面的程序存成 .py 文件运行。脚本需要 Python 3.12+,因为它显式设置了连接的 autocommit 参数(资料3)。这里让 SQLite 接收 SQL 写法的 BEGIN、COMMIT 和 ROLLBACK,同时用 closing 保证连接及关键游标会关闭。它会创建临时数据库,演示结束即清理。

代码语言:python
复制
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory


def 检查(名称, 实际, 预期):
    if 实际 != 预期:
        raise RuntimeError(f'{名称}: 得到 {实际!r},预期 {预期!r}')
    print('PASS', 名称)


if sqlite3.sqlite_version_info < (3, 35, 0):
    raise SystemExit(f'需要 SQLite 3.35.0+,当前为 {sqlite3.sqlite_version}')

with TemporaryDirectory() as 目录:
    文件 = Path(目录) / 'queue.db'
    with closing(sqlite3.connect(文件, autocommit=True)) as 写入, \
         closing(sqlite3.connect(文件, autocommit=True)) as 观察:
        写入.execute('PRAGMA journal_mode=DELETE').fetchall()
        写入.execute('CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)')
        写入.execute('''
            CREATE TRIGGER normalize AFTER INSERT ON jobs
            BEGIN
                UPDATE jobs SET status = lower(trim(NEW.status)) WHERE id = NEW.id;
            END
        ''')
        插入 = 'INSERT INTO jobs(id, status) VALUES (?, ?) RETURNING id, status'

        def 查询(连接):
            with closing(连接.execute('SELECT id, status FROM jobs ORDER BY id')) as 游标:
                return 游标.fetchall()

        写入.execute('BEGIN')
        with closing(写入.execute(插入, (1, ' READY '))) as 游标:
            检查('RETURNING 初值', 游标.fetchall(), [(1, ' READY ')])
        检查('同连接看到触发器结果', 查询(写入), [(1, 'ready')])
        检查('提交前第二连接无记录', 查询(观察), [])
        写入.execute('ROLLBACK')
        检查('回滚后无记录', 查询(写入), [])

        写入.execute('BEGIN')
        with closing(写入.execute(插入, (2, ' READY '))) as 游标:
            检查('第二次返回初值', 游标.fetchall(), [(2, ' READY ')])
        写入.execute('COMMIT')
        检查('提交后第二连接读到最终值', 查询(观察), [(2, 'ready')])

        写入.execute('BEGIN')
        多行插入 = 'INSERT INTO jobs(id, status) VALUES (?, ?), (?, ?) RETURNING id'
        with closing(写入.execute(多行插入, (3, 'READY', 4, 'READY'))) as 游标:
            首行 = 游标.fetchone()
            try:
                写入.execute('COMMIT')
            except sqlite3.OperationalError as 异常:
                检查('语句未结束时提交遇到 SQLITE_BUSY', 异常.sqlite_errorcode, sqlite3.SQLITE_BUSY)
            else:
                raise RuntimeError('仍有返回行时提交竟然成功')
            检查('读完两条返回行', sorted([首行, *游标.fetchall()]), [(3,), (4,)])
        写入.execute('COMMIT')
        检查('第二连接读到三条已提交记录', 查询(观察), [(2, 'ready'), (3, 'ready'), (4, 'ready')])

第一轮 INSERT 后,写连接查到 (1, 'ready'),观察连接查到 []。代码随后主动回滚。虽然程序已经拿到了 id=1,库里仍然没有这条记录。第二轮把同样的状态写到 id=2,这次执行 COMMIT,观察连接用一条新查询读到 (2, 'ready')。程序可以用返回值继续处理当前事务。等提交成功,再通知使用独立连接的消费者。

fetchone() 只拿一行,写语句可能还没结束

最后一轮故意用一条 INSERT 写两行,先拿一条 RETURNING 结果。此时还有返回行没取,当前连接发 COMMIT 得到 SQLITE_BUSY。取完剩余结果并关闭游标后,同一事务可以提交,第二连接随后读到了预期记录。例子先把返回编号排序再逐项比对,SQLite 并不约定 RETURNING 的行顺序(资料1)。

事务文档也列了其他 SQLITE_BUSY 情形,例如另一连接的读事务挡住提交(资料2)。所以遇到 BUSY,先在本连接查有没有还没结束的写语句。如果确实还有一条等着应用消费,单纯把等待时间调大,代码也不会自己走到 fetchall()。生产路径只需要一个编号时,可以先问这条 SQL 是否真的只改一条记录。批量场景需要控制规模和列数,SQLite 会先把 RETURNING 的输出积在内存里(资料1)。

放在接口里,哪一步算保存完成

如果调用方要拿最终状态,建议把查询放在写入事务里,并按主键回读。应用确认提交成功以后,再返回保存成功或通知消费者。跨连接验收时用新的读事务。如果观察方一直开着旧读事务,读到的仍可能是那个事务开始后的历史快照,等旧事务结束再开始一次查询(资料2)。这样可以避免把读连接的旧视图误判为写入丢失。

最后别把一次 SQLite 提交当成消息一定送达。数据库写入和应用向外发送消息是两次操作,本例只实际验证了本地两连接的读写。不论采用后置发送还是同库写入待发送记录,调用方都需要对消息发送单独留状态。排查时分别记下返回行、写连接回读值、提交结果和读端事务的开始位置,看到空表时便知道该找哪一步。

参考资料

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

目录
  • 返回行来自这条写语句
  • 一个连接看到了,另一个连接还在等提交
  • fetchone() 只拿一行,写语句可能还没结束
  • 放在接口里,哪一步算保存完成
  • 参考资料
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档