Two Writes, One Row: Who Wins?

Two people share a bank card and tap it at two different tills in the same second. There's £100 in the account and each purchase is £80. One of those taps has to fail. If both go through, the bank has paid out £160 from £100 and the balance still claims there's £20 left.

Nothing about that is exotic. It's two requests touching the same row at the same time, which every system with more than one user does constantly. Most of the time the timing is loose enough that nobody notices. This post is about the times it isn't.

Read, compute, write back: the lost update

Here's a withdrawal endpoint. It reads the balance, checks there's enough, subtracts, writes the result back.

@itsnas TypeScript

// POST /wallets/:id/withdraw
async function withdraw(req: Request) {
  const [wallet] = await db`
    SELECT balance FROM wallets WHERE id = ${req.params.id}
  `
  if (wallet.balance < req.body.amount) {
    return json(422, { error: 'Insufficient funds' })
  }
 
  const balance = wallet.balance - req.body.amount
  await db`
    UPDATE wallets SET balance = ${balance} WHERE id = ${req.params.id}
  `
 
  return json(200, { balance })
}

main

Nas (@itsnas)

Correct on its own, wrong in pairs

Run it once and it's right. Run it twice at the same moment and both requests read before either one writes:

Both requests read 100 before either writes. Both pass the check, both write 20, and the second write erases the first.

Two things broke here, and they're worth separating. The invariant broke: the balance check passed twice against money that was only there once. And an update was lost: A's write happened, and then B overwrote it with a number computed from a value that was already stale. Swap the second withdrawal for a £50 deposit and there's no invariant to break, but the lost update is still there. The balance ends at either £20 or £150, never the correct £70.

The pattern has a name, the lost update, and its shape is always the same: read, compute in application code, write back a value derived from the read. The gap between the read and the write is where the other request gets in.

The naive code already picked a strategy

It's tempting to say the endpoint above has no conflict resolution. It does: last write wins. Whichever UPDATE lands second is the state of the world, and the first one is gone without a trace.

That's not always wrong. If a user changes their display name from two tabs, the last name they typed is the one they want, and throwing away the earlier one is correct. Last-write-wins is fine when the new value doesn't depend on the old one.

The balance fails that test. balance - amount is computed from what was read, so writing it back asserts "nothing changed since I looked." That's the question every fix below answers differently: who makes sure nothing changed, and what happens when something did?

When the write is a delta: let the database do it

The simplest fix is to stop reading first. Send the change, not the result, and put the check in the same statement:

@itsnas TypeScript

async function withdraw(req: Request) {
  const [wallet] = await db`
    UPDATE wallets
    SET balance = balance - ${req.body.amount}
    WHERE id = ${req.params.id} AND balance >= ${req.body.amount}
    RETURNING balance
  `
  if (!wallet) return json(422, { error: 'Insufficient funds' })
 
  return json(200, { balance: wallet.balance })
}

main

Nas (@itsnas)

One statement: the check and the write can't be split apart

Re-run the trace. Both requests send their UPDATE. A takes the row lock and writes 20. B waits on that lock, and once A commits, #postgres re-checks B's WHERE clause against the row as it now is: 20 >= 80 is false, so B updates zero rows and gets a clean "insufficient funds." The deposit case works too, because balance + 50 is applied to whatever the balance is at that moment, not to a copy read earlier.

This works under Postgres's default isolation level, with no explicit transaction and no retry loop. Whenever the write can be phrased as "change it by this much, if this still holds," this is the one to reach for. Counters, stock levels, and seat inventory all fit.

Pessimistic: lock the row before you read it

Not every rule fits in a WHERE clause. Say withdrawals also have a daily limit, checked against the sum of today's withdrawals in another table. That needs a read, some logic, then the write, and the gap between them is back.

SELECT ... FOR UPDATE closes it by taking the row lock at read time instead of write time. This is pessimistic locking: assume another request is about to collide with you, and make it wait before it can. Any other request that tries the same thing waits at its own SELECT until this transaction commits:

@itsnas TypeScript

