插入操作执行期间与完成后查询结果不一致的问题解决

我的数据库在特定时段每秒都会写入数据，同时该时段内每分钟会进行一次读取操作。业务时段与非业务时段的查询输出结果存在差异，推测可能是读取时数据尚未完成写入。如何解决该问题，确保读取前最近一分钟的数据已完整写入？更换数据库是否能改善情况？

读取代码 #

conn = sqlite3.connect(f'{loc_tick}/tick.db',
detect_types=sqlite3.PARSE_DECLTYPES, timeout=20, isolation_level=None)
select_statement = f"select * from symfut WHERE timestamp >= date('now', '-10 days')"
m1df = pd.read_sql(select_statement, conn)

写入代码 #

conn = sqlite3.connect('tick.db', 
detect_types=sqlite3.PARSE_DECLTYPES, timeout=20,isolation_level=None)
c = conn.cursor()

c.execute('PRAGMA journal_mode=wal')
c.execute('PRAGMA wal_autocheckpoint = 100')
c.execute('INSERT INTO symfut (timestamp, c, b, a) VALUES (?,?,?,?)',
                  (timestamp, c, b, a))

1. 优化SQLite读写一致性 #

你的代码里设置了isolation_level=None，让SQLite进入自动提交模式，但高并发场景下，读取可能刚好卡在写入提交的间隙。可以通过以下方式调整：

• 显式提交写入事务：移除isolation_level=None，每次写入后调用conn.commit()确保数据落盘，避免未提交数据残留。修改后的写入代码：

conn = sqlite3.connect('tick.db', 
detect_types=sqlite3.PARSE_DECLTYPES, timeout=20)  # 移除自动提交配置
c = conn.cursor()

c.execute('PRAGMA journal_mode=wal')
c.execute('PRAGMA wal_autocheckpoint = 100')
c.execute('INSERT INTO symfut (timestamp, c, b, a) VALUES (?,?,?,?)',
                  (timestamp, c, b, a))
conn.commit()  # 显式提交，确保数据写入主库
conn.close()   # 及时关闭连接，避免资源泄漏

• 读取前强制同步WAL：在执行查询前，调用PRAGMA wal_checkpoint(FULL)，强制SQLite把WAL日志里的未提交数据写入主数据库，确保读取到完整的历史数据。修改后的读取代码：

conn = sqlite3.connect(f'{loc_tick}/tick.db',
detect_types=sqlite3.PARSE_DECLTYPES, timeout=20)
c = conn.cursor()
c.execute('PRAGMA wal_checkpoint(FULL)')  # 强制同步WAL到主库
select_statement = "select * from symfut WHERE timestamp >= date('now', '-10 days')"
m1df = pd.read_sql(select_statement, conn)
conn.close()

• 缩小读取时间范围：因为读取是每分钟执行一次，可以把查询的时间上限设为「当前时间减1分钟」，直接避开正在写入的实时数据，确保读取的都是已经稳定写入的历史数据：

select_statement = "select * from symfut WHERE timestamp >= date('now', '-10 days') AND timestamp <= date('now', '-1 minute')"

2. 更换数据库的效果 #

如果换成PostgreSQL、MySQL这类客户端-服务器架构的数据库，它们天生支持MVCC多版本并发控制，能在高并发读写场景下自动保证数据一致性，不需要手动处理WAL同步这类底层细节。但这类数据库需要额外部署和维护服务，不像SQLite是轻量的文件型数据库。

如果你的业务并发量持续增长，或者对数据一致性要求极高，更换数据库能从根本上解决问题；如果只是当前的小并发场景，优化SQLite的配置和代码就足够满足需求。

内容的提问来源于stack exchange，提问作者Sid