SQLite 并发踩坑与修复实录

为什么 Web 后端必须开启 WAL？

一、SQLite：被误解的轻量级数据库

1.1 什么是 SQLite

SQLite 是一个自包含、无服务器、零配置、事务性的 SQL 数据库引擎。与 MySQL、PostgreSQL 等传统数据库不同，它不是一个独立的进程，而是直接嵌入到应用程序中的库文件。整个数据库就是一个普通的磁盘文件，读写操作就是对文件的读写。

它的设计哲学可以概括为：简单、可靠、随处可用。

1.2 发展史

1.3 性能表现：到底快不快？

这个问题需要分场景回答：

真实数据参考： 在标准测试环境（M1 Pro / NVMe SSD / 单行简单查询 / WAL + NORMAL 模式）下，SQLite 每秒可完成约 10 万次 SELECT 或 5 万次 INSERT。此数据仅供参考，实际性能受查询复杂度、磁盘 IO 和并发度影响。

1.4 一个关键疑问：为什么 SQLite 默认不是 WAL 模式？

很多人（包括我）第一次踩到这个坑时都会问：WAL 模式这么好，为什么 SQLite 不默认开启？

SQLite 在 3.7.0（2010 年） 引入 WAL 模式，至今十几年了，默认依然是 DELETE journal 模式。这不是疏忽，是深思熟虑的设计决策。

原因一："最广泛场景"的理解差异

SQLite 部署量超过 1 万亿个数据库文件。我们来看看这些文件分布在哪里：

SQLite 开发团队的选择逻辑是：

默认值服务于最广泛的场景，而非最优场景。 对于 99% 的 SQLite 使用者（移动端、嵌入式、桌面应用），DELETE 模式已经足够，而 WAL 会消耗额外的内存和磁盘资源。

WAL 模式是一项"高级特性"，使用者应该在明确需要时主动开启，并理解其代价。

原因二：向后兼容

SQLite 的核心承诺是极致的向后兼容性。一个在 2004 年创建的数据库文件，今天的最新版 SQLite 依然能读取。如果默认改为 WAL：

• 旧版本 SQLite（低于 3.7.0）无法读取新创建的数据库
• 嵌入式系统固化着老版本 SQLite，升级数据库格式会导致设备无法启动
• 跨进程共享数据库的行为会发生变化（WAL 需要共享内存，某些 NFS 环境不支持）

原因三：WAL 的代价并非零成本

所以，WAL 适合我们吗？

对于传统 Web 后端服务——也就是我们正在写的代码——答案是肯定的。我们拥有：

• 多连接并发（连接池通常 4~50 个）
• 读写混合（登录写 + 查询读）
• 可控的运行环境（Kubernetes 或虚拟机，支持共享内存）
• 运维能力（可以监控 WAL 文件大小）

类比： 汽车默认配的是经济型轮胎。你要下赛道跑 200km/h，得自己换高性能轮胎。这不是厂家"疏忽"，是你的使用场景需要主动选择更合适的配置。

对于 Web 后端，WAL 是必需品，DELETE 是灾难。 这正是本文故障的根源。

1.5 致命弱点：并发写锁

SQLite 最需要警惕的限制是 写操作会锁定整个数据库。在默认的 DELETE journal 模式下：

• 一个写操作获取排他文件锁
• 所有其他连接（包括读操作）被阻塞
• 没有 busy_timeout 时，等待是无限的

这正是本文要解决的故障核心。

二、Nginx 配置：proxy_set_header Connection 的区别

在排查故障时，我们注意到了 Nginx 配置的变化。虽然最终根因不在 Nginx，但这两个配置的理解对排查代理层问题很有价值。

2.1 proxy_set_header Connection $connection_upgrade;

这是 Nginx 处理 WebSocket 的标准写法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

http {
    map $http_upgrade $connection_upgrade {
        default upgrade;
        '' close;
    }
    
    server {
        location /ws/ {
            proxy_pass http://backend;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection $connection_upgrade;
        }
    }
}

