Curious Pandas
Explorer0 XP

Transactions and the lost update

Two clerks sell the same tee at the same moment and the stock only drops by one. Find out why BEGIN and COMMIT alone don't fix it, and the three fixes that do - each checked against a real PostgreSQL.

Runs in your browserOn your machine
18 min+40 XP for finishing

A transaction groups statements so they succeed or fail together. Move money between two accounts inside one, and you never see the money leave one account without arriving in the other. That’s the promise most people know.

There’s a quieter problem a transaction doesn’t solve on its own: two people changing the same row at the same time.

The lost update

The shop has 10 panda tees. Two clerks each sell one, half a second apart. Each clerk’s app does the obvious thing: read the stock, subtract one, and write the result back.

Visual guideTwo sales, one decrement
Two sales, one decrementClerk A reads a stock of 10. Clerk B also reads 10 before A writes. A writes 9. B, working from its own read, also writes 9. Two tees were sold, but the stock only dropped by one: A's update was lost.A reads 10SELECT stockB reads 10before A writesA writes 910 - 1B writes 9its own 10 - 1stock: 9two sold, one lostminus 1minus 1overwrittenlast write
  1. Clerk A reads the stock: 10.
  2. Half a second later clerk B reads it too, before A has written anything. B also sees 10.
  3. A works out 10 - 1 and writes 9.
  4. B works out 10 - 1 from its own read and also writes 9, overwriting A's change. Two tees sold, and the stock dropped by one.

Does BEGIN … COMMIT fix it?

Run it and see. Every strategy below replays what PostgreSQL 17.6 actually did with two real psql sessions, recorded for this lesson, including the moment clerk B sat waiting.

The last panda teestwo sessions, replayed from PostgreSQL 17.6

The table holds one row: panda-tee with stock 10. Clerks A and B each sell one tee, half a second apart.

Clerk AClerk B

    Why the transaction alone didn’t help

    PostgreSQL’s default isolation level is Read Committed: each statement sees the data committed before it began. Clerk B’s UPDATE found the row locked by A, so it waited for A to commit, exactly as the documentation describes. Then it carried on and wrote its 9. The database protected the row. It couldn’t know that B’s 9 was based on a number that was no longer true.

    The three fixes all stop B from acting on a stale read:

    Fix What changes Cost
    SELECT ... FOR UPDATE B’s read waits until A commits, so B reads 9 Clerks queue for the row
    SET stock = stock - 1 The database does the arithmetic on the current value; no stale read exists Only works when the change is a simple calculation
    REPEATABLE READ B’s write is refused with a serialization error The app must catch the error and retry
    Check yourself+10 XP

    Both clerks run BEGIN; SELECT stock; UPDATE stock = (what they read - 1); COMMIT at READ COMMITTED. What happens?

    Check yourself+10 XP

    Which change removes the lost update with no extra locking statement in the app?

    Do it for real · your machine+50 XP bonus

    Everything above ran in your browser. Now do it on a real cluster withthe tilt platform. You need Docker, kind and Tilt, about 15 GB of free disk and 8 GB of spare RAM. Setup guide · Can my machine run it?

    The gitops profile runs a CloudNativePG cluster called demo-pg. Open two terminals and connect both:

    kubectl -n cnpg-system exec -it demo-pg-1 -- psql demo

    Then, in each terminal in turn (A first, then B, then back to A):

    CREATE TABLE IF NOT EXISTS items (sku text PRIMARY KEY, stock int);
INSERT INTO items VALUES ('panda-tee', 10) ON CONFLICT (sku) DO UPDATE SET stock = 10;  -- terminal A only
BEGIN;
SELECT stock FROM items WHERE sku = 'panda-tee';           -- A, then B: both see 10
UPDATE items SET stock = 9 WHERE sku = 'panda-tee';        -- A, then B: B hangs
COMMIT;                                                    -- in A: watch B's UPDATE finish
COMMIT;                                                    -- in B
SELECT stock FROM items WHERE sku = 'panda-tee';           -- 9

    Repeat with SELECT ... FOR UPDATE, and then with BEGIN ISOLATION LEVEL REPEATABLE READ;, and compare.

    Self-reported. We can't see your machine, and we don't want to. This is a bonus, never required.

    Sources

    Finished reading?

    Missions and quizzes above earn their own XP. This marks the lesson done.