假设任务入库后,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 版本号,未必换了它背后的库。

图中两栏分别看返回表达式的值和显式提交后的可见性。本例的读连接每次查询都读完并关闭游标,没有保留上一轮读事务。本文使用 AI 辅助写作及配图。
把下面的程序存成 .py 文件运行。脚本需要 Python 3.12+,因为它显式设置了连接的 autocommit 参数(资料3)。这里让 SQLite 接收 SQL 写法的 BEGIN、COMMIT 和 ROLLBACK,同时用 closing 保证连接及关键游标会关闭。它会创建临时数据库,演示结束即清理。
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 删除。