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.

Start with the version that looks fine

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

@itsnas — TypeScript

Hide line numbersCopy code

// 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 })
}

Show all 16 lines

main

Nas (@itsnas)

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

Request A

Postgres

Request B

SELECT balance

100

SELECT balance

100

SET balance = 20

SET balance = 20

Request A

Postgres

Request B

1. Request A to Postgres: SELECT balance
2. Postgres to Request A: 100
3. Request B to Postgres: SELECT balance
4. Postgres to Request B: 100
5. Request A to Postgres: SET balance = 20
6. Request B to Postgres: SET balance = 20

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

Hide line numbersCopy code

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)

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

Hide line numbersCopy code

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 })
  })
}

Show all 33 lines

main

Nas (@itsnas)

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:

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

Hide line numbersCopy code

// 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}"` })
}

Show all 25 lines

main

Nas (@itsnas)

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.

Re-run the trace. Bob loads version 3, Alice loads version 3. Bob saves; the row becomes version 4. Alice saves with If-Match: "3", the WHERE matches nothing, and she gets a 412 instead of silently reverting Bob's bank account.

Resolving the 412

Detection is half of it. Something still has to decide what happens next, and the right answer depends on who made the write.

For a person, show them. Reload the latest version, point out what changed since they opened the form, and let them re-apply their edit on top of it. Don't retry a human edit automatically: retrying Alice's stale form with the fresh version number just performs the same overwrite with extra steps.

For a machine, say a job that recomputes a merchant's risk score, re-read and retry. Its input was stale, so it fetches the new row, recomputes, and tries again with the new version, with a cap on attempts so a hot row can't spin it forever.

And write less. Alice only changed the schedule. If the client sends just the fields that changed (a PATCH instead of the whole object), her save can't revert bank_account_id, because it never sends one. It still gets a 412 here, though: the version covers the whole row, and Bob's save moved it. To let edits to unrelated fields both land, version each group of fields that belong together instead of the row. A new bank account and the currency it pays out in share one version, since they're one decision; 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:

	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.

---