When two requests read a row, change it in application code, and
write it back, the second write silently erases the first. That's
last-write-wins, and the naive endpoint picks it for you. The fix
depends on the write: a delta goes to the database as one
conditional UPDATE, logic that needs a read first takes a row lock
(pessimistic), and a human edit made minutes after the read gets a
version check (optimistic) that turns the collision into a conflict
someone resolves.
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.
Here's a withdrawal endpoint. It reads the balance, checks there's enough, subtracts, writes the result back.
Run it once and it's right. Run it twice at the same moment and both requests read before either one writes:
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.
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?
The simplest fix is to stop reading first. Send the change, not the result, and put the check in the same statement:
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,
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 , 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.
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:
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 error. The fix is to always lock in the same order, whatever the direction of the transfer:
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:
HTTP already has the vocabulary for this. The GET returns the
version as an ETag, the client echoes it in
,
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.
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.
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:
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.
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 , 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.
The question to ask of every write isn't "could this race?" (it can) but what the write actually is:
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.