async function withdraw(req: Request) {
  const amount = req.body.amount
 
  return tx(async db => {
    const [wallet] = await db`
      SELECT balance, daily_limit FROM wallets
      WHERE id = ${req.params.id}
      FOR UPDATE
    `
    const [{ today }] = await db`
      SELECT coalesce(sum(amount), 0)::int AS today FROM withdrawals
      WHERE wallet_id = ${req.params.id} AND created_at >= current_date
    `
 
    if (
      wallet.balance < amount ||
      today + amount > wallet.daily_limit
    ) {
      return json(422, { error: 'Withdrawal not allowed' })
    }
 
    await db`
      INSERT INTO withdrawals (wallet_id, amount)
      VALUES (${req.params.id}, ${amount})
    `
    await db`
      UPDATE wallets SET balance = balance - ${amount}
      WHERE id = ${req.params.id}
    `
 
    return json(200, { balance: wallet.balance - amount })
  })
}

main

Nas (@itsnas)

The lock is taken on read, so the second request waits before it even looks

Notice what the lock is actually protecting. It's on the wallets row, but the rule it enforces reads the withdrawals table. That only holds because every path that inserts a withdrawal takes the same wallet lock first. One code path that skips it, say an admin tool that inserts directly, and the race is back. The lock is a convention every writer has to follow, not a property of the data.

Locking two rows brings one more hazard. A transfer from wallet X to wallet Y locks X then Y; a transfer the other way at the same moment locks Y then X. Each now holds the lock the other is waiting for, and Postgres kills one of them with a deadlock error. The fix is to always lock in the same order, whatever the direction of the transfer:

@itsnas TypeScript

await db`
  SELECT id, balance FROM wallets
  WHERE id IN (${from}, ${to})
  ORDER BY id
  FOR UPDATE
`

main

Nas (@itsnas)

Optimistic: check a version when you write

Locks work because the gap between read and write is milliseconds. Some gaps are minutes.

Two people on an ops team open the same merchant's payout settings. Bob changes the bank account and saves. A minute later Alice changes the payout schedule and saves too. Her form still holds the old bank account from when she opened it, so her save writes it straight back over Bob's change. Nobody sees an error. The merchant's next payout goes to an account they asked to stop using.

That's the same lost update, stretched across a coffee break. You can't hold a row lock while someone reads a form, so this time the fix doesn't prevent the collision, it detects it. This is optimistic locking: assume nobody else will write in between, take no lock, and check that assumption at the moment you write. Every row carries a version number, the client sends back the version it loaded, and the write only succeeds if the version hasn't moved:

@itsnas TypeScript

// PUT /merchants/:id/payout-settings
async function updatePayoutSettings(req: Request) {
  const ifMatch = req.header('If-Match')
  if (!ifMatch) return json(428, { error: 'If-Match required' })
 
  // Only a strong tag this API issued, like "3". A weak tag never
  // matches If-Match, and `*` would skip the very check this is for.
  const tag = /^"(\d+)"$/.exec(ifMatch)
  if (!tag) return json(412, { error: 'Version is stale' })
  const loadedVersion = Number(tag[1])
 
  const { schedule, bankAccountId } = req.body
  const [merchant] = await db`
    UPDATE merchants
    SET payout_schedule = ${schedule},
        bank_account_id = ${bankAccountId},
        version = version + 1
    WHERE id = ${req.params.id}
      AND version = ${loadedVersion}
    RETURNING version
  `
  if (!merchant) return json(412, { error: 'Version is stale' })
 
  return json(200, { ok: true }, { ETag: `"${merchant.version}"` })
}

main

Nas (@itsnas)

Zero rows updated means someone else saved first

HTTP already has the vocabulary for this. The GET returns the version as an ETag, the client echoes it in If-Match, a stale version gets 412 Precondition Failed, and a request with no If-Match at all gets 428 Precondition Required. That last one matters: if the header is optional, every client that forgets it is back on last-write-wins without knowing.

Both load version 3. Bob's save moves the row to 4, so Alice's save, still claiming 3, matches nothing and gets a 412 instead of reverting his bank account.

Resolving the 412

Detection is half of it. What happens next depends on who wrote:

