如何改写SQLite3的SELECT+UPDATE语句避免竞态条件并获取更新行？

这个问题在并发任务调度（比如爬虫页面抓取）场景里非常典型——先查询再更新的两步操作在多进程/线程并发时很容易出现竞态，多个进程会同时选中同一行数据进行处理。下面给你两种可靠的解决方案，都能消除竞态，同时让你明确知道哪一行被更新了：

方案1：使用UPDATE ... RETURNING（推荐，SQLite 3.35.0+） #

如果你的SQLite版本在3.35.0及以上（这个版本开始支持RETURNING子句），这是最优解：把查询和更新合并成一个原子操作，直接返回被更新的行数据，完全避免竞态。

代码示例（node-sqlite3） #

const db = require('sqlite3').Database('./your-db-file.db');

// 替换成你的目标域名参数
const targetDomain = 'example.com';

db.get(`
  UPDATE pages 
  SET crawled = DATETIME('now') 
  WHERE id = (
    SELECT id FROM pages 
    WHERE domain = ? 
    ORDER BY crawled ASC 
    LIMIT 1
  )
  RETURNING *
`, [targetDomain], function(err, updatedRow) {
  if (err) {
    return console.error('操作失败:', err.message);
  }

  if (updatedRow) {
    // 这里直接拿到被更新的完整行数据，可直接用于后续处理
    console.log('成功抓取并更新页面:', updatedRow);
    // 示例：processPage(updatedRow.content);
  } else {
    console.log(`域名${targetDomain}下没有待抓取的页面`);
  }
});

为什么能解决竞态？ #

整个UPDATE语句是SQLite的原子操作，执行时会自动锁定目标行，确保同一时间只有一个会话能成功更新该行。RETURNING *直接返回被修改的行数据，不需要额外查询，既高效又从根源上避免了竞态。

方案2：事务 + SELECT ... FOR UPDATE（兼容旧版SQLite） #

如果你的SQLite版本低于3.35.0，不支持RETURNING，可以用排他事务+行级锁的方式来实现原子性：

代码示例（node-sqlite3） #

const db = require('sqlite3').Database('./your-db-file.db');
const targetDomain = 'example.com';

// 使用serialize确保事务内的语句按顺序执行
db.serialize(() => {
  // 开启排他事务，锁定相关资源
  db.run('BEGIN EXCLUSIVE TRANSACTION', (err) => {
    if (err) throw err;
  });

  // 用FOR UPDATE锁定选中的行，其他会话无法修改/锁定该行直到事务提交
  db.get(`
    SELECT * FROM pages 
    WHERE domain = ? 
    ORDER BY crawled ASC 
    LIMIT 1 
    FOR UPDATE
  `, [targetDomain], function(err, selectedRow) {
    if (err) {
      db.run('ROLLBACK');
      return console.error('查询失败:', err.message);
    }

    if (!selectedRow) {
      db.run('ROLLBACK');
      return console.log(`域名${targetDomain}下没有待抓取的页面`);
    }

    // 更新选中的行
    db.run(`
      UPDATE pages 
      SET crawled = DATETIME('now') 
      WHERE id = ?
    `, [selectedRow.id], function(err) {
      if (err) {
        db.run('ROLLBACK');
        return console.error('更新失败:', err.message);
      }

      // 提交事务，释放锁
      db.run('COMMIT', function() {
        // 这里可以使用选中的行数据进行后续处理
        console.log('成功抓取并更新页面:', selectedRow);
        // 示例：processPage(selectedRow.content);
      });
    });
  });
});

为什么能解决竞态？ #

BEGIN EXCLUSIVE TRANSACTION开启了排他事务，SELECT ... FOR UPDATE会锁定查询到的行，其他并发会话必须等待当前事务提交后才能访问该行，从根本上避免了多个进程同时选中同一行的问题。

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