.NET中SQLite并发写入时频繁触发“database is locked”异常

Question

在.NET应用中使用SQLite进行高并发写入时，常因多个线程/请求同时尝试获取数据库独占写锁而触发“database is locked”异常。该问题并非源于连接泄漏或未关闭命令，而是SQLite默认采用**全局写锁（WAL模式未启用）+ 阻塞式忙等待（BusyTimeout不足）+ 连接复用不当**所致。典型场景包括：ASP.NET Core中将`SqliteConnection`注入为Singleton、批量插入未批处理、事务粒度过大或未显式设置`BusyTimeout`；即使启用了`PRAGMA journal_mode=WAL`，若读写线程未正确配合检查点（checkpoint）机制，仍可能因写入者阻塞检查点或读取者持有旧快照而加剧锁争用。如何在不引入SQL Server等重型方案的前提下，通过连接生命周期管理、WAL模式调优、合理事务边界与重试策略，实现稳定、可伸缩的SQLite并发写入？

Answer 1

```html

一、现象定位：从“database is locked”异常切入底层锁机制

该异常并非 .NET 层面的资源泄漏信号，而是 SQLite 内核级写入阻塞的直接反馈。SQLite 默认使用 DELETE 日志模式（全局排他锁），任何写事务需独占整个数据库文件——即使仅更新单行，也会阻塞其他所有写操作及部分读操作。在 ASP.NET Core 高并发场景下（如每秒数百请求写入日志/事件/会话），此设计成为性能瓶颈。

二、根因分层解析：三大耦合性缺陷

• 锁粒度缺陷：未启用 WAL 模式 → 全局写锁 → 写-写/写-读强互斥
• 等待策略缺陷：默认 BusyTimeout=0（即立即失败），未配置合理退避窗口
• 连接生命周期缺陷：SqliteConnection 注入为 Singleton → 连接复用引发隐式长事务与状态污染

三、WAL 模式深度调优：不止于 PRAGMA 设置

启用 WAL 是必要但非充分条件。需组合以下四步：

配置项	推荐值	作用说明
PRAGMA journal_mode = WAL	执行一次，持久化

开启多版本并发控制（MVCC）基础

PRAGMA synchronous = NORMAL

避免 FULL 带来的 fsync 开销

平衡数据安全性与吞吐量

PRAGMA wal_autocheckpoint = 1000

每 1000 页 WAL 日志触发自动检查点

防 WAL 文件无限膨胀阻塞写入

PRAGMA busy_timeout = 3000

连接级超时（毫秒）

配合 .NET 的 ConnectionString 中 Busy Timeout=3000

四、连接生命周期重构：基于作用域的精准管控

绝对禁止 Singleton 注入 SqliteConnection。正确模式如下：

// ✅ 正确：Scoped + using 显式释放
services.AddScoped<ISqliteConnectionFactory, SqliteConnectionFactory>();
// 在服务中：
using var conn = _factory.CreateConnection();
conn.Open();
using var tx = conn.BeginTransaction();
// ... 执行写入
tx.Commit(); // 自动释放连接

五、事务边界治理：从“大事务”到“微事务”

典型反模式：foreach (var item in list) { Insert(item); } → N 次事务开销 + N 次 WAL 写入争用。应改为：

• 批量插入：使用 INSERT INTO ... VALUES (...), (...), (...) 单语句写入 ≤ 500 行
• 分片事务：将万级数据切分为 100–500 条/批，每批独立短事务
• 异步队列缓冲：引入 Channel<WriteBatch> + 后台工作者批量落库，解耦请求响应与 I/O

六、智能重试策略：指数退避 + 锁冲突感知

捕获 SqliteException 并识别 SQLITE_BUSY（错误码 5）：

public async Task<bool> ExecuteWithRetryAsync(Func<Task> operation, int maxRetries = 3)
{
    for (int i = 0; i <= maxRetries; i++)
    {
        try
        {
            await operation();
            return true;
        }
        catch (SqliteException ex) when (ex.SqliteErrorCode == 5)
        {
            if (i == maxRetries) throw;
            await Task.Delay(TimeSpan.FromMilliseconds(Math.Pow(2, i) * 100));
        }
    }
    return false;
}

七、检查点协同机制：读写线程的 WAL 生命周期协同

WAL 模式下，若长期无读操作（或读连接未关闭），检查点无法推进，WAL 文件持续增长并最终阻塞写入。解决方案：

1. 定期后台检查点：PRAGMA wal_checkpoint(TRUNCATE) 每 30 秒执行一次（通过 IHostedService）
2. 读连接显式关闭：避免 DataReader 长期持有 snapshot
3. 监控 WAL 状态：PRAGMA journal_size_limit + PRAGMA wal_checkpoint 返回值分析

八、性能验证指标与压测建议

上线前必须验证以下 5 项关键指标：

• 平均写入延迟 ≤ 15ms（P95）
• “database is locked” 异常率 < 0.01%（万次写入）
• WAL 文件大小稳定在 ≤ 50MB（避免 checkpoint 失效）
• 连接池活跃数 ≤ 最大连接数 × 70%
• 同步写入吞吐 ≥ 800 TPS（本地 SSD 环境）

九、生产就绪 checklist（含代码片段）

graph TD
    A[启动时执行初始化] --> B[SET journal_mode=WAL]
    A --> C[SET synchronous=NORMAL]
    A --> D[SET busy_timeout=3000]
    E[每次写入] --> F[using var conn = factory.Create()]
    E --> G[using var tx = conn.BeginTransaction()]
    E --> H[单批≤500条 INSERT]
    I[后台服务] --> J[每30s wal_checkpoint TRUNCATE]
    I --> K[监控 sqlite_stat4 / wal_checkpoint 返回值]

十、进阶演进路径：面向百万级日写入的弹性架构

当单库写入逼近 10K TPS 时，可平滑演进：

• 分库分表：按时间（日志）或租户哈希路由至不同 SQLite 文件
• 写-读分离：主库 WAL 写入 + 只读副本通过 sqlite3_wal_hook 同步（C# P/Invoke 封装）
• 嵌入式消息队列：集成 LiteDB 或 SQLite-Fulltext + FTS5 实现写缓冲与异步索引构建
• 可观测性增强：暴露 /metrics 端点，采集 wal_checkpoint_duration_ms、busy_wait_count 等自定义指标

```