MVCC and Isolation Levels
PostgreSQL uses multiversion concurrency control (MVCC) so statements can read consistent row versions while concurrent transactions modify data. Readers and writers often avoid blocking each other, though locks and conflicts still occur. Isolation levels determine which concurrency anomalies may be observed.
READ COMMITTED is PostgreSQL's default and gives each statement a snapshot as of that statement's start. REPEATABLE READ uses a stable transaction snapshot but can still require retry handling in some scenarios. SERIALIZABLE provides stronger guarantees by detecting executions that cannot be serialized, and transactions may need to be retried after serialization failures.
Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- perform related reads and writes
COMMIT;💡 The transaction uses a consistent snapshot for its reads. Application code must still handle serialization errors and other transaction failures where applicable.
Key Takeaways
- MVCC provides snapshots of row versions.
- READ COMMITTED is PostgreSQL's default isolation level.
- Stronger isolation can require retrying transactions.
Knowledge Check
What is PostgreSQL's default transaction isolation level?