SQLite 启用 WAL 模式 + 调几个 pragma 让并发写不再串行

SQLite 默认 journal_mode 是 delete，写操作要拿整库锁，多写者
直接互相 block。改 WAL（Write-Ahead Logging）后：

读不阻塞写
写不阻塞读
多个读者并发
单写者（这个仍是 SQLite 的硬限制）

对中小型应用（< 10k QPS）足够把它当生产数据库用。

启用 WAL

import sqlite3
conn = sqlite3.connect('app.db')
conn.execute('PRAGMA journal_mode = WAL;')
conn.execute('PRAGMA synchronous = NORMAL;')
conn.execute('PRAGMA busy_timeout = 5000;')
conn.execute('PRAGMA cache_size = -64000;')  # 64MB cache
conn.execute('PRAGMA foreign_keys = ON;')
conn.execute('PRAGMA temp_store = MEMORY;')
conn.execute('PRAGMA mmap_size = 134217728;')  # 128 MB mmap

各 pragma 含义：

pragma	作用	建议值
`journal_mode = WAL`	改为 Write-Ahead Logging	WAL
`synchronous = NORMAL`	WAL 同步级（默认 FULL 更安全但慢）	NORMAL
`busy_timeout`	等锁的毫秒数（超时返回 SQLITE_BUSY）	5000
`cache_size`	负数表示 KB，正数表示页数；建议负数	-64000 (64MB)
`foreign_keys`	默认关闭 —— 必须显式开	ON
`temp_store`	临时表放内存还是磁盘	MEMORY
`mmap_size`	用 mmap 而不是 read() 读	128MB

每次新连接都要重设这些 pragma（除了 journal_mode 是持久化的）。

WAL 后会出现的文件

app.db
app.db-wal       # WAL log
app.db-shm       # 共享内存映射

备份这 3 个文件都要在一致快照里，最好用 VACUUM INTO 或 SQLite Online Backup API。
不要简单 cp app.db backup.db，可能拿到不一致状态。

# 一致备份：
src = sqlite3.connect('app.db')
dst = sqlite3.connect('backup.db')
with dst:
    src.backup(dst)

checkpoint：把 WAL 合并回主库

WAL 不断追加，会越来越大。SQLite 默认 1000 页时自动 checkpoint，
但繁忙系统可能赶不上。手动控制：

conn.execute('PRAGMA wal_autocheckpoint = 1000;')   # 默认值
conn.execute('PRAGMA wal_checkpoint(PASSIVE);')      # 立刻做一次（不阻塞读写）
conn.execute('PRAGMA wal_checkpoint(TRUNCATE);')     # 完全清空 WAL（要短暂独占）

低峰期跑一次 TRUNCATE 比较好。

真实并发测试

# 5 个线程并发读 + 1 个线程写
import threading, sqlite3, time

DB = 'test.db'
def setup():
    c = sqlite3.connect(DB)
    c.execute('PRAGMA journal_mode = WAL')
    c.execute('CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, v TEXT)')
    for i in range(10000):
        c.execute('INSERT INTO t (v) VALUES (?)', (f'val{i}',))
    c.commit(); c.close()

setup()

def reader():
    c = sqlite3.connect(DB)
    c.execute('PRAGMA busy_timeout = 5000')
    for _ in range(1000):
        c.execute('SELECT count(*) FROM t').fetchone()
    c.close()

def writer():
    c = sqlite3.connect(DB)
    c.execute('PRAGMA busy_timeout = 5000')
    for i in range(1000):
        c.execute('INSERT INTO t (v) VALUES (?)', (f'w{i}',))
        c.commit()
    c.close()

threads = [threading.Thread(target=reader) for _ in range(5)] + [threading.Thread(target=writer)]
t0 = time.time()
for t in threads: t.start()
for t in threads: t.join()
print(f'done in {time.time()-t0:.2f}s')

WAL 模式比 delete 模式通常快 3-10 倍（取决于 IO）。

SQLITE_BUSY 的根因 + 处理

WAL 模式下还可能 BUSY：

两个写者同时持有写锁：第二个收到 BUSY。busy_timeout
让 SQLite 自动重试 5 秒
DDL 时被读者卡住：CREATE / ALTER 需要独占，正在 SELECT
的连接会阻塞 DDL
WAL checkpoint(TRUNCATE) 时被读者卡住：同上

应用层正确做法：捕获 sqlite3.OperationalError 重试，或者用
busy_timeout 让驱动层重试。

Django 配置

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
        'OPTIONS': {
            'init_command': 'PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;',
            'timeout': 20,   # busy_timeout (秒)
        },
    }
}

或用一个 connection signal 设更多 pragma：

from django.db.backends.signals import connection_created

def _set_pragmas(sender, connection, **kwargs):
    if connection.vendor == 'sqlite':
        with connection.cursor() as cur:
            cur.execute('PRAGMA journal_mode=WAL;')
            cur.execute('PRAGMA synchronous=NORMAL;')
            cur.execute('PRAGMA temp_store=MEMORY;')
            cur.execute('PRAGMA cache_size=-64000;')
            cur.execute('PRAGMA mmap_size=134217728;')

connection_created.connect(_set_pragmas)

什么时候放弃 SQLite

写 QPS > 1000：单写者是瓶颈
多机器需要共享 DB：SQLite 是单机的
字段需要 jsonb 索引等高级 PG 特性
复杂查询性能：SQLite 优化器较 PG 简单

否则 SQLite 比 PG/MySQL 部署简单一个数量级，单文件备份，绝对值得。

踩过的坑

WAL 文件不被压缩；WAL 不 checkpoint 时膨胀到几 GB 还在变大。监控
app.db-wal 大小，超过 100MB 跑一次 PRAGMA wal_checkpoint(TRUNCATE)。
NFS / SMB 上跑 SQLite 绝对不行——锁机制基于 fcntl，网络文件系统的
fcntl 不可靠，会有静默数据损坏。
PRAGMA foreign_keys = ON 每个连接都要单独设。Django 默认设了；
自己 sqlite3.connect() 时容易漏。
多进程（如 gunicorn 多 worker）共享 SQLite 没问题，但每个 worker
自己的连接都要设 pragma。