如何正确混用better-sqlite3与异步代码？解决数据库锁定问题

第二次尝试仍报错的原因 #

1. 异步等待引发的事件循环冲突：Node.js中await slowAsyncProcess会让出事件循环控制权，若此时有其他异步任务（比如定时器、其他数据库操作）访问同一数据库文件，就会和当前操作产生锁竞争——SQLite是数据库级排他锁，写操作需要独占锁，读操作需要共享锁，两者无法同时持有。
2. 高频单条更新触发锁超时：处理1M+行时，每次单独执行UPDATE会频繁获取、释放排他锁，当操作频率过高时，SQLite默认的锁等待超时（通常几秒）会被触发，直接抛出锁错误。
3. 重复编译SQL语句：upd函数每次都重新prepare更新语句，额外增加了数据库交互开销，进一步加剧锁竞争概率。
4. ID遍历的无效查询：假设id连续递增的遍历方式，若表中存在id缺失的情况，会产生大量无效SELECT查询，多余的数据库操作会增加锁冲突可能性。

正确实现方式 #

方案1：分批次读取+并行异步处理+事务批量更新 #

该方案平衡内存占用与处理效率，大幅减少锁竞争：

const db = require('better-sqlite3')('your-database.db');
// 延长锁等待超时时间至30秒，避免轻易触发锁错误
db.pragma('busy_timeout = 30000');

// 提前编译复用SQL语句，避免重复编译开销
const getBatch = db.prepare('SELECT id, x FROM t LIMIT ? OFFSET ?');
const updateStmt = db.prepare('UPDATE t_new SET x = @x WHERE id = @id');

const batchSize = 1000; // 每次处理1000行，可根据内存调整
const totalRows = db.prepare('SELECT COUNT(*) AS count FROM t').get().count;

async function processBatch(offset) {
    // 读取一批数据
    const rows = getBatch.all(batchSize, offset);
    if (rows.length === 0) return;

    // 并行处理当前批次的异步计算
    const processed = await Promise.all(
        rows.map(async row => {
            if (!row.x) return null;
            const xNew = await slowAsyncProcess(row.x);
            return { id: row.id, x: xNew };
        })
    );

    // 过滤无需更新的行
    const validUpdates = processed.filter(Boolean);

    // 使用事务批量更新，减少锁持有时间
    if (validUpdates.length > 0) {
        const batchUpdateTx = db.transaction(updates => {
            for (const update of updates) {
                updateStmt.run(update);
            }
        });
        batchUpdateTx(validUpdates);
    }

    // 递归处理下一批
    await processBatch(offset + batchSize);
}

// 启动处理流程
processBatch(0)
    .then(() => {
        console.log('所有数据处理完成');
        db.close();
    })
    .catch(err => {
        console.error('处理失败:', err);
        db.close();
    });

async function slowAsyncProcess(x) {
    // 替换为你的实际异步计算逻辑
    await new Promise(resolve => setTimeout(resolve, 10));
    return x * 2;
}

方案2：逐行处理+锁安全优化 #

如果必须逐行处理，可通过以下方式避免锁冲突：

const db = require('better-sqlite3')('your-database.db');
db.pragma('busy_timeout = 30000');

// 复用编译好的SQL语句
const getNextRow = db.prepare('SELECT id, x FROM t WHERE id > ? ORDER BY id LIMIT 1');
const updateStmt = db.prepare('UPDATE t_new SET x = @x WHERE id = @id');

async function processNextRow(lastId = -1) {
    const row = getNextRow.get(lastId);
    if (!row) return;

    // 读取完成后读锁已释放，再执行异步计算
    let xNew;
    if (row.x) {
        xNew = await slowAsyncProcess(row.x);
    }

    // 用事务包裹单条更新，稳定锁状态
    if (xNew !== undefined) {
        const tx = db.transaction(() => {
            updateStmt.run({ x: xNew, id: row.id });
        });
        tx();
    }

    // 递归处理下一行
    await processNextRow(row.id);
}

processNextRow()
    .then(() => {
        console.log('处理完成');
        db.close();
    })
    .catch(err => {
        console.error('处理失败:', err);
        db.close();
    });

async function slowAsyncProcess(x) {
    // 你的异步逻辑
    await new Promise(resolve => setTimeout(resolve, 10));
    return x * 2;
}

核心优化要点 #

• 设置busy_timeout：让SQLite在遇到锁时等待足够长的时间，避免直接抛出错误。
• 复用Prepared语句：减少SQL编译次数，降低数据库交互开销。
• 事务批量操作：将多个更新合并到一个事务中，减少锁的获取/释放次数，提升效率同时降低锁冲突。
• 分批次读取：避免一次性加载大量数据占用内存，同时控制数据库操作频率。
• 无假设遍历：通过id > lastId的方式遍历，避免因id缺失产生无效查询。

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