What isolation levels does PostgreSQL provide, and how do they behave?

Summary

READ COMMITTED (the default: each statement sees data committed before it started), REPEATABLE READ (snapshot isolation: the whole transaction sees one snapshot and concurrent updates of the same row cause a serialization error), and SERIALIZABLE (Serializable Snapshot Isolation: detects dangerous read/write dependency patterns and aborts one transaction so the result equals some serial order). READ UNCOMMITTED behaves like READ COMMITTED.

Longer Answer

In READ COMMITTED, an UPDATE that finds a row changed by a concurrent committed transaction re-evaluates its WHERE clause on the new version and continues, which is convenient but allows anomalies such as lost updates in read-modify-write code.

REPEATABLE READ prevents those by raising could not serialize access due to concurrent update. SERIALIZABLE also prevents write skew (two doctors both going off call because each saw the other on call). Both require the application to retry failed transactions; SERIALIZABLE is a valid, practical choice in PostgreSQL if you build the retry loop.

Key Points

• Default READ COMMITTED.
• REPEATABLE READ = snapshot isolation.
• SERIALIZABLE = SSI, prevents write skew.
• Higher levels need retry loops.

Deep dive: Transactions, Locking & Concurrency in Code

What interviewers look for

Knowing that PostgreSQL's SERIALIZABLE is SSI (optimistic, not lock-based) and that applications must retry.

Follow-up questions

• Give an example of write skew.
• Which SQLSTATE do you retry? (40001)