Two Accountants, One Invoice: Fixing a Lost Update in Postgres

Here's a bug that never shows up in testing and always shows up in production.

Two accountants open the same invoice. Each records a payment against it. Both requests succeed. Both get a green toast. But the invoice's paid amount only includes one of the payments. The other one is gone, with no error and no log line.

This is a lost update, and I had one in BuildLedger, the construction ERP I built.

#The code that caused it

Recording a payment against an invoice looked like this:

const inv = await invoicesRepository.findById(invoiceId);
const currentBalanceDue = Number(inv.balanceDue);
if (data.allocatedAmount > currentBalanceDue + 0.01) {
  throw BadRequest("Allocation exceeds the balance due");
}

const total = Number(inv.total);
const newPaid = Number(inv.paidAmount) + data.allocatedAmount;
const newBalance = Math.max(0, total - newPaid);

await invoicesRepository.updateFinancials(invoiceId, newPaid, newBalance, newStatus);
return invoicesRepository.addPaymentAllocation({ invoiceId, ...data });

Read, calculate, write. It looks completely reasonable, and that's what makes it dangerous.

#Why it loses money

Run two of those requests at the same time, both paying 10,000 on an invoice that has 0 paid so far:

Request A                         Request B
---------                         ---------
read paid_amount → 0
                                  read paid_amount → 0
newPaid = 0 + 10,000
                                  newPaid = 0 + 10,000
write paid_amount = 10,000
                                  write paid_amount = 10,000   ← overwrites A

Both requests read the same starting value, so the second write replaces the first. Two payments were recorded, but the invoice says 10,000 paid, not 20,000. The balance check doesn't help either: both requests passed it against the same stale balance.

Two smaller problems made it worse:

• The read and the two writes weren't in a transaction, so a failure between them could leave the invoice updated without its allocation row.
• The maths used JavaScript numbers on money (more on that in Money Is Not a Float).

#The fix: lock the row you're about to change

Postgres already solves this. SELECT … FOR UPDATE locks the rows it reads until the transaction ends. Any other transaction that tries to lock the same row waits until the first one commits, then reads the new value.

Prisma doesn't expose FOR UPDATE directly, so the repository uses a raw query inside the transaction:

/**
 * Locks the invoice row until the surrounding transaction ends, so concurrent
 * payment/status changes on the same invoice run one after another.
 * Amounts come back as exact decimal strings.
 */
async lockForUpdate(id: string, tx: Prisma.TransactionClient) {
  const rows = await tx.$queryRaw<LockedInvoice[]>`
    SELECT id, total::text AS total, paid_amount::text AS paid_amount,
           balance_due::text AS balance_due, status::text AS status
    FROM invoices WHERE id = ${id}::uuid FOR UPDATE`;
  return rows[0] ?? null;
}

Casting the amounts to text matters: it keeps the exact decimal value instead of letting the driver turn it into a float.

The service then does the whole read-calculate-write inside one transaction, starting with the lock:

// One transaction + row lock: two allocations to the same invoice can no
// longer both read the old paid amount and overwrite each other.
return prisma.$transaction(async (tx) => {
  const inv = await invoicesRepository.lockForUpdate(invoiceId, tx);
  if (!inv) throw NotFound("Invoice");

  const balanceDue = money(inv.balance_due);
  if (allocated.gt(balanceDue)) {
    throw BadRequest("Allocation exceeds the balance due");
  }

  const newPaid = money(inv.paid_amount).plus(allocated);
  const newBalance = maxMoney(0, money(inv.total).minus(newPaid));
  // …every write below goes through `tx`
});

Now the timeline looks like this:

Request A                         Request B
---------                         ---------
BEGIN; lock invoice → paid 0
                                  BEGIN; lock invoice → waits…
write paid 10,000; COMMIT
                                  …lock acquired → paid 10,000
                                  write paid 20,000; COMMIT

Request B no longer sees a stale value. It waits a few milliseconds and does the right thing. The same lock now guards status changes and removing a payment allocation, because they read and write the same columns.

#Two more races, two more tools

Row locks aren't the only option. The same audit turned up two other races, and each needed a different tool.

Invoice numbers. Generating the next number (INV-0042) by reading the latest one has the same read-then-write problem, but there's no single row to lock. A transaction-scoped advisory lock on the prefix serialises it instead:

await tx.$executeRaw`SELECT pg_advisory_xact_lock(hashtext(${"invoice-number:" + prefix.toUpperCase()}))`;

Two reviewers, one deletion request. Deletions go through an approval workflow, and two reviewers could approve and reject the same request at once. Here the fix is a conditional update: only move the request out of pending if it's still pending, and check whether you won.

const { count } = await prisma.deletionRequest.updateMany({
  where: { id, status: "pending" },
  data: { status: data.status, reviewedBy: data.reviewedBy, reviewedAt: new Date() },
});
return count === 1 ? prisma.deletionRequest.findUnique({ where: { id } }) : null;

If count is 0, someone else got there first, and the API returns 409 Conflict with "This request was just reviewed by someone else."

#How I tested it

The unit test checks the order of operations: the invoice must be locked before it's read, and every write must use the transaction client, not the global one. A write that slips outside the transaction is exactly how this kind of fix quietly breaks later.

That test proves the code is structured correctly. It doesn't fire two real payments at Postgres at the same time, and that integration test is next on my list. Being honest about what a test does and doesn't prove is part of the job.

#The takeaway

If your code reads a value, computes something from it, and writes it back, ask one question: what happens if two requests do this at once? If the answer is "the last one wins", you need a lock, a conditional update, or an atomic UPDATE … SET paid = paid + $1. Pick one on purpose.