Writer	On a 412
A person (Alice)	Reload, show what changed since she opened the form, let her re-apply. Never retry automatically: that's the same overwrite with extra steps.
A machine (a risk-score job)	Re-read, recompute, retry with the new version, with a cap on attempts.

And write less. If Alice's client sends only the field she changed (a PATCH), her save can't revert bank_account_id because it never sends one. It still gets a 412, because the version covers the whole row. To let unrelated edits both land, version each group of fields that belong together: the bank account and its currency share one version, the schedule gets its own. Smaller writes, checked against smaller versions, collide less.

Pessimistic or optimistic?

The Alice and Bob case forces optimistic, because nothing can be held open for minutes. But the version check works just as well on a write that takes milliseconds. Put the daily-limit endpoint on a version column instead of FOR UPDATE and it's still correct: the loser of a collision gets zero rows back, re-reads, and tries again.

So when both would work, the choice is a bet on how often writes to the same row actually collide:

Situation	Pessimistic	Optimistic
On a collision	The second request waits	The second write fails and retries
Cost when collisions are rare	Every write takes a lock anyway	Close to free
Cost when collisions are common	Requests queue, none wasted	Retries pile up, work is thrown away
Gap between read and write	Milliseconds (a lock is held)	Any length (nothing is held)

A wallet two people rarely touch at once is fine either way, and optimistic saves a lock on every request. A hot row, like the stock count on a product in a flash sale, collides constantly: optimistic turns into a retry storm there, and a lock (or the single conditional UPDATE from earlier) keeps requests moving in order.

Why not just turn up the isolation level?

There is one more lever. Postgres runs every transaction at READ COMMITTED by default, which is why the naive endpoint loses the update without complaint. Run it inside a transaction at REPEATABLE READ instead, and the second writer doesn't overwrite: Postgres aborts it with could not serialize access due to concurrent update (SQLSTATE 40001). The second writer still waits on the first one's row lock, same as before; the difference is that once the first commits, the second is aborted instead of allowed to overwrite.

SERIALIZABLE goes further and catches conflicts where no row is written twice. If withdrawals were only rows in a ledger, with no shared balance to update, two of them could each sum today's total, both see room under the limit, and both insert. No single row collides, so REPEATABLE READ lets both through. SERIALIZABLE notices that each transaction read what the other wrote, and aborts one.

It's a real option, and sometimes a cleaner one than threading FOR UPDATE through every query. But it moves the work rather than removing it. Every caller now has to catch 40001 and re-run the whole transaction, with backoff, and a caller that doesn't turns a lost update into a 500. The resolution strategy still has to be decided; the database just forces you to decide it.

Picking one

The question to ask of every write isn't "could this race?" (it can) but what the write actually is:

The write is...	Resolve it with	Style
A new value that doesn't depend on the old one	Last write wins, chosen on purpose	Neither
A change the database can apply (balance, stock, counter)	One conditional `UPDATE`	Neither
Logic between read and write, on a row that's often hit	`SELECT ... FOR UPDATE`	Pessimistic
Logic between read and write, on a row that's rarely hit	Version check or `SERIALIZABLE`, plus retry	Optimistic
A person's edit, made minutes after the read	Version check, then show them the conflict	Optimistic
A machine recompute from stale input	Version check, then re-read and retry	Optimistic

Back at the two tills: with the conditional UPDATE, one tap goes through and the other gets declined, which is exactly what the bank should do. The two people will never know how close they came to the same £100. The system knows, because it stopped assuming that nothing changed between looking and writing.

Pairs with

Why Payment Retries Need Idempotency Keys Retry with Backoff & Jitter

Two Writes, One Row: Who Wins?

Read, compute, write back: the lost update

The naive code already picked a strategy

When the write is a delta: let the database do it

Pessimistic: lock the row before you read it

Optimistic: check a version when you write

Resolving the 412

Pessimistic or optimistic?

Why not just turn up the isolation level?

Picking one

When the Settlement Report Disagrees With Your Ledger

How Does Facebook's Distributed Like Counter Work?

How Does Instagram Instantly Know If Your Username Is Available?

Transactional Outbox: Two Systems, One Commit