行为解释：

• 当客户端发起 WebSocket 请求（携带 Upgrade: websocket）时，$connection_upgrade 被设置为 upgrade
• Nginx 转发 Connection: upgrade 给后端，告诉后端保持连接升级状态
• 对于普通 HTTP 请求，$connection_upgrade 为 close，Nginx 发送 Connection: close

核心用途： 让 Nginx 正确代理 WebSocket 协议升级握手。

2.2 proxy_set_header Connection "";

这是清空 Connection 请求头：

1
2
3
4

location /api/ {
    proxy_pass http://backend;
    proxy_set_header Connection "";
}

行为解释：

• 将 Connection 头设置为空字符串，实际上是移除该头
• 后端服务器接收不到 Connection 头，因此不会收到 close 或 upgrade 指令
• 默认情况下，Nginx 会设置 Connection: close，强制每次请求后关闭后端连接

核心用途： 启用 HTTP 连接复用（Keep-Alive），减少握手开销。

2.3 对比总结

在我们的故障场景中，摘掉 Nginx 后登录成功，但后续 API 依然 pending，说明问题不在代理层，而是后端本身。

三、故障排查：从页面卡死到 SQLite 锁

3.1 现象

生产环境出现了一个诡异问题：

1. 用户点击登录按钮后，页面一直 Loading，登录请求迟迟没有响应
2. 摘掉 Nginx 后登录成功，但后续 API（credentials/paging）全部 pending
3. 重启服务后短暂恢复，很快又卡住

3.2 排查方向

去掉 Nginx 代理后登录成功、后续 API 卡死 -> 问题不在代理层 -> 指向后端服务本身。

API 全部 pending 而不是报错 -> 不是业务异常 -> 更像某种阻塞等待。

3.3 关键线索

登录请求会调用 UserRepository.Update（写操作），后续 credentials/paging 都是纯读操作。如果写操作持有锁，读操作就会等待。

查看数据库配置发现：

1
2
3
4

// 问题配置
db.SetMaxOpenConns(50)          // SQLite 同时开 50 个连接
// 没有设置 journal_mode
// 没有设置 busy_timeout

SQLite 默认的 DELETE journal 模式下：

1
2
3
4
5
6
7
8
9

用户点击登录 -> 发起 /login 请求 -> UserRepository.Update（写操作）
                                      ↓
                              SQLite 获取文件级排他锁
                                      ↓
                          连接池中 50 个连接竞争同一把锁
                                      ↓
                       后续所有请求（包括 /login 自身）阻塞
                                      ↓
                              页面一直 Loading

登录触发了写锁，后续 50 个连接中的读请求全部被阻塞——这就是根因。

3.4 错误的修复尝试

第一版修复方案：

1
2
3

db.SetMaxOpenConns(1)
db.Exec("PRAGMA journal_mode=WAL")
db.Exec("PRAGMA busy_timeout=5000")

这个方案有 3 个问题：

3.5 最终正确方案

DSN 配置

1
2
3
4
5
6

// 使用 _pragma 让每个新连接自动应用 PRAGMA
dsn := "file:" + dbPath + "?" +
    "_pragma=journal_mode(WAL)&" +
    "_pragma=synchronous(NORMAL)&" +
    "_pragma=busy_timeout(5000)&" +  // 单位：毫秒（ms），等待5秒后超时返回SQLITE_BUSY
    "_pragma=wal_autocheckpoint(1000)"

连接池配置

1
2
3
4
5
6
7
8
9

// WAL 模式下推荐值：1 写 + 3 读
db.SetMaxOpenConns(4)
db.SetMaxIdleConns(4)
 
// SQLite 是本地文件连接，无 TCP 静默断开风险；
// DSN _pragma 保证新连接自动携带正确配置，无需通过回收连接来刷新。
// 注意：修改 DSN 中的 _pragma 后必须重启服务才能生效。
// （对比：MySQL/PostgreSQL 等网络数据库建议设为 30m，防止防火墙/NAT 丢弃空闲连接）
db.SetConnMaxLifetime(0)

