0
Challenge · 31Correct & Fast

Isolation, the lost update & deadlocks

Dataused in ~65% of apps

Two fast taps on the same like button — a real double-click, or a flaky network causing a client retry — can both read `like_count = 5`, both compute 6, and both write 6. One like vanishes into thin air, and Postgres raises no error at all: two statements succeeded, and the count is still wrong. This is the single most-asked "why is my counter wrong" bug in production apps, and Postgres's default settings do not save you from it by accident.

Core idea. Postgres's default isolation level, Read Committed, guarantees you never see another transaction's uncommitted data — but it does NOT guarantee that a read-then-write sequence spanning two statements stays safe from a concurrent write in between. That gap is the lost update. The fix is never a stronger isolation level by default; it's making the read-modify-write atomic (a single UPDATE, an explicit row lock, or a uniqueness constraint that makes the race impossible to express in the first place).
Playground, this isn't a screenshot
t = 0
Transaction T1 (alice's tap)
SELECT like_count FROM posts WHERE id=1;
→ reads 5
Transaction T2 (a second fast tap)
SELECT like_count FROM posts WHERE id=1;
→ reads 5
Both taps land at nearly the same instant. Both read the SAME starting value: 5. Neither has written anything, so neither blocks the other.
A single Postgres connection can only run one statement at a time, so this sandbox cannot literally overlap two transactions for you — the interleaving above is exactly what Postgres does with two real connections, hand-stepped so you can see both lanes at once.
↑ play a little first, then dig in