Concurrency problems: lost updates and friends

Part of the free LogicWiz SQL & Databases course, Module 13: Transactions and concurrency. The queries and practice run in the app on real Postgres.

Tatkal opens at 10:00. Aarav and Meher both see berth 72 as free, both click Book in the same second, and both get "Confirmed". The train has one berth 72. What went wrong, and in which line of SQL?

You'll learn to

Name the four classic problems: dirty read, non-repeatable read, phantom read, lost update
Spot a lost update in read-then-write code
Fix it with a relative UPDATE or a guarded UPDATE

Learn

The lost update: two UPI debits, one survives

Session A	Session B
BEGIN;
SELECT balance FROM accounts WHERE accountid = 1;
	BEGIN;
	SELECT balance FROM accounts WHERE accountid = 1;
-- app: 25000 - 1000 = 24000 UPDATE accounts SET balance = 24000 WHERE accountid = 1;
COMMIT;
	-- app: 25000 - 500 = 24500 UPDATE accounts SET balance = 24500 WHERE accountid = 1;
	COMMIT;
SELECT balance FROM accounts WHERE accountid = 1;

The fix: let the database do the maths

Session A	Session B
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE accountid = 1;
	BEGIN;
	UPDATE accounts SET balance = balance - 500 WHERE accountid = 1;
COMMIT;
	COMMIT;
SELECT balance FROM accounts WHERE accountid = 1;

Both debits, run one after the other

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SELECT balance FROM accounts WHERE account_id = 1;

With relative updates the result is the same whichever order the two run in: 23500.00. Correct no matter how they interleave.

A guarded write: no double Tatkal booking

UPDATE seats
SET booked_by = 'Aarav'
WHERE coach = 'B1'
  AND seat_no = 72
  AND booked_by IS NULL;
-- Meher clicks a moment later
UPDATE seats
SET booked_by = 'Meher'
WHERE coach = 'B1'
  AND seat_no = 72
  AND booked_by IS NULL;
SELECT coach, seat_no, booked_by FROM seats ORDER BY coach, seat_no;

The first UPDATE says 1 row affected, the second 0 rows: the berth was no longer free. Run at the same moment, the second would wait for the first's row lock, re-check booked_by IS NULL on the new row, and still update 0 rows.

Dirty read: Postgres never allows it

Session A	Session B
BEGIN;
UPDATE accounts SET balance = 0 WHERE accountid = 3;
	BEGIN ISOLATION LEVEL READ UNCOMMITTED;
	SELECT balance FROM accounts WHERE accountid = 3;
ROLLBACK;

Non-repeatable read and phantom read

Session A	Session B
BEGIN;
SELECT balance FROM accounts WHERE accountid = 1;
SELECT COUNT() FROM upitxns WHERE fromacc = 1;
	UPDATE accounts SET balance = balance - 5000 WHERE accountid = 1;
	INSERT INTO upitxns VALUES (21, 1, 7, 45.00, '2025-08-09 08:50', 'success', 'chai');
SELECT balance FROM accounts WHERE accountid = 1;
SELECT COUNT() FROM upitxns WHERE fromacc = 1;
COMMIT;

The four problems

Problem	What happens	Real-life damage
Dirty read	Read data another transaction hasn't committed	Show a balance that gets rolled back
Non-repeatable read	Read the same row twice, get different values	A statement where the opening and closing balance don't match
Phantom read	Run the same WHERE twice, a row appears or disappears	A report whose count and list disagree
Lost update	Two read-modify-writes; the second overwrites the first	Two Tatkal bookings for one berth; a missing UPI debit

The first three are about reading; isolation levels (next lesson) decide which ones can happen. The lost update is about writing and is fixed by writing the update well or by locking.

The classic lost-update recipe is in application code: SELECT balance, compute new = balance - amount in Java/Python, then UPDATE … SET balance = new. Between the SELECT and the UPDATE someone else can change the row. Write SET balance = balance - amount, or add a guard like WHERE booked_by IS NULL, so the check and the change happen in one statement.

Exams draw a schedule (T1 reads A, T2 writes A, …) and ask which problem it shows. Dirty read = W-R conflict on uncommitted data (also called temporary update). Lost update = W-W conflict. Non-repeatable read = R-W then read again. Phantom = a new row matching an earlier search. Older books call the lost update the "lost update problem" and the inconsistent report the "incorrect summary problem".

In one line

Dirty read, non-repeatable read, phantom and lost update; Postgres never allows dirty reads, and relative or guarded UPDATEs stop lost updates.

Interview corner

What is a lost update and how do you prevent it?

Two transactions read the same value, each computes a new one, and the second write overwrites the first. Prevent it by making the database do the maths (SET stock = stock - 1), by guarding the write (WHERE booked_by IS NULL), by locking the row first (SELECT … FOR UPDATE), or with optimistic locking using a version column.

Difference between a non-repeatable read and a phantom read?

A non-repeatable read is the same row returning a different value the second time (someone updated it). A phantom is the same query returning a different set of rows (someone inserted or deleted a matching row).

Can you get a dirty read in Postgres?

No. Postgres accepts READ UNCOMMITTED but runs it as READ COMMITTED, so a session only ever sees committed data (plus its own changes).