深入 · 并发、锁与事务

先说清模型：SQLite 是多读单写。大白话就是——读的人可以随便来（多个读同时进行，互不排斥）；写的人只能排队（同一时刻一个写）。

三种粒度对比

贯穿下面的例子：你账户 100 元，给朋友转 50 元。需要两步：①你的余额 100→50（代码叫 扣款）、②朋友余额 0→50（代码叫 入账）。另一个操作是朋友同时取款 80 元（考验「能不能看到最新余额」）。

这是WAL 模式下依然会报的错。因为 SQLITE_BUSY 不是「有冲突」，而是「我等了你给定的超时时间，还是没拿到锁」。

四个真实有效的处理办法

const db = new Database('prod.db');
db.pragma('journal_mode = WAL');      // 写日志再落盘
db.pragma('busy_timeout = 5000');     // 拿不到锁就最多等 5 秒 ← 关键这一行
db.pragma('synchronous  = NORMAL');   // 配合 WAL：断电可能丢最后几笔，换写入提速
db.pragma('foreign_keys = ON');       // 外键默认关，必须显式开

// ✗ 危险：事务里发了HTTP 请求（可能 3 秒超时）
const tx = db.transaction(() => {
  insertOrder(order);
  callShippingApi(order);      // ← 持锁 3 秒！
});

// ✓ 正确：事务只写库，外部调用放外面
const tx = db.transaction(() => {
  insertOrder(order);
  return db.prepare('SELECT id FROM order WHERE order_no = ?').get(order.orderNo).id;
});
const orderId = tx();
await callShippingApi(order);   // ← 事务已提交，锁已释放

// 带抖动的指数退避：避免两个进程「一起等 10ms 又一起冲」的共振
function retry(fn, { retries = 3, base = 20 } = {}) {
  for (let i = 0;; i++) {
    try { return fn(); } catch (e) {
      if (!/SQLITE_BUSY/.test(String(e)) || i >= retries) throw e;
      const wait = base * 2 ** i + Math.random() * base;   // 20, 40+~20, 80+~20...
      console.warn(`BUSY，第 ${i + 1} 次重试，等 ${wait.toFixed(0)}ms`);
      // 注意：重试的必须是「整个事务」，不是事务里的某一步
      sleepSync(wait);
    }
  }
}
retry(() => db.transaction(() => { /* ... */ })());

-- 怎么确认到底是不是 BUSY 在困扰你
PRAGMA busy_timeout;        -- 看当前超时是多少（0 就是没配）
PRAGMA journal_mode;        -- 确认是不是 WAL（wal 才支持读不阻塞写）
PRAGMA integrity_check;     -- 确认库没被写坏

-- 批量写用一条多值INSERT（少 1000 次写锁竞争）
INSERT INTO inventory_movement (sku, warehouse_id, type, qty, created_at)VALUES
  ('HINGE-H4002', 1, 'in', 10, 1759400000),
  ('HINGE-H4002', 1, 'out', 3,  1759400100),
  ...;   -- 一条语句、一个事务、一次写锁

一次写入的完整过程

-- 手动checkpoint + 顺便把 -wal 文件缩到 0（TRUNCATE 模式）
PRAGMA wal_checkpoint(TRUNCATE);
-- 三种模式的区别：
--   PASSIVE  ：尽可能checkpoint，不阻塞其他连接（默认行为）
--   FULL     ：等所有读者读完才checkpoint
--   RESTART  ：同上，且强制等新读者读到快照之后
--   TRUNCATE ：checkpoint 完把 -wal 文件长度缩成 0（磁盘上最干净）

-- ✓ 正确做法一：用 SQLite 自带的 .backup 命令（它会正确处理 WAL）
sqlite3 prod.db ".backup 'backup/prod-20261002.db'"
# 若需要一致性备份，加 .backup 后立刻 checkpoint，让日志落回主文件
sqlite3 prod.db ".backup 'backup/prod-20261002.db'"
sqlite3 prod.db "PRAGMA wal_checkpoint(TRUNCATE);"

-- ✓ 正确做法二：停服务 + 三个文件一起拷 + 校验 md5
pm2 stop all
cp prod.dbbackup/ && md5sum ...        # 主文件
cp prod.db-wal   backup/  && md5sum ...   # 日志 ← 绝对不能漏
cp prod.db-shm   backup/                # 临时文件，拷不拷都行（会被重建）
md5sum prod.db backup/prod.db           # 两个 md5 必须一致
pm2 start all

-- ✗ 错误做法：服务还在跑，只 cp prod.db
#→ 最近的数据可能还在 -wal 里，备份缺数据；更糟的是 cp 到一半的文件不一致

你的门店系统大概率是 NORMAL。要讲清这个取舍：「掉电时最多丢最后几笔操作，但数据库结构完好、可以继续用」——这在业务上通常是可接受的（用户会重试），但如果丢的是「用户刚提交的订单」，就是投诉。所以关键业务（订单提交、远程双签）可以单独用 synchronous=FULL 的连接来写，其他操作用 NORMAL——两条连接，重要的事走慢路。

这一节讲你项目里真实存在的那个「必然会撞」的问题。

