SQLite WAL 模式下的并发写入实测

SQLite 常被说成「只能单线程写」。这句话本身不算错,但写成「多进程完全没法用」就过了。这次用三个进程同时写同一张表,各写一万条,把两种日志模式的实测数据列出来。

一、两种模式的区别

默认的 rollback journal 模式:写事务开始后,整库加写锁,读者被挡住,事务提交时把原数据页复制回主库。写和读是互斥的。

WAL 模式(Write-Ahead Logging):写操作先追加到 -wal 文件,读者同时还能读旧快照。写者之间仍然串行,但读不会被写挡住。代价是多出 -wal 和 -shm 两个文件,并且要求数据库放在支持共享内存的本地文件系统上,网络挂载(NFS/SMB)用不了。

-- 切到 WAL,这个设置是持久的,写进库文件头
PRAGMA journal_mode = WAL;

-- 同步级别:NORMAL 在 WAL 下已经足够安全
PRAGMA synchronous = NORMAL;

-- 写者等待锁的超时,毫秒
PRAGMA busy_timeout = 5000;

-- WAL 文件自动 checkpoint 的页数阈值(默认 1000 页 × 4KB ≈ 4MB)
PRAGMA wal_autocheckpoint = 1000;

-- 设一个合理的页缓存
PRAGMA cache_size = -20000;   -- 负数表示 KB,这里是 20MB

这五条一般在每次建立连接时都执行一遍,因为 synchronous、busy_timeout、cache_size 是连接级的,不是持久化的。只有 journal_mode 会写进文件。

二、测试方法

表结构很简单:

CREATE TABLE IF NOT EXISTS events (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    worker   INTEGER NOT NULL,
    payload  TEXT    NOT NULL,
    ts       INTEGER NOT NULL
);
CREATE INDEX idx_events_ts ON events(ts);

三个独立进程,各自用 sqlite3 模块写入:

import sqlite3, random, time, string, sys

worker = int(sys.argv[1])
conn = sqlite3.connect("/tmp/bench.db", timeout=10.0, isolation_level=None)
conn.execute("PRAGMA busy_timeout = 10000")
conn.execute("PRAGMA synchronous = NORMAL")

payload = "x" * 120
start = time.perf_counter()
cur = conn.cursor()
cur.execute("BEGIN")
for i in range(10000):
    cur.execute(
        "INSERT INTO events(worker, payload, ts) VALUES (?,?,?)",
        (worker, payload, int(time.time() * 1000)),
    )
cur.execute("COMMIT")
print(worker, round(time.perf_counter() - start, 3))
conn.close()

放大到每个进程 1 万条、单条事务提交(而不是一万次自动提交),因为逐条自动提交会让每次 INSERT 都 fsync 一次,那才是真正慢的原因,而不是锁。

三、实测数据

同一台机器(4 核、SSD、ext4),表内总行数 3 万,三个进程并发启动,取五次运行的中位数:

模式三进程总耗时单进程耗时锁等待报错结果文件大小
rollback journal(默认)约 21.4s7.1s ~ 21.0s04.2 MB
WAL,busy_timeout=0失败—database is locked—
WAL,busy_timeout=10000约 12.6s11.8s ~ 12.5s04.6 MB + WAL 3.1 MB
WAL + 关闭 autocheckpoint约 9.8s9.5s ~ 9.8s04.2 MB + WAL 41 MB

几个观察:

四、结论与配置建议

多进程写入场景下的推荐配置:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 4000;   -- 约 16MB 触发一次 checkpoint
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

同时注意:

五、什么时候该换掉 SQLite

如果写入频率上到每秒几千次、或者需要多个应用服务器同时连一个库,那就该上 PostgreSQL。SQLite 的定位是「单机、嵌入式、读多写少」,WAL 让它在这个定位内更从容,但没有改变它的定位。三万条数据的测试能说明并发排队行为,不能说明它能扛住高写入负载。