Transaction isolation levels in practice

What it is

The SQL standard defines isolation levels by the anomalies they forbid: dirty reads, non-repeatable reads, phantom reads and serialisation anomalies. PostgreSQL implements read committed (the default: each statement sees data committed before it started), repeatable read (the transaction sees a snapshot taken at its first statement) and serializable (transactions behave as if executed one after another, with serialisation failures reported as errors that the application must retry).

Why it matters

Most application bugs called "race conditions" are two transactions reading the same row, computing on it and writing back under read committed. The fix is a deliberate choice: row locks (SELECT … FOR UPDATE), atomic statements (UPDATE … SET count = count + 1), or a stricter isolation level with retry logic.

How to apply

• Keep the default level for simple reads and single-statement writes.
• Use SELECT … FOR UPDATE when a read-modify-write sequence must be atomic (this wiki's article updates do that with ETag checks inside the locked transaction).
• Use serializable for multi-row invariants that cannot be expressed as constraints, and wrap the transaction in a bounded retry loop.
• Keep transactions short; long transactions hold snapshots and locks.

Pitfalls

Repeatable read does not prevent write skew between different rows. Serialization failures are normal, not bugs, but only if the application retries. Application-level caches can reintroduce stale reads that the database prevented.