重要约束

所有涉及写操作的业务逻辑必须显式使用 db.BeginTx() 包裹事务，确保多条写语句在同一连接上串行执行。非事务的 db.Exec() 分散到不同连接，在 WAL 模式下虽不会死锁，但会因连接切换增加锁争用开销。

MaxOpenConns(4) 是经验值，适用于读写比约 10:1 的常规 Web 场景。若业务以写入为主（如日志入库），应调小至 1，并在应用层用 channel 或队列串行化写入请求，彻底消除锁竞争。

数据安全性说明

synchronous=NORMAL 在 WAL 模式下，事务提交时仅确保数据写入 WAL 文件并 fsync，但 checkpoint（将 WAL 合并回主库）是异步的。若在 checkpoint 过程中操作系统崩溃，最近一次 checkpoint 之后已提交的事务会丢失（数据库文件本身不会损坏，会回滚到上一个 checkpoint 点）。

对于金融订单、计费、账户余额等零丢失场景，应保持 synchronous=FULL。对于登录状态、缓存、埋点日志等可容忍分钟级数据丢失的场景，NORMAL 是合理的性能选择。

最终参数对比

3.6 为什么 WAL 能解决并发问题？

WAL（Write-Ahead Logging）模式下，写操作不直接修改主库，而是追加到 WAL 文件末尾：

1
2
3
4
5
6
7
8
9
10
11

+-----------------------------------------------------------+
|                       WAL 文件                              |
|   +-------+ +-------+ +-------+ +-------+                 |
|   | 写1   | | 写2   | | 写3   | | ...   |                 |
|   +-------+ +-------+ +-------+ +-------+                 |
+-----------------------------------------------------------+
|           主数据库文件（读为主，checkpoint 时写）           |
|   +---------------------------------------------------+   |
|   |          所有已提交的数据（读为主）                 |   |
|   +---------------------------------------------------+   |
+-----------------------------------------------------------+

关键改进：

1. 写操作：新数据追加到 WAL 文件末尾，不修改主库
2. 读操作：从主库 + WAL 文件合并读取，不被写阻塞
3. Checkpoint：后台将 WAL 数据合并回主库（NORMAL 模式降低频率）

这就是为什么 MaxOpenConns(4) 能在 WAL 下安全运行——多个连接不会因为一个写操作而全部阻塞。

3.7 关于 _pragma 的说明

glebarez/sqlite 驱动支持在 DSN 中直接传递 PRAGMA：

1
2
3

// 格式：_pragma=name(value)
// 多个用 & 连接
dsn := "file:data.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)"

这样每个新连接建立时都会自动执行这些 PRAGMA，不依赖连接池保活。

版本说明： _pragma DSN 参数在 glebarez/sqlite v1.9.0 及以上版本中稳定支持。若使用更早版本，建议升级后使用本文配置。

四、总结

经验教训

SQLite Web 后端接入自查清单

1
2
3
4
5
6
7
8
9
10

[ ] DSN 中包含 _pragma=journal_mode(WAL)
[ ] DSN 中包含 _pragma=busy_timeout(5000)
[ ] 根据数据重要性选择 synchronous=NORMAL 或 FULL
[ ] SetMaxOpenConns 设为 4（读写混合）或 1（纯写入）
[ ] SetConnMaxLifetime(0) — SQLite 本地连接永不过期
[ ] 所有写操作均使用 db.BeginTx() 包裹
[ ] 已移除 cache=shared 参数
[ ] 监控面板已添加 WAL 文件大小告警
[ ] glebarez/sqlite 版本 >= v1.9.0
[ ] 团队已知悉：修改 DSN _pragma 后需重启服务

核心认知

SQLite 默认不是 WAL，不是设计缺陷，而是服务 99% 场景的合理选择。但如果你在用 SQLite 做 Web 后端，你就属于那需要主动开启 WAL 的 1%。

AI