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.4s | 7.1s ~ 21.0s | 0 | 4.2 MB |
| WAL,busy_timeout=0 | 失败 | — | database is locked | — |
| WAL,busy_timeout=10000 | 约 12.6s | 11.8s ~ 12.5s | 0 | 4.6 MB + WAL 3.1 MB |
| WAL + 关闭 autocheckpoint | 约 9.8s | 9.5s ~ 9.8s | 0 | 4.2 MB + WAL 41 MB |
几个观察:
- 默认模式下三个进程并没有互相等着报错,是因为
timeout参数让 SQLite 内部做了重试,实际是串行化的,所以单进程耗时在 7s 到 21s 之间波动——谁先拿到锁谁快。 - WAL 下三进程耗时接近,说明写确实被串行化了,但排队等待是干净的,没有失败。
- 关掉自动 checkpoint 是最快的,但 WAL 文件涨到 41MB,这是拿磁盘换时间,长期跑不可取。
四、结论与配置建议
多进程写入场景下的推荐配置:
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;
同时注意:
busy_timeout必须设,否则高并发下会直接抛database is locked而不是等待。- 写事务尽量合并,一千条一批比一条一次快一个数量级。批量插入时用
BEGIN ... COMMIT包住,别依赖自动提交。 - WAL 文件不会自己缩小,只在 checkpoint 时被截断。长期运行要监控
-wal文件大小,必要时手动PRAGMA wal_checkpoint(TRUNCATE)。 - 数据库目录要放本地盘。放在 NFS 上会直接无法进入 WAL 模式,报
database is locked或disk I/O error。 - 备份不能只拷
.db文件,WAL 里可能还有未 checkpoint 的数据。用VACUUM INTO 'backup.db'或.backup命令。
五、什么时候该换掉 SQLite
如果写入频率上到每秒几千次、或者需要多个应用服务器同时连一个库,那就该上 PostgreSQL。SQLite 的定位是「单机、嵌入式、读多写少」,WAL 让它在这个定位内更从容,但没有改变它的定位。三万条数据的测试能说明并发排队行为,不能说明它能扛住高写入负载。