Anomalies when many transactions read and write together, and how to fix each one Intermediate

Problem

A service checks “at least one doctor remains on call” before approving time off. Two doctors, A and B, request time off at the same time. Each request reads that the other is still on call, then updates its own row to on_call = false.

Under Repeatable Read both commit, neither touched the same row, and in the end nobody is on call.

“Race condition” is too broad to pick a fix. An anomaly is the specific name of each kind of interleaving, and each kind has its own fix: change the SQL, add a constraint, lock, or raise the isolation level.

Summary: which anomaly, which level, which fix

Anomaly	Read Committed	Repeatable Read	Serializable	Practical fix
Dirty read	No	No	No	Not needed
Non-repeatable read	Yes	No	No	One statement, or REPEATABLE READ READ ONLY for reports
Phantom read	Yes	No (stricter than the SQL standard)	No	Same as above; to block writes over a set of rows, use EXCLUDE/UNIQUE
Read skew	Yes (if split into statements)	No	No	One SELECT fetching both rows, or a shared snapshot
Lost update (read, then write a constant computed in the app)	Yes	40001 error	40001 error	Atomic SQL SET x = x + 10, FOR UPDATE, version column
Write skew	Yes	Yes	40001 for one side	EXCLUDE/UNIQUE, lock the parent row, Serializable + retry
Read-only transaction anomaly	Yes	Yes	No	Serializable (rare; see the isolation docs)

The Repeatable Read and Serializable columns for lost update match the PostgreSQL docs: a transaction may not modify or lock a row that another transaction changed after its snapshot, so PostgreSQL returns could not serialize access due to concurrent update (40001), and the app retries the whole transaction.

Three read anomalies, step by step

The tables below run in time order from top to bottom. Read Committed unless stated otherwise.

Non-repeatable read: same row, two reads, two results.

T1 (report)	T2 (writer)
SELECT balance (reads 100)
	UPDATE ... SET balance = 70; COMMIT
SELECT balance (reads 70)

Phantom: same filter condition, the set of rows changes.

T1	T2
SELECT count(*) WHERE status = 'pending' (reads 2)
	INSERT a pending job, COMMIT
SELECT count(*) WHERE status = 'pending' (reads 3)

Read skew: combining two snapshots into a number that never existed. A = 100, B = 50, the real total is always 150.

T1 (report)	T2 (moves 30 from A to B)
SELECT balance WHERE id = A (reads 100)
	UPDATE A = 70; UPDATE B = 80; COMMIT
SELECT balance WHERE id = B (reads 80)

The report adds up to 180. T2 is still atomic; the bug is the report stitching two statements together. All three are fixed the same way: put it into one statement, or BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY so every read uses one snapshot.

Lost update: writing a constant computed from an old value

Lost update: two requests each add 10, the result is 110 instead of 120

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

A reads balance = 100 into an application variable.

Under Read Committed, this outcome always happens with the schedule above: the second statement waits for the first to commit, then assigns exactly the constant 110 the app computed. PostgreSQL only serializes the writes; it does not know that 110 is stale.

Three fixes, from cheapest to most expensive:

1. Atomic SQL: UPDATE account SET balance = balance + 10 WHERE id = 1. Under Read Committed, the second statement waits, then applies the addition to the new version, giving 120. This is the default to use.
2. SELECT ... FOR UPDATE before reading, in the same transaction (see row-level locks).
3. Version column (optimistic): UPDATE ... SET balance = 110, version = version + 1 WHERE id = 1 AND version = 7; a row count of 0 means someone changed it first, so re-read and retry.

Write skew: two transactions, two rows, one invariant

Write skew: two doctors both leave the shift under Repeatable Read

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

A reads: 2 people are on call, so A may take time off.

Unlike lost update, the two transactions write two different rows, so there is no write-write conflict for PostgreSQL to catch. Repeatable Read’s stable snapshot does not help: both decisions are correct for their own snapshots.

Run serially, this outcome is impossible, because the second doctor would see that the first had already left.

Choose a fix in this order of preference:

Fix	When to use	Example
UNIQUE / EXCLUDE constraint	The invariant can be expressed over rows	No duplicate usernames; no two overlapping bookings for the same room
Lock a parent or coordinating row	There is a shared entity for the rows	SELECT ... FROM shifts WHERE id = 7 FOR UPDATE, then check and modify
Serializable + retry 40001	Complex invariant, aborts are acceptable	The “at least one person on call” rule
Advisory lock on a business key	No parent row to lock	pg_advisory_xact_lock(hashtext('shift:7'))

EXCLUDE for “no double-booked rooms” (needs the btree_gist extension to use = in GiST):

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id int NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

The second overlapping INSERT is rejected with SQLSTATE 23P01 (exclusion_violation) at every isolation level; no Serializable needed. Likewise, “check the username does not exist, then insert” should use UNIQUE (error 23505) instead of SELECT then INSERT.

