SQLite 多进程写入：WAL 与 busy_timeout 实战

问题场景

多个进程或线程写同一个 SQLite 数据库文件时，常见报错 sqlite3.OperationalError: database is locked。SQLite 默认使用 rollback journal，写操作需要独占锁；当另一个连接正在写时，后来的写请求会失败或等待。Python 的 sqlite3 会在超时后抛异常。即使设置了 timeout，如果事务较长或多个写者同时竞争，仍可能遇到锁冲突。WAL 模式允许多个读者和一个写者并发，能缓解读写互斥；busy_timeout 让连接在锁冲突时等待而不是立刻失败；应用层再配合短事务和重试，可以处理偶发写写冲突。

最小可运行示例

以下脚本演示连接初始化、WAL 设置、BEGIN IMMEDIATE 写入与指数退避重试。数据库路径 app.db 是占位值，可替换。

# sqlite_wal_demo.py
import sqlite3
import time
import random
import multiprocessing as mp

DB_PATH = 'app.db'  # 占位：实际项目替换为你的数据库文件路径
BUSY_TIMEOUT_MS = 5000  # 忙等待超时，单位毫秒

def connect(db_path=DB_PATH):
    conn = sqlite3.connect(
        db_path,
        timeout=BUSY_TIMEOUT_MS / 1000.0,
        isolation_level=None,  # 关闭隐式事务，由我们显式 BEGIN
    )
    # WAL 是数据库级别的持久设置；第一次设置后会一直保留
    conn.execute('PRAGMA journal_mode=WAL;')
    # busy_timeout 是连接级别，建议每个连接都设置
    conn.execute(f'PRAGMA busy_timeout={int(BUSY_TIMEOUT_MS)};')
    # WAL 下通常把 synchronous 设为 NORMAL，兼顾安全与写入开销
    conn.execute('PRAGMA synchronous=NORMAL;')
    conn.execute('PRAGMA foreign_keys=ON;')
    return conn

def init_db(conn):
    conn.execute(
        '''
        CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
        '''
    )

def is_locked_error(exc):
    msg = str(exc).lower()
    return 'locked' in msg or 'busy' in msg

def write_event(name, max_retries=8, base_delay=0.05):
    # 写入一条记录；遇到锁冲突时重试整个事务。
    last_error = None
    for attempt in range(max_retries):
        conn = None
        try:
            conn = connect()
            init_db(conn)
            # BEGIN IMMEDIATE 立即申请写锁，避免提交时才暴露冲突
            conn.execute('BEGIN IMMEDIATE;')
            conn.execute('INSERT INTO events(name) VALUES (?);', (name,))
            conn.execute('COMMIT;')
            return True
        except sqlite3.OperationalError as exc:
            if conn is not None:
                try:
                    conn.execute('ROLLBACK;')
                except sqlite3.Error:
                    pass
            if not is_locked_error(exc):
                raise
            last_error = exc
            # 指数退避 + 抖动，避免多个进程同时重试再次撞锁
            delay = base_delay * (2 ** attempt) + random.uniform(0, 0.02)
            time.sleep(delay)
        finally:
            if conn is not None:
                conn.close()
    raise last_error

def worker(worker_id, count=50):
    for i in range(count):
        write_event(f'worker-{worker_id}-{i}')

if __name__ == '__main__':
    procs = [mp.Process(target=worker, args=(i,)) for i in range(4)]
    for p in procs:
        p.start()
    for p in procs:
        p.join()
    print('done')

运行方法

把上述代码保存为 sqlite_wal_demo.py，在 shell 中执行：

python sqlite_wal_demo.py

脚本会在当前目录创建 app.db、app.db-wal 和 app.db-shm 三个文件。若输出 done，说明写入完成。可以用 sqlite3 app.db 'select count(*) from events;' 查看记录数。如果本机未安装 sqlite3 命令行工具，也可用 Python 查询。

如果要观察锁冲突与重试，可以把 count 调大或启动更多进程。注意不要在生产环境用大量进程同时写同一个 SQLite 文件，这只能作为机制演示。

代码要点

• WAL 模式：PRAGMA journal_mode=WAL; 让读和写可以并发。一个连接设置成功后，数据库文件会保持 WAL 模式。WAL 文件是数据库的一部分，移动数据库时要一并处理 -wal 和 -shm 文件。
• busy_timeout：连接在遇到锁时最多等待指定毫秒。Python 的 sqlite3.connect(timeout=...) 与 PRAGMA busy_timeout 都用于设置忙等待超时，示例中显式写 PRAGMA，便于阅读和调整。
• BEGIN IMMEDIATE：在事务开始时申请写锁，比默认的延迟写锁更早暴露冲突，避免“读到一半才发现无法提交”的复杂情况。
• 重试整个事务：锁冲突后不要只重试最后一条 SQL，而应回滚并重新执行整个事务。退避加随机抖动，减少多进程同步重试。
• 短事务：写事务越长，持锁时间越久，其他写者等待越久。尽量先准备好数据，再快速 INSERT/UPDATE 并提交。

边界与限制

1. WAL 并不消除写写冲突。WAL 允许“多读一写”，但同一时刻仍只有一个写者。多个进程同时写时，busy_timeout 和重试只是缓解，不能完全避免失败。
2. busy_timeout 是等待上限。超过后仍会抛出 database is locked。设置过大可能造成请求堆积，设置过小会增加重试次数。需要根据事务长度和并发量调整。
3. 重试不是无限循环。示例设置 max_retries，最终仍会把异常抛给上层，避免永久阻塞。
4. WAL 依赖共享内存。数据库文件应放在支持共享内存的本地文件系统上；部分网络文件系统（如 NFS）对 SQLite WAL 的支持并不可靠，应谨慎使用。
5. 长事务、忘记提交/回滚、异常退出都可能长期持锁。使用 try/finally 关闭连接，并确保事务有明确的 COMMIT 或 ROLLBACK。
6. 在线备份要使用 SQLite 的备份 API，或先执行 PRAGMA wal_checkpoint(TRUNCATE);，不要简单复制主文件而忽略 WAL 文件。
7. 如果写入吞吐要求高，SQLite 不是多写者数据库。更合适的是单写者队列（一个进程负责写，其他进程通过 IPC 发送写请求），或改用客户端/服务器数据库。

小结

处理 SQLite 的 database is locked，可以用 WAL 改善读写并发，用 busy_timeout 让连接等待锁，再在应用层用 BEGIN IMMEDIATE、短事务和带抖动的重试兜底。示例代码可作为最小验证起点，但正式项目需要根据事务长度、并发量和部署文件系统调整超时与重试策略。