Isolation Levels

When you are the only one using the database, life is simple - each query sees what the previous one left behind. The moment multiple transactions run concurrently, their reads and writes start to overlap in time, and things get complicated.

Isolation levels define the rules for what a transaction is allowed to see about the work of other concurrent transactions. Weaker isolation means more concurrency but also more ways for reads to go wrong. Stronger isolation prevents those problems but requires more coordination - which can mean transactions waiting on each other, or failing and retrying.

The three classic anomalies

Dirty read: Transaction A reads a row that transaction B has modified but not yet committed. If B then rolls back, A has read data that never officially existed. You acted on a ghost.

Non-repeatable read: Transaction A reads a row. Transaction B modifies and commits that row. A reads the same row again and gets a different value. The surprising part is that both reads happened inside the same transaction - and the data still changed underneath you.

Phantom read: Transaction A queries all rows matching a condition. Transaction B inserts a new matching row and commits. A runs the same query again and gets a different number of rows. New rows appeared between the two reads, even though A never closed its transaction.

The four isolation levels

Each step up the table prevents one more class of anomaly. Each step also imposes more coordination overhead - transactions may block each other, or detect conflicts and have to retry.

How PostgreSQL implements each level

PostgreSQL does not implement READ UNCOMMITTED - it quietly upgrades it to READ COMMITTED. For the levels it does support:

READ COMMITTED (the default): Each statement sees a fresh snapshot of committed data as of when that statement began. Two statements in the same transaction can see different data if another transaction committed between them. This trips people up the first time they see it, but it is the right default for most workloads.

REPEATABLE READ: The whole transaction sees a single snapshot taken at BEGIN time. Other transactions can commit freely while yours is running - you just will not see their changes. If two transactions try to modify the same rows, one will fail with a serialization error and must retry.

SERIALIZABLE: The strongest guarantee. PostgreSQL uses Serializable Snapshot Isolation (SSI) to detect write conflicts between concurrent transactions and abort whichever one would create an anomaly. Any transaction that commits successfully behaves as if it ran in some sequential order, one after another.

Setting the isolation level

BEGIN ISOLATION LEVEL READ COMMITTED;  -- the default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

When to use a higher isolation level

Most application code runs correctly at READ COMMITTED. You need a higher level when:

• REPEATABLE READ: You read the same data more than once inside a transaction and need a consistent view. A report that joins several tables and must reflect a single consistent point in time is the textbook case.

• SERIALIZABLE: Your logic reads data to decide what to write - for example, check current inventory, then insert an order only if stock is sufficient. At lower isolation levels another transaction can decrement inventory between your read and your write, and neither of you will notice. SERIALIZABLE detects that conflict and surfaces an error.

When you go SERIALIZABLE, your application must handle ERROR: could not serialize access due to concurrent update by retrying the transaction. This is not optional - it is part of the contract.

Practical guidance

Do not reach for a higher isolation level the moment you spot a race condition. Diagnose first. Figure out whether you are dealing with a dirty read, a non-repeatable read, or a phantom read - then pick the level that prevents exactly that.

The vast majority of web applications run fine at READ COMMITTED with short transactions and carefully ordered writes. Serializable is a powerful tool, but it comes with real overhead and a retry requirement that has to be wired into your application code. Use it when you have a specific, clearly understood need - not as a comfort blanket.