执行大型SQLite事务时降低内存消耗的问题排查

可能的错误原因 #

1. 事务生命周期管理不当 #

• 如果批次循环中没有正确完成Commit/Rollback，比如Commit出错后未处理、遗漏Rollback操作，事务会一直处于活跃状态，数据库缓存里的脏数据无法被清理，内存也无法释放。
• Go的sql.Tx对象若未执行Commit或Rollback，会持续持有数据库资源，导致内存泄漏。

2. SQLite PRAGMA参数的影响 #

• cache_size=1000000：默认单位是数据库页（通常4KB），这意味着你设置了约4GB的内存缓存。SQLite会优先将数据留在缓存中，即使Commit完成，只要缓存空间充足，不会立刻将数据刷到磁盘文件。
• journal_mode=OFF + synchronous=0：关闭了事务日志和同步刷盘机制，Commit操作仅会将数据标记为已提交，但不会强制写入磁盘，数据仍会留在内存缓存里。

3. 批量插入代码逻辑漏洞 #

• 误用DB.Exec而非Tx.Exec，导致实际使用SQLite自动事务，而非你手动开启的批次事务，相当于还是单事务在运行。
• 未关闭预处理语句sql.Stmt，或重复创建Stmt但未释放，导致内存和资源占用持续增长。
• 处理完的批次数据未主动释放（如切片未清空、引用未断开），Go的GC无法及时回收这部分内存。

修复方案 #

1. 严格管控事务生命周期 #

确保每个批次的事务都能完成Commit或Rollback，用defer保证异常情况下的资源清理：

import "database/sql"
_ "github.com/mattn/go-sqlite3"

// 假设dataBatches是分好块的待插入数据
for _, batch := range dataBatches {
    tx, err := db.Begin()
    if err != nil {
        // 处理事务开启错误
        continue
    }
    defer tx.Rollback() // 确保失败时回滚事务

    // 绑定事务内的预处理语句
    stmt, err := tx.Prepare("INSERT INTO your_table (col1, col2) VALUES (?, ?)")
    if err != nil {
        // 处理预处理错误
        continue
    }
    defer stmt.Close() // 及时关闭Stmt释放资源

    // 执行批次插入
    var execErr error
    for _, item := range batch {
        _, execErr = stmt.Exec(item.Col1, item.Col2)
        if execErr != nil {
            break
        }
    }

    // 无错误则提交事务
    if execErr == nil {
        if err = tx.Commit(); err != nil {
            // 处理提交错误
        } else {
            // 提交成功后主动清空批次数据，帮助GC回收
            batch = nil
        }
    }
}

2. 调整PRAGMA参数，平衡速度与内存 #

• 把cache_size改到合理值，比如设置为**-200000**（负号表示单位为KB，即200MB），避免缓存占用过多内存：

  PRAGMA cache_size = -200000;
  

• 若需要强制Commit后刷盘，可在Commit后执行检查点命令，强制将缓存数据写入磁盘：

  _, err = db.Exec("PRAGMA wal_checkpoint(FULL);")
  

3. 主动释放数据资源 #

每个批次提交完成后，将数据切片置为nil，帮助Go回收内存：

// 批次提交成功后
batch = nil

内容的提问来源于stack exchange，提问作者WayMax