Transaction Isolation Levels

Isolation level controls how much one transaction can see of another transaction's in-progress changes. Stricter isolation prevents more anomalies, at the cost of more blocking/retries under heavy concurrency.

The Anomalies (What Can Go Wrong)

Anomaly	Description
Dirty read	Reading another transaction's uncommitted changes
Non-repeatable read	Re-reading the same row twice in one transaction gives different results, because another transaction committed a change in between
Phantom read	Re-running the same query twice gives a different set of rows, because another transaction inserted/deleted matching rows

PostgreSQL never allows dirty reads, at any isolation level.

Setting the Isolation Level

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... statements ...
COMMIT;

Read Committed (the Default)

Each statement sees only data committed before that statement began — so two SELECTs in the same transaction can see different data if another transaction commits in between.

BEGIN; -- defaults to READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;
-- (another transaction commits a change here)
SELECT balance FROM accounts WHERE id = 1; -- may return a DIFFERENT value
COMMIT;

Repeatable Read

The entire transaction sees a consistent snapshot taken at its first statement — re-reading the same row always gives the same result for the rest of that transaction.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- (another transaction commits a change here)
SELECT balance FROM accounts WHERE id = 1; -- SAME value as before
COMMIT;

If a conflicting write happens concurrently, PostgreSQL raises a serialization failure rather than silently allowing it — your application needs to catch this and retry the transaction.

Serializable — The Strictest Level

Guarantees the result is as if transactions ran one after another, with no overlap at all — the strongest guarantee, but the most retries under contention.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;

Choosing a Level

Level	Prevents	Use When
Read Committed	Dirty reads	Default — fine for most everyday queries
Repeatable Read	+ Non-repeatable reads	Reports/calculations that must see one consistent snapshot
Serializable	+ Phantom reads, all anomalies	Financial transfers, inventory counts — correctness above all

Common Errors & Troubleshooting

• ERROR: could not serialize access due to concurrent update — expected behavior at REPEATABLE READ/SERIALIZABLE when a real conflict occurred; the fix is to retry the whole transaction from the start, not just the failed statement.
• Assuming REPEATABLE READ prevents all anomalies — it stops non-repeatable reads within one transaction, but phantom reads can still occur in standard PostgreSQL's implementation unless you go all the way to SERIALIZABLE.
• Setting SERIALIZABLE everywhere "to be safe" and seeing constant retries — the strictest level trades throughput for correctness; reserve it for the specific operations that truly need it (like financial transfers), not as a blanket default.

Key Takeaways

• PostgreSQL never allows dirty reads, even at the default level.
• Read Committed is the default and is fine for the majority of application queries.
• Stricter levels can raise serialization errors that your application must be ready to catch and retry.