postgres-lost-update

Postgres keeps every old version of every row. It still lets one writer quietly overwrite another writer's decision.

This repo lets you watch that happen on your own machine, in about five minutes, and then fix it yourself.

The thing you are about to see

An auction is closing. Two writers reach for the same row at the same moment:

• Alice places a $150 bid.
• A background job wakes up on the deadline, closes the auction, and records the winner as whoever the high bidder was when it looked.

Both read the row first. Both get the same answer: no high bidder yet. Here is the row you end up with:

final row: { status: 'closed', high_bid_cents: 15000, winner_id: null, version: 1 }

Alice's $150 bid is sitting right there. The auction closed with no winner.

Nothing failed. No error, no warning, no rollback. Both writes were accepted, both were perfectly legal, and the database is not corrupt.

Run it

You need Docker and Node 22+. Four steps:

git clone https://github.com/matthewpsbilo/postgres-lost-update
cd postgres-lost-update
npm install
cp .env.example .env      # on Windows PowerShell: copy .env.example .env
docker compose up -d
npm run demo

You should see two passing tests. Both are meant to pass, including the one that shows the bug: proving a bug exists means asserting that the wrong thing happens.

Test	What it shows
1. Postgres never edits a row in place	An UPDATE writes a whole new row version at a new physical address, and leaves the old one behind. You can watch ctid and xmin move.
2. ...and that still does not stop two writers	The race above. The auction closes with no winner.

If port 5433 is already taken on your machine, change it in docker-compose.yml and in .env.

Then fix it yourself

The fix is one condition in a WHERE clause. Rather than read it, add it:

1. In test/exercise.test.ts, change describe.skip( to describe(.
2. In src/exercise.ts, fill in the two TODOs.
3. Run npm run exercise.

When it works, you will see this:

rows changed on first try: 0
final row: { status: 'closed', high_bid_cents: 15000, winner_id: 'alice', version: 2 }

Zero rows is the point. The closing job asked to change a row that no longer existed in the shape it remembered. So nothing was written, the job found out its view was stale, looked again, saw Alice, and closed the auction against what was actually true.

The answer, if you get stuck (or cannot run it)

In src/exercise.ts:

export const guardedClose: BuildCloseUpdate = ({ auctionId, winnerId, versionIRead }) => ({
  text: `UPDATE auctions
            SET status = 'closed', winner_id = $1, version = $2
          WHERE id = $3 AND version = $4`,

  values: [winnerId, versionIRead + 1, auctionId, versionIRead],
})

The whole guard is AND version = $4. It means: change this row only if it is still on the version I read.

Why the version column alone does nothing

Look again at the broken result: version: 1.

The version column did move. Both writers read version 0, both wrote version 1, and the number ended up exactly where it should be. Having the column changed nothing.

A version number is not a guard. The guard is the condition you attach to the write.

Why this happens at all

Postgres hands every statement a photograph of the data, taken the instant you asked. That is what all the row versions are for. From the manual:

"each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data."

The photograph is honest about the past. It cannot tell you what somebody else is about to do a millisecond from now. On the default isolation level, which is Read Committed, a second writer waits for the first to finish and then applies its change on top:

"it will attempt to apply its operation to the updated version of the row."

Apply its operation. Not: check whether the operation still makes sense.

So Postgres versions rows to decide what you are allowed to see. It does not decide who wins. Those are two different jobs, and it only signed up for one of them.

How the race is staged

Real concurrency is not reliable enough to demonstrate anything, so the two writers are stepped through each other deliberately in src/race.ts, using two separate connections. A connection pool is not used, because a pool is free to hand the same connection to both writers, and then there is no race at all.

The demo also checks your server is on Read Committed before it starts. At a stricter isolation level Postgres blocks the lost update itself, so there would be nothing to show.

This has been run 200 times with one outcome each. If you ever see it produce something different, please open an issue: that would be genuinely interesting.

The write-up

This repo is the companion to a blog post: Postgres versions every row. So why did I add a version column?

The post explains why this happens, what all that row versioning is actually for, and why the version column on its own does nothing. It also gives away the answer to the exercise above, so run the exercise first if you want to try it.

Licence

MIT.