Most applications are running on Read Committed isolation. Most engineers assume Serializable. The gap between these two assumptions is where race conditions, double-bookings, and phantom reads live in production — problems that appear intermittently and are nearly impossible to reproduce in testing.

Situation

PostgreSQL supports four isolation levels: Read Uncommitted (aliased to Read Committed in PostgreSQL), Read Committed, Repeatable Read, and Serializable. MySQL InnoDB supports the same four. The ANSI SQL standard defines these levels by which anomalies they prevent.

Most applications use the database default — Read Committed in PostgreSQL and MySQL — without explicitly choosing it. Most engineers do not know what anomalies Read Committed allows.

The Problem

An application manages event ticket inventory. Two users request the last ticket simultaneously. The application reads the remaining count (1), decides both can proceed, and issues two inserts. Both succeed. The event is now oversold. This is a lost update anomaly — and it happens at Read Committed because the two transactions each read a consistent snapshot of the row before either write committed.

Read Committed is not wrong. It is the right choice for most workloads. But using it for inventory, financial balances, or any counter where two concurrent writers can conflict requires explicit application-level locking to compensate.

What does each isolation level actually prevent, and how do you know which one your application needs?

The Isolation Levels

Read Committed (PostgreSQL default): each statement in a transaction reads the latest committed data at the moment that statement executes. A second SELECT in the same transaction may return different rows than the first if another transaction committed between them. Prevents: dirty reads. Does NOT prevent: non-repeatable reads, phantom reads, lost updates.

Repeatable Read: each statement in a transaction reads the same snapshot established at the beginning of the transaction. A second SELECT will return the same rows as the first, even if another transaction committed between them. Prevents: non-repeatable reads. Does NOT prevent: phantom reads (in standard SQL; PostgreSQL’s implementation also prevents most phantoms). Does NOT prevent: lost updates if two transactions modify the same row concurrently.

Serializable (SSI): transactions execute as if they ran one at a time, in some serial order. If two transactions have read/write dependencies that would cause an anomaly in any serial order, PostgreSQL aborts one of them with a serialization failure. Prevents: all standard anomalies including phantoms and write skew. Cost: serialization failures require application retry logic.

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Check current transaction isolation
SHOW transaction_isolation;

-- Ticket inventory pattern with explicit locking at Read Committed:
BEGIN;
SELECT quantity FROM tickets WHERE event_id = 42 FOR UPDATE;
-- Only one transaction proceeds past this point concurrently
UPDATE tickets SET quantity = quantity - 1 WHERE event_id = 42 AND quantity > 0;
COMMIT;

SELECT ... FOR UPDATE adds an explicit row lock — it is the correct pattern for counter decrement operations at Read Committed isolation, because it prevents the lost update anomaly that Read Committed otherwise allows.

In Practice

PostgreSQL’s documented behavior for Serializable Snapshot Isolation (SSI) uses predicate locking and dependency tracking to detect serialization conflicts at commit time rather than at statement time. This means serialization failures appear as commit errors, not as blocked statements — the application must catch ERROR: could not serialize access and retry the transaction.

The documented anomalies that SSI prevents but Repeatable Read does not: write skew (two transactions each read a condition that the other’s write will violate) and phantom reads that involve write dependencies. The canonical write skew example: two doctors each check whether at least one doctor is on call, find yes, and both go off call — leaving no coverage. At Repeatable Read, both succeed. At Serializable, one is aborted.

Where It Breaks

AnomalyIsolation level neededPattern
Lost update (concurrent increment/decrement)Read Committed + FOR UPDATEExplicit locking on the row being modified
Non-repeatable read (read same row twice, get different value)Repeatable ReadLong read transactions that must see consistent data
Write skew (two transactions each invalidate the other’s assumption)SerializableDoctor on-call, seat booking, any “check then act” pattern
Phantom read (new rows appear in range query)Repeatable Read (PostgreSQL)Reporting queries with range conditions

What to Do Next

  • Problem: Applications running at Read Committed default isolation are exposed to lost updates and non-repeatable reads that appear as intermittent data inconsistencies under concurrent load.
  • Solution: Identify the data entities where concurrent writes conflict (counters, balances, inventory, slots) and add SELECT ... FOR UPDATE or switch to Serializable isolation with retry logic.
  • Proof: After adding FOR UPDATE to your inventory decrement pattern, the oversell scenario cannot occur — the second transaction blocks until the first commits, then re-evaluates the quantity condition.
  • Action: Find the one place in your application where two concurrent users can write to the same row without coordination — that is your lost update risk — and verify whether you have explicit locking or rely on application-level checks that the database does not enforce.