Isolation Levels

When a single user runs queries against a database, everything is straightforward: each query sees the state left by the previous one. The difficulty arises when multiple transactions run at the same time and their reads and writes overlap in time.

Isolation levels define the rules for what a transaction is allowed to see about the work of other concurrent transactions. Weaker isolation allows more concurrency but can expose anomalies. Stronger isolation prevents anomalies but requires more coordination between transactions, which can mean waiting or 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.

Non-repeatable read: Transaction A reads a row. Transaction B modifies and commits that row. A reads the same row again and sees a different value. The row changed between A's two reads.

Phantom read: Transaction A queries all rows matching a condition. Transaction B inserts a new row that matches the condition and commits. A runs the same query again and gets a different number of rows. New rows "appeared" between A's two queries.

The four isolation levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Higher levels prevent more anomalies. They also impose more coordination overhead: transactions may need to wait for locks, or detect conflicts and retry.

How PostgreSQL implements each level

PostgreSQL does not implement READ UNCOMMITTED (it is quietly treated as READ COMMITTED). For the levels it does support:

READ COMMITTED (the default): Each statement within the transaction sees a fresh snapshot of only the committed data at the moment that statement begins. If another transaction commits between two statements in your transaction, your second statement sees the new data. This is appropriate for most workloads.

REPEATABLE READ: The entire transaction sees a snapshot of the database as of when the transaction started. Other transactions can commit changes during your transaction, but you will not see them. This prevents non-repeatable reads. If two transactions try to modify the same rows, one of them will fail with a serialization error and must retry.

SERIALIZABLE: The strongest level. PostgreSQL implements this with Serializable Snapshot Isolation (SSI), which detects write conflicts between transactions and aborts the transaction that would create an anomaly. All successfully committed transactions appear as if they executed in some sequential order.

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. The cases where you need more are:

• REPEATABLE READ: You need to read the same data multiple times within a transaction and require it to be consistent. A reporting transaction that reads several tables and needs them to reflect the same moment in time is a good example.

• SERIALIZABLE: You are implementing logic that depends on the results of reads to determine what to write. For example: read the current inventory count, then write a new order only if inventory is sufficient. At SERIALIZABLE, the database detects if another transaction modified inventory concurrently and surfaces a conflict error.

When you use SERIALIZABLE, your application must handle ERROR: could not serialize access due to concurrent update by retrying the transaction. This is not optional.

Practical guidance

Do not reach for a higher isolation level as a first response to a race condition you do not fully understand. Diagnose the anomaly. Identify whether it is a dirty read, a non-repeatable read, or a phantom read. Then choose the level that prevents it.

For the vast majority of web application workloads, READ COMMITTED with short transactions and carefully ordered writes is sufficient. Serializable transactions are powerful but come with real complexity. Use them when you have a specific, understood need.