Isolation levels control how transaction anomalies (dirty read, non-repeatable read, phantom) appear under concurrency. Postgres default is READ COMMITTED.
Setting isolation
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- other session may commit changes; repeatable read sees snapshot
COMMIT;Practice: Run on a local Postgres instance you own. Avoid changing production cluster settings.
Levels overview
- READ COMMITTED — default; each statement sees committed data as of statement start
- REPEATABLE READ — snapshot for whole transaction; phantoms prevented for indexes
- SERIALIZABLE — strictest; may raise serialization_failure—retry in apps
Handling serialization failures
ORMs and APIs should retry on SQLSTATE 40001. Design short transactions to reduce contention.
Important interview questions and answers
- Q: Postgres default?
A: READ COMMITTED. - Q: Serializable trade-off?
A: Strongest guarantees with potential retry errors under heavy write contention.
Self-check
- Which isolation level is Postgres default?
- Why retry on serialization_failure?
Tip: Retry apps on serialization_failure (SQLSTATE 40001).
Interview prep
- Postgres default?
READ COMMITTED.
- Serializable failure?
Retry transaction on serialization_failure.