SQLite 并发写入实战:WAL 模式、busy_timeout 与单写者队列

举报
yd_257742204 发表于 2026/11/10 06:19:47 2026/11/10
【摘要】 多进程写 SQLite 频繁报 database is locked,只开 WAL 并不能根治。本文按锁模型拆解,给出 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 秒超时。

两条修正:

  1. 批量插入用 executemany + 一次提交,把事务边界压到毫秒级:
conn.executemany("INSERT INTO events(a,b,c) VALUES(?,?,?)", rows)
conn.commit()
  1. 副作用移出事务:先把要告警的数据收集到列表,提交之后再发请求。数据库事务里永远不要做网络 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、把事务压短(副作用移出事务)、必要时用单写者队列串行化写入。单看任何一条都只能解决一部分问题,这也是它最容易被误判成「不适合并发」的原因。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。