// 正确做法：整个进程共用一个连接（better-sqlite3 是同步 API，本来也不需要连接池）
const db = new Database('/data/prod.db');
db.pragma('journal_mode = WAL');
db.pragma('busy_timeout = 5000');

// ✓ 把这个 db 实例挂到 app.locals，全进程复用
app.locals.db = db;

// ✌ 错误做法：每个请求新建一个连接（连接数不受控，文件锁竞争加剧）
app.get('/api/xxx', (req, res) => {
  const conn = new Database('/data/prod.db');   // ← 每个请求一个句柄
  ...
});

先解释这个词：幂等 idempotent是指同一个操作执行 1 次和执行 10 次，结果一样。
为什么突然讲这个：因为它本质上也是并发问题——但不是「两个人同时做」，是「同一个人/同一个请求做了两次」。而触发它的场景非常真实：

幂等键怎么设计

-- ✓ 做法 A：靠唯一索引天然防重（推荐）
-- order_no 已经是 .notNull().unique()，所以第二次插入会直接失败
INSERT INTO "order" (order_no, dealer_id, status, created_at, updated_at)
VALUES (?, ?, 'submitted', ?, ?);
-- catch 到的错误就是「这单已经下过了」，然后去查出来返回给前端

-- ✓ 做法 B：专门的幂等键表
CREATE TABLE IF NOT EXISTS idempotency (
  key         TEXT PRIMARY KEY,     -- 客户端传来的请求 ID
  result_json TEXT,                  -- 第一次执行的结果，重试时直接返回它
  created_at  INTEGER NOT NULL
);
-- 完整实现思路（伪代码）
function handle(req, idemKey) {
  const done = db.prepare('SELECT result_json FROM idempotency WHERE key = ?').get(idemKey);
  if (done) return JSON.parse(done.result_json);        // ①已经做过 → 直接返回上次的结果
  const result = doRealBusiness(req);                   // ②真正执行（放在事务里）
  db.transaction(() => {
    db.prepare('INSERT INTO idempotency (key, result_json, created_at) VALUES (?, ?, ?)')
       .run(idemKey, JSON.stringify(result), Date.now());
    writeOrder(result);                                 // ③幂等记录和业务写入同一个事务
  })();
  return result;
}

第一步：先确认是哪一类问题

第二步：三个 PRAGMA 快速体检

sqlite3 prod.db
sqlite> PRAGMA journal_mode;              -- 期望 wal
sqlite> PRAGMA busy_timeout;              -- 期望 > 0（不是0）
sqlite> PRAGMA foreign_keys;              -- 期望 1（SQLite 默认是0！）
sqlite> PRAGMA integrity_check;           -- 期望 ok；如果报一堆内容就是文件坏了
sqlite> PRAGMA wal_checkpoint(TRUNCATE);  -- 顺便把 -wal 收干净，再对比前后库大小
sqlite> SELECT COUNT(*) FROM inventory;

第三步：主动压并发，看 BUSY 出现几次