With Serializable, in the schedule above the later committer (B) is usually the one receiving 40001, but the chosen victim can differ in other schedules. The retry wrapper must rerun the whole transaction, including the part that reads the condition (see isolation levels).

Real-world example

Meeting-room booking and ticket sales: three bugs, three fixes

A meeting-room booking system: 40 rooms, 300 bookings a day. If the code says “SELECT to see whether the room is free, then INSERT”, overlapping bookings slip through at peak hours: this is a phantom leading to write skew over a set of rows.

The EXCLUDE above fixes it for good, no retry needed.

The same system has an inventory table: the code reads stock = 5, subtracts 1 in the app, and writes stock = 4. Two concurrent requests leave stock at 4 after selling 2 tickets (lost update, oversell).

Fix it with UPDATE inventory SET stock = stock - 1 WHERE id = 9 AND stock > 0 and check the row count.

Lesson: fix with the cheapest tool that can express the invariant, and save Serializable for the rest.

The numbers in the example are an illustrative scenario, not measurements.

Try it locally

Create the demo data from the repo root:

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-anomalies/setup.sql

Open two psql terminals (docker compose -f examples/docker-compose.yml exec postgres psql -U hub -d hub) and run in exactly this order, pausing both after the SELECT:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT name, on_call FROM anomaly_doctors ORDER BY name;

-- Session B (another terminal)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT name, on_call FROM anomaly_doctors ORDER BY name;

-- Back to A
UPDATE anomaly_doctors SET on_call = false WHERE name = 'A';
COMMIT;

-- Back to B
UPDATE anomaly_doctors SET on_call = false WHERE name = 'B';
COMMIT;
SELECT name, on_call FROM anomaly_doctors ORDER BY name;

Result under Repeatable Read: both A and B are false. Rerun setup.sql, change both BEGINs to BEGIN ISOLATION LEVEL SERIALIZABLE;, and keep the same order: one side gets 40001.

When testing in code, you need two real connections, a synchronization point so both sides read before writing, and assertions on the final state. If the ORM opens a separate transaction for the write, the two-session simulation no longer describes your code.

Trade-offs

Identifying the right anomaly

• Pick a fix that fits the bug: atomic SQL for lost update, one snapshot for read skew, a constraint for simple invariants.
• Test the order of events instead of waiting for the race to appear at random.
• Distinguish an app writing stale values from the database’s isolation behavior.

The cost of handling it

• Stronger isolation increases retries and the cost of monitoring conflicts.
• Locks protect the invariant but can cause waits and deadlocks if you do not lock in a consistent order.
• A SELECT that is correct today can be split into several statements by an ORM or a refactor, creating new read skew.

Common mistakes

Common pitfall

“Repeatable Read prevents phantoms, so it also prevents write skew.” A fixed snapshot keeps the set of rows the transaction sees, but two transactions can still modify two different rows based on the same stale condition. Use a constraint, lock the parent row, or use Serializable.

Common pitfall

“UPDATE takes a row lock, so lost updates are impossible.” The lock only serializes the right to write. If the new value is computed in the app from an old snapshot and the SQL assigns a constant, the later statement still overwrites.

Move the computation into the UPDATE, or lock before reading. Under Repeatable Read, PostgreSQL blocks it with 40001, but you still have to retry.

Code review checklist against races

1. No “read into the app, compute, then SET x = constant”: move the computation into the UPDATE (SET balance = balance + 10) and check the row count.
2. No “SELECT to check, then INSERT” for uniqueness or overlap: use UNIQUE or EXCLUDE.
3. Invariants spanning multiple rows (like “at least one person on call”): lock the parent row, use an advisory lock, or Serializable + retry; Repeatable Read is not enough.
4. Reports combining several numbers: one statement, or REPEATABLE READ READ ONLY to share a snapshot.
5. The 40001 retry wrapper reruns the whole transaction, including the part that reads the condition.
6. Test with two real connections, with a synchronization point so both sides read before writing, and assert on the final state.

Summary

Key takeaways

• Dirty reads do not happen in PostgreSQL; non-repeatable reads, phantoms, and read skew only happen under Read Committed.
• A lost update under Read Committed is guaranteed when the app writes a precomputed constant; Repeatable Read and Serializable return 40001.
• Write skew still happens under Repeatable Read; fix it with a constraint, a parent-row lock, or Serializable + retry.
• Choose the cheapest fix that expresses the invariant; verify with two real connections and assertions on the final state.

Self-check:

1. How do two requests both running SET balance = 110 from a value of 100 differ from two SET balance = balance + 10 under Read Committed?
2. Why does a report that reads A before the commit and B after it produce a total that never existed?
3. In the doctor example, does each doctor locking only their own row prevent write skew?

References

• Transaction Isolation — PostgreSQL 17
• Serialization Failure Handling — PostgreSQL 17
• Constraints — PostgreSQL 17
• Explicit Locking — PostgreSQL 17