积分并发扣款竞态 Bug 复盘：从 Lost Update 到三重防护

// hold — FOR UPDATE 锁行 + 原子 UPDATE + WHERE 二次校验
const candidates = await tx.execute(
  sql`SELECT id, remaining - held as available
      FROM credit
      WHERE user_id = ${userId} AND remaining > held AND status = 'active' ...
      ORDER BY priority ASC, end_at ASC NULLS LAST
      FOR UPDATE`
);

for (const row of rows) {
  const holdAmount = Math.min(row.available, remaining);
  // 原子 UPDATE：WHERE 条件保证不超扣
  await tx.execute(
    sql`UPDATE credit
        SET held = held + ${holdAmount}
        WHERE id = ${row.id}
          AND remaining - held >= ${holdAmount}`  // ← 二次校验
  );
}

// consume — FOR UPDATE + 原子减
const candidates = await tx.execute(
  sql`SELECT id, held FROM credit
      WHERE user_id = ${userId} AND held > 0 AND status = 'active'
      ORDER BY priority ASC, end_at ASC NULLS LAST
      FOR UPDATE`
);

for (const row of rows) {
  const consumeAmount = Math.min(row.held, remaining);
  await tx.execute(
    sql`UPDATE credit
        SET held = held - ${consumeAmount},
            used = used + ${consumeAmount},
            remaining = remaining - ${consumeAmount}
        WHERE id = ${row.id}
          AND held >= ${consumeAmount}`  // ← 二次校验
  );
}

时刻 T1: hold(user_A, 205) 开始事务
         → SELECT ... FOR UPDATE: 锁住 credit 行
         → UPDATE SET held = held + 205 WHERE remaining - held >= 205
         → COMMIT, 释放锁

时刻 T2: hold(user_A, 205) 等待锁...拿到锁
         → 读取最新值（T1 已提交的）
         → UPDATE SET held = held + 205 WHERE remaining - held >= 205
         → COMMIT

两次 hold 各自正确增加 205，互不干扰 ✅

时刻 T1: consume(gen_A, 205) 开始事务
         → SELECT ... FOR UPDATE: 锁住 credit 行, held=410
         → UPDATE SET held = held - 205 WHERE held >= 205
         → COMMIT

时刻 T2: consume(gen_B, 205) 等待锁...拿到锁
         → 读取最新值: held=205
         → UPDATE SET held = held - 205 WHERE held >= 205
         → COMMIT
         
最终: held=0, used 正确增加了 410 ✅

-- 1. held 卡住（不应有）
SELECT * FROM credit WHERE held > 0;

-- 2. 卡住的 generation（不应有）
SELECT * FROM generation WHERE status IN ('queued', 'processing', 'completing');

-- 3. succeeded 但缺 consume（不应有）
SELECT g.id, g.model_id, g.credits_cost
FROM generation g
WHERE g.status = 'succeeded'
  AND NOT EXISTS (
    SELECT 1 FROM credit_transaction ct
    WHERE ct.generation_id = g.id AND ct.type = 'consume'
  );

-- 4. failed/timeout 但缺 refund（不应有）
SELECT g.id, g.model_id, g.status
FROM generation g
WHERE g.status IN ('failed', 'timeout')
  AND NOT EXISTS (
    SELECT 1 FROM credit_transaction ct
    WHERE ct.generation_id = g.id AND ct.type IN ('refund', 'consume')
  );

-- 5. 数值一致性
SELECT * FROM credit WHERE used + remaining != total;