// 并发压测脚本：20 个并发，各跑 50 次扣减，检验有没有丢更新
// 期望：最终库存 = 初始 - 成功次数；BUSY 次数应该很少（配了 busy_timeout 的话）
node -e "
const Database = require('better-sqlite3');
const db = new Database('test.db');
db.pragma('journal_mode = WAL'); db.pragma('busy_timeout = 5000');
db.exec('DROP TABLE IF EXISTS inv');
db.exec('CREATE TABLE inv (sku TEXT PRIMARY KEY, qty INTEGER)');
db.exec(\"INSERT INTO inv VALUES ('SKU1', 1000)\");

let busy = 0, lost = 0;
function worker(n) {
  // ✓ 正确写法：原子操作
  const atomic = db.transaction(() => {
    db.prepare('UPDATE inv SET qty = qty - 1 WHERE sku = ? AND qty >= 1').run('SKU1');
  });
  for (let i = 0; i < 50; i++) {
    try { atomic(); } catch (e) { if (/BUSY/.test(String(e))) busy++; }
  }
}
Promise.all(Array.from({length: 20}, (_, i) => Promise.resolve().then(() => worker(i))))
  .then(() => {
    const left = db.prepare('SELECT qty FROM inv WHERE sku = ?').get('SKU1').qty;
    console.log('剩余:', left, ' 期望:', 1000 - 20*50, '  BUSY 次数:', busy);
    console.log(left === 1000 - 1000 ? '✓ PASS' : '✗ 有丢更新');
  });
"

// 对照实验：换成有 bug 的「读-改-写」，看会不会丢
function workerBuggy() {
  const bad = db.transaction(() => {
    const row = db.prepare('SELECT qty FROM inv WHERE sku = ?').get('SKU1');
    db.prepare('UPDATE inv SET qty = ? WHERE sku = ?').run(row.qty - 1, 'SKU1');
  });
  for (let i = 0; i < 50; i++) { try { bad(); } catch (e) { if (/BUSY/.test(String(e))) busy++; } }
}
// 期望：最终剩余 > 1000-1000（有东西多卖了）→ 复现成功

第四步：怎么知道是不是大事务在锁表

-- SQLite 没有「当前谁在持锁」的完整视图（这是它的短板），但可以这样看：
-- ① 看库文件有没有在增长（WAL 模式）
ls -la prod.db prod.db-wal

-- ② 如果有另一个终端开着，可以用 .timeout 试抢锁
sqlite> .timeout 0
sqlite> BEGIN IMMEDIATE;      -- 立刻返回 SQLITE_BUSY = 现在有人在写
--   注意：这个探测会失败是正常的，目的只是「确认有人在写」

-- ③ 最实用的：看日志里的时间戳
--    同一个时间戳密集出现一批BUSY → 基本就是某个大事务/跑批造成的

这条单独拎出来，因为它是新手最容易上当的一条。「套个大事务不就安全了？」——安全是真的，代价也是真的。

WAL 只解决读写互不阻塞。同一时刻仍然只有一个写者，写竞争原样存在。把 WAL 当并发问题的终点，是最普遍也最贵的误解。

默认 0 意味着「拿不到锁立刻报错」。一行配置就能解决 90% 的 BUSY，不配就是白挨。而且报错抛到业务层通常没有重试逻辑，于是用户看到的就是「偶发操作失败」——最难查的一类问题。

HTTP 请求、等用户确认、写大文件、await 第三方调用——每一个都会把锁的时间从毫秒拉到秒。事务里只该有「读写数据库」。

SQLite 每个连接就是一个文件句柄，直接参与文件锁竞争。连接数不受控 → 竞争加剧 → BUSY 变多。正确做法是全进程共用一个连接实例（better-sqlite3 是同步 API，本来也不需要连接池）。

WAL 模式下最新数据可能还在日志里没合并回主文件。只拷 .db 会静默丢掉最近的数据——而且数据库能正常打开，你发现不了。正确做法：用 .backup 命令，或停服务后三个文件一起拷 + 校验 md5。

WAL 依赖本机文件锁（-shm）。NFS/SMB 上文件锁不可靠，会真的把库写坏。这也意味着「多台机器共享一个 SQLite 文件」在架构上就不成立——横向扩容必须换 PostgreSQL。

两个请求同时来，都查到「不存在」，然后都去插——应用层的 if 判断挡不住。真正的那道防线是数据库的唯一约束：第二个请求撞唯一约束失败，然后去读第一个写好的结果返回。所以幂等方案必须落在数据库上，不能只写在前端或内存里。

SQLite 外键默认关。不开的话「订单明细指向不存在的订单」这种脏数据能直接写进去，直到业务层才炸。必须显式 pragma('foreign_keys = ON')。

任何「用户点一下、有可能重试」的写操作（提交订单、通过审批、提交报价）都要有幂等键。你的 order_no / po_no / wo_no / req_no 都是天然幂等键，用上它们。

• ✓我知道 SQLite 是多读单写模型，判断标准是写入频率不是用户数
• ✓我能说出三条该换 PostgreSQL 的信号：万级/秒写、多进程高频写、需要复杂并发控制
• ✓我知道WAL 模式下库不能放网络盘，所以「多机共享一个库」架构上不成立
• ✓我能画出共享锁/排他锁的三种组合：读读✅、读写❌、写写❌
• ✓我知道三种粒度，并且知道 SQLite 的写锁是整库级（不是行级）——所以大事务哪怕只改一行也堵全库
• ✓我能用转账例子讲出四种隔离级别分别会坏在哪，并说出脏读/不可重复读/幻读各自的大白话
• ✓我知道隔离级别越高越慢，也知道 SQLite 的行为接近可串行化但读能并发（WAL 的价值）
• ✓我知道调隔离级别解决不了丢更新——丢更新是业务逻辑问题，不是隔离问题
• ✓我知道 SQLITE_BUSY 的成因是另一个连接持有写锁，而且默认 busy_timeout=0 是最大原因
• ✓我会配 busy_timeout / synchronous / foreign_keys，并知道 synchronous=OFF 会损坏数据库
• ✓我知道 -wal 和 -shm 各自是什么，-shm 是纯临时的可重建
• ✓我知道 checkpoint 是把日志合并回主文件，默认 1000 页自动触发
• ✓我知道备份必须连 -wal 一起拷，也知道服务运行时直接 cp 会拷出不一致的文件
• ✓我知道 pm2 多进程是真实并发（LumenX 是 3 个），且每个进程各开连接会加剧竞争
• ✓我知道全进程共用一个数据库连接，不每个请求新建
• ✓我知道网络重试会造成重复下单（HTTP 是「至少一次」不是「恰好一次」）
• ✓我能设计幂等键：优先用业务唯一键（order_no/po_no/wo_no/req_no），并且知道真正防线是唯一索引不是先查后插
• ✓我知道幂等记录和业务写入必须同一事务，否则要么重复执行要么永久丢请求
• ✓我会用并发压测脚本 + 对照实验复现丢更新（BUSY 多 = 锁竞争；数据不对 = 丢更新）
• ✓我记住那条红线：不要用「加大事务」当万能药——它解决不了问题，只是把范围放大到全库