SQLite 并发写入实战:WAL 模式、busy_timeout 与单写者队列
SQLite 并发写入实战:WAL 模式、busy_timeout 与单写者队列
前段时间给一个采集服务换存储,从「每个进程一个 JSON 文件」改成 SQLite。单进程时一切正常,一上多进程就开始零星报 database is locked,日志里平均每小时几条,重试一下就过去,但偶尔会连续失败把整批数据丢掉。这篇文章记录排查和最终方案,都是可以直接抄的配置。
一、先理解 SQLite 的锁模型
SQLite 不是「一个连接一把大锁」这么粗暴,它在文件层面用三种锁:
- SHARED:读事务持有,允许多个连接同时持有。
- RESERVED:写事务开始写入时持有,同时仍允许别的连接读。
- EXCLUSIVE:真正写盘时持有,此时其他连接的读写都会被拒。
在默认的 journal_mode=DELETE 下,写事务提交那一刻需要拿到 EXCLUSIVE,其他连接只要有一个在读(哪怕读得慢),写就等不了,直接返回 SQLITE_BUSY。这就是多进程下高频报错的根源。
二、WAL 是第一步,但只解决一半
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
WAL(Write-Ahead Logging)把「写主库」改成「写日志文件」,读操作走主库快照,读写互不阻塞。设置一次即持久化,之后所有连接生效。
再配 synchronous=NORMAL,把每次事务的 fsync 从「提交时」改成「检查点(checkpoint)时」,写入吞吐通常能翻几倍。代价是断电可能丢最后几个事务,但对采集、日志、缓存这类数据完全可以接受;涉及金额的账本不要这么改。
WAL 带来的新问题:同一时刻仍然只允许一个写者。多个进程同时想写,后面的还是会拿到 BUSY,只是不再被读者影响了。
三、busy_timeout:把「立刻失败」改成「排队等一下」
PRAGMA busy_timeout = 5000;
这一行最容易被忽略,收益却最大。默认值是 0,意味着拿不到锁立刻报错;设成 5000 后,SQLite 内部会以退避方式重试 5 秒,绝大多数毫秒级的写冲突就此消失。
在 Python 里更推荐用连接参数统一设置,避免每个连接忘记执行 PRAGMA:
import sqlite3
def connect(path: str) -> sqlite3.Connection:
conn = sqlite3.connect(path, timeout=5.0) # 等价于 busy_timeout=5000
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn
注意 timeout 参数只对「等锁」有效,它不是查询超时,别指望用它兜住慢 SQL。
四、真正的坑:长事务把写锁攥住不放
配置完 WAL 和 busy_timeout 之后报错从每小时几条降到几天一条,但没归零。抓包看调用栈,发现有一处代码是这样的:
cur = conn.cursor()
for row in rows:
cur.execute("INSERT INTO events(...) VALUES(?,?,?)", row)
if row[2] == "error":
send_alert(row) # 网络请求,最慢 30 秒
conn.commit() # 30 秒后才提交
一个隐式事务把几百条插入和一次网络请求包在一起,写锁被持有了整整半分钟,别的进程只能撞上 5 秒超时。
两条修正:
- 批量插入用
executemany+ 一次提交,把事务边界压到毫秒级:
conn.executemany("INSERT INTO events(a,b,c) VALUES(?,?,?)", rows)
conn.commit()
- 副作用移出事务:先把要告警的数据收集到列表,提交之后再发请求。数据库事务里永远不要做网络 IO。
五、多进程场景:加一层单写者队列
如果写压力再大一些(比如同时有 10 个 worker),即使事务很短,「抢锁-退避-重试」本身也是浪费。更稳的做法是让所有写请求进一个队列,由一个专门的写线程串行落库:
import queue, threading
_write_q: "queue.Queue" = queue.Queue(maxsize=10000)
def writer_loop(db_path: str) -> None:
conn = connect(db_path)
while True:
batch = [_write_q.get()]
# 顺手攒批:把队列里已有的都取出来
while len(batch) < 500:
try:
batch.append(_write_q.get_nowait())
except queue.Empty:
break
try:
conn.executemany("INSERT INTO events(a,b,c) VALUES(?,?,?)", batch)
conn.commit()
except Exception:
conn.rollback()
# 落盘到失败文件,别静默吞掉
with open("failed_batch.jsonl", "a", encoding="utf-8") as f:
for item in batch:
f.write(__import__("json").dumps(item, ensure_ascii=False) + "\n")
finally:
for _ in batch:
_write_q.task_done()
threading.Thread(target=writer_loop, args=("data.db",), daemon=True).start()
这样写者永远只有一个,锁竞争彻底消失,同时因为攒批,吞吐反而比多进程各自写更高。读仍然可以多进程并发,WAL 保证了读写不互斥。
队列设 maxsize 很关键:满了就让生产者阻塞(或丢弃并计数),避免内存被无限堆积撑爆。
六、还有几个容易踩的细节
检查点不要忘。 WAL 文件会持续增长,默认在 1000 页时自动 checkpoint。批量导入大量数据时建议手动触发:
PRAGMA wal_checkpoint(TRUNCATE);
它会把 WAL 内容合并回主库并把日志文件截断为 0。
不要用网络盘。 SQLite 的锁依赖文件系统的 POSIX 锁语义,NFS/SMB 上行为不可靠,容易出现「锁住了但对方不知道」的诡异问题。放本地盘,或者干脆换 PostgreSQL。
PRAGMA foreign_keys 每个连接都要开。 它是连接级开关,不是库级,新开的连接默认是关闭的——这点和 WAL 不同,很容易漏。
事务用 BEGIN IMMEDIATE。 默认的 BEGIN DEFERRED 会先拿读锁、写的时候才升级成写锁,升级失败会直接 BUSY 而不会走 busy_timeout 的重试。要写就一开始声明:
conn.execute("BEGIN IMMEDIATE")
七、效果对照
同一台机器、8 个采集进程、每小时约 12 万条写入:
| 配置 | 每小时 BUSY 报错 | 平均写入耗时 |
|---|---|---|
| 默认 DELETE 模式 | 数十条 | 约 40 ms |
| + WAL + NORMAL | 几条 | 约 9 ms |
| + busy_timeout=5s | 偶发(长事务导致) | 约 9 ms |
| + 短事务 + 单写者队列 | 0 | 约 3 ms |
改完之后跑了两周,database is locked 一条都没有。
小结
SQLite 完全能撑住多进程并发写,前提是四件事一起做:开 WAL、设 busy_timeout、把事务压短(副作用移出事务)、必要时用单写者队列串行化写入。单看任何一条都只能解决一部分问题,这也是它最容易被误判成「不适合并发」的原因。
- 点赞
- 收藏
- 关注作者
评论(0)