SQLite 并发写入优化实战：从锁库到内存缓存的解决方案

问题发现：SQLite 的并发写入瓶颈

在本地进行批量读写测试时，发现：

• 读取完全正常，无任何异常
• 批量写入（如并发评论/资料更新）时频繁触发锁库，导致写入失败
• 错误表现为 database is locked，这是 SQLite 的写锁独占机制在起作用

第一轮优化：启用 WAL 模式

通过 DeepSeek 建议，首先尝试 Write-Ahead Logging (WAL) 模式：

db, err := sql.Open("sqlite", "file:data.db?_journal_mode=WAL")

效果：

• 相比默认的 DELETE 日志模式，WAL 允许读写并发（读不阻塞写，写不阻塞读）
• 但多个写入仍然会互相阻塞，高并发下依然会出现锁库

第二轮优化：批量事务合并

DeepSeek 进一步建议：

1. 合并多个写入为单个事务，减少锁竞争
2. 按阈值或定时触发写入（如每 100 条评论或每 5 秒提交一次）

实现代码示例：

// 使用事务批量写入
func batchUpdate(updates []Update) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // 出错时回滚

    for _, u := range updates {
        _, err = tx.Exec("UPDATE books SET views = views + ? WHERE id = ?", u.Views, u.ID)
        if err != nil {
            return err
        }
    }
    return tx.Commit() // 统一提交
}

效果：

• 减少了锁冲突频率，但极端并发下仍会偶发锁库

终极方案：内存缓存 + 异步持久化

最终采纳的解决方案：

1. 内存缓存热点数据（如书籍信息、用户资料）
2. 写入先进入内存队列，异步批量落盘
   • 高频更新操作（如点赞、浏览计数）暂存内存
   • 通过定时任务或阈值触发数据库写入
3. 关键数据绕过缓存直写数据库

实现架构：

客户端请求 → 内存缓存（读写） → 定期同步 → SQLite
                     ↓
                 关键数据直写

代码示例：

var (
    cache = make(map[int]Book) // 简易内存缓存
    mu    sync.RWMutex
)

// 读取优先走缓存
func GetBook(id int) (Book, error) {
    mu.RLock()
    defer mu.RUnlock()
    if book, ok := cache[id]; ok {
        return book, nil
    }
    // 缓存未命中时查数据库
    return fetchFromDB(id)
}

// 写入先更新缓存，异步持久化
func UpdateViews(id int) {
    mu.Lock()
    defer mu.Unlock()
    cache[id].Views++ // 内存更新

    // 触发异步落盘（实际可合并更多操作）
    go func() {
        _, err := db.Exec("UPDATE books SET views = views + 1 WHERE id = ?", id)
        if err != nil {
            log.Printf("异步写入失败: %v", err)
        }
    }()
}

优化结果

• 日志监控显示：database is locked 错误完全消失
• 吞吐量提升：并发写入请求处理速度提高 10 倍+
• 代价：极端情况下可能丢失少量非关键数据（如浏览计数），但对业务无影响

经验总结

1. SQLite 的并发瓶颈主要在写入锁竞争，WAL 模式治标不治本
2. 内存缓存 + 异步持久化是小型项目的高效解决方案
3. 关键数据仍需保证实时持久化
4. 未来可考虑改用 Redis + SQLite 组合，进一步分离读写负载

通过这次优化，深刻体会到：没有完美的技术方案，只有适合场景的权衡。AI 的建议提供了关键方向，但最终仍需结合实际测试调整。