PostgreSQL transaction isolation levels explained with examples
READ COMMITTED, REPEATABLE READ, SERIALIZABLE, dirty read, non-repeatable read, phantom read, serialization failure, SET TRANSACTION ISOLATION LEVEL
Isolation Levels
Isolation level controls what a transaction can see when concurrent transactions are running. Higher isolation means fewer anomalies but more contention.
Set isolation level
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- your queries
COMMIT;READ COMMITTED (default)
Each statement sees data committed before that statement began. You can get different values for the same row in two statements within one transaction. Safe for most OLTP workloads.
REPEATABLE READ
Each transaction sees a snapshot of data as of its start. The same SELECT returns the same rows throughout the transaction. Prevents non-repeatable reads. Phantom reads are also prevented in PostgreSQL (unlike the SQL standard).
SERIALIZABLE
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL detects conflicting concurrent transactions
-- and may raise:
-- ERROR: could not serialize access due to concurrent update
-- Your application must retry on this error
COMMIT;SERIALIZABLE gives the strongest guarantees — execution is equivalent to some serial order. It uses Serializable Snapshot Isolation (SSI) in PostgreSQL, which is more efficient than locking-based approaches but requires retry logic in application code.