一、 场景痛点：SQLite 的“假死”与崩溃

在代码审查中，我们发现很多做工控上位机或边缘网关的工程师，把 SQLite 当作文本文件来用：

• 现状：一个 Python 脚本负责 100ms 一次的高频写入（采集数据），另一个 Go 程序负责每秒读取（上传云端），还有一个 Web 界面偶尔查询历史记录。

• 事故：

1. "Database is locked"：当写入频率变高时，程序频繁抛出此异常，导致采集数据丢失。

2. I/O 阻塞：界面查询历史数据时，采集线程被卡住，整个网关像死机了一样。

3. 数据库腐烂 (Corruption)：意外断电后，数据库文件损坏，无法打开，且没有备份。

SQLite 默认的 DELETE Journal 模式 性能极差且不支持并发。通过启用 WAL (Write-Ahead Logging) 模式和正确的 Pragma 调优，SQLite 的性能可以提升 10 倍，且支持“一写多读”并发。

二、 架构设计：WAL 模式 + 读写分离

不要试图用多线程去抢一把锁。架构上应遵循：

1. 开启 WAL 模式：写入操作不再锁住主数据库文件，而是写入 -wal 日志文件。读取操作可以同时进行，互不阻塞。

2. 单写入者 (Single Writer)：在应用层设计一个单例的 Writer 进程/协程，所有写入请求通过 Channel 或 Queue 排队进入。

3. 多读取者 (Multi Readers)：读取连接可以有多个，随意并发。

采集线程/Web查询 --(读请求)--> [SQLite Main DB]

采集线程 --(写请求)--> [SQLite WAL File] --(Checkpoint)--> [SQLite Main DB]

三、 核心实施步骤 (Copy & Paste)

无论你用 Python, Go 还是 C#，初始化连接时必须执行以下 SQL 指令。

1. 核心 Pragma 配置代码 (Python 示例)

import sqlite3

def create_connection(db_file):
    conn = sqlite3.connect(db_file, timeout=10.0) # 设置较长的等待锁时间
    
    # --- 核心调优开始 ---
    cursor = conn.cursor()
    
    # 1. 开启 WAL 模式 (性能提升 5-10 倍，支持读写并发)
    # 只要设置一次，属性会持久化到数据库文件中
    cursor.execute("PRAGMA journal_mode=WAL;")
    
    # 2. 放宽同步等级 (平衡性能与安全性)
    # NORMAL: 在 WAL 模式下最安全且高效的设置。只有在 Checkpoint 时才强制刷盘。
    # 相比 FULL (默认)，写入速度极大提升，且断电不容易坏库。
    cursor.execute("PRAGMA synchronous=NORMAL;")
    
    # 3. 使用内存临时存储 (减少 Flash 磨损)
    cursor.execute("PRAGMA temp_store=MEMORY;")
    
    # 4. 增加页缓存 (用内存换速度)
    # -20000 表示占用约 20MB 内存 (默认只有 2MB)
    cursor.execute("PRAGMA cache_size=-20000;")
    
    # 5. 忙碌超时处理 (防止立刻报错)
    cursor.execute("PRAGMA busy_timeout=5000;") 
    
    cursor.close()
    return conn

# 使用示例
conn = create_connection("edge_data.db")
# ... 执行业务 ...

2. 定期 Checkpoint (检查点)

WAL 文件会无限增长，直到触发 Checkpoint 将数据合并回主库。虽然 SQLite 有自动 Checkpoint，但在工业高频写入下，建议手动控制。

在写入线程的空闲间隙（如每隔 1 分钟）：

-- PASSIVE: 尽可能合并，但不阻塞其他读写 PRAGMA wal_checkpoint(PASSIVE);

四、 踩坑复盘 (Red Flags)

1. 共享网络挂载 (NFS/SMB) 的禁区

• 现象：为了方便备份，把 SQLite 数据库文件放在了 NAS 挂载的目录里。

• 后果：数据库必定损坏。SQLite 的文件锁依赖于操作系统的 fcntl，在网络文件系统中即使是 WAL 模式也不可靠。

• 对策：SQLite 必须放在本地磁盘。如果需要备份，请使用 VACUUM INTO 'backup.db' 命令生成副本，再把副本传到 NAS。

2. 僵尸 WAL 文件

• 现象：运行一个月后，发现 edge_data.db-wal 文件竟然有 2GB 大，而主文件只有 100MB。

• 原因：有某个“长连接”的读取事务（Transaction）一直没提交（Commit）或回滚（Rollback），导致 Checkpoint 无法执行，旧日志无法回收。

• 对策：检查代码，确保所有的 SELECT 操作即使出错也必须关闭 Cursor 或提交事务。严禁在事务未关闭时去执行耗时的网络请求。

3. Flash 写放大

• 风险：默认的 Page Size 是 4KB。如果你的 Flash 物理页是 16KB，每次写入 4KB 都会引发一次擦除重写（Read-Modify-Write）。

• 优化：在建库时设置 PRAGMA page_size=4096 (或 8192/16384，取决于 SSD/eMMC 规格)。这能显著延长网关硬盘寿命。

五、 关联资源与选型

SQLite 跑得好，硬件不能太拉垮。

• 硬件推荐：

• 带掉电保护的工业级 SSD：WAL 模式虽然抗断电，但硬件级的 PLP (Power Loss Protection) 是最后一道防线。

  
  

软件替代：

• 如果数据量超过 10GB 且主要是时序数据，请迁移到 TDengine 或 VictoriaMetrics。SQLite 不适合存海量时序数据。

  
  

代码片段

我们封装了一个 "Python SQLite 工业级 Wrapper" 类。

内置了：自动 WAL 配置、单例写入锁、定时备份机制、以及损坏自动修复逻辑。