Transaction-Less Batch Update: A Partial-Failure Bug Mesrai Found

A batch job updating account balances. Loops over users, issues a per-row UPDATE. No transaction wrapping the loop. If anything fails mid-loop — network blip, server restart, a single bad row — the database is left in an inconsistent state.

The vulnerable diff

// jobs/migrate-balances.ts
async function migrateBalances() {
  const users = await db.users.findMany();
  // BUG: each update is its own implicit transaction
  for (const u of users) {
    const newBalance = recompute(u);
    await db.query(
      "UPDATE accounts SET balance = $1 WHERE user_id = $2",
      [newBalance, u.id]
    );
  }
}

What is wrong

Each UPDATE in the loop runs as its own auto-commit transaction. The job has no atomic boundary. If row 4,000 of 10,000 throws (deadlock, network timeout, validation error), the first 3,999 updates are persisted and the remaining 6,001 are not. There is no rollback. Recovery is a manual reconciliation job. The fix is either a single transaction wrapping the loop (works for batches up to ~10K rows) or a single bulk-UPDATE statement against a VALUES list (works for any size, faster).

The attack

Failure-mode reproducer:

# Kill the job halfway through:
$ node jobs/migrate-balances.ts &
$ sleep 30 && kill %1

# State after kill:
SELECT count(*) FROM accounts WHERE balance != recompute(user);
-- 5,847 rows still on old balance. 4,153 on new. No log of which.

Recovery requires knowing which users were processed — usually a separate audit table the job did not write to.

Mesrai's review comment

mesraipilot · Bot · reviewed 2 min ago

[mesrai] [code-review] [Logic] [Atomicity] [critical]

Batch update without transaction. Each UPDATE is its own auto-commit
TX. Mid-loop failure leaves the database half-migrated with no
rollback option.

Two correct patterns. Pick by batch size:

  // For batches up to ~10K rows: wrap the loop in a transaction
  await db.$transaction(async (tx) => {
    for (const u of users) {
      await tx.query(
        "UPDATE accounts SET balance = $1 WHERE user_id = $2",
        [recompute(u), u.id]
      );
    }
  }, { timeout: 60_000 });

  // For larger batches: single bulk UPDATE FROM VALUES
  // Postgres handles 100K+ rows efficiently:
  const values = users.map(u => `(${u.id}, ${recompute(u)})`).join(",");
  await db.query(`
    UPDATE accounts SET balance = v.bal
    FROM (VALUES ${values}) AS v(uid, bal)
    WHERE accounts.user_id = v.uid
  `);

For batches in the millions: process in chunks of 10K, each chunk
in a transaction, with a checkpoint table tracking last-processed
id for resumability.

The fix

// jobs/migrate-balances.ts — fixed
async function migrateBalances() {
  const users = await db.users.findMany();
  await db.$transaction(async (tx) => {
    for (const u of users) {
      await tx.query(
        "UPDATE accounts SET balance = $1 WHERE user_id = $2",
        [recompute(u), u.id]
      );
    }
  }, { timeout: 60_000 });
}

The transaction wraps the entire batch. Mid-loop failure rolls back the whole thing — DB stays consistent with pre-job state. Re-run the job and it idempotently re-applies. For very large batches, the bulk UPDATE FROM VALUES form is more efficient because it is one round-trip instead of N.

Why human review missed it

Per-row updates in a loop read as normal application logic. The implicit-transaction semantics are invisible. Reviewers see the work being done and trust the database to be consistent. Mesrai catches the pattern by flagging any sequence of N writes to the same table inside a loop without an explicit transaction or bulk form.

Related rules + further reading

Mesrai rule pack: logic/batch-write-needs-transaction — flags per-row writes in a loop without atomic boundary.

Postgres docs: Concurrency Control — Transactions.

Common in data-migration jobs; harder to spot than the inventory race because it only manifests on partial failure.

Takeaway

Wrap the batch in a transaction. Or use a single bulk UPDATE. Either way the batch has an atomic boundary — no half-migrated state.