Level 2 · 25 min

Transactions

PostgreSQL transaction isolation levels control what concurrent transactions can see of each other's uncommitted changes. Understanding the phenomena each level prevents — and which phenomena remain possible — is essential for writing correct concurrent code.

Isolation Levels and Read Phenomena

SQL defines four isolation levels and three read phenomena. Dirty Read: reading uncommitted changes of another transaction (prohibited by all PostgreSQL levels — even READ UNCOMMITTED behaves as READ COMMITTED). Non-Repeatable Read: reading the same row twice in one transaction and getting different values because another transaction committed between the reads. Phantom Read: re-executing a range query and getting different rows because another transaction inserted/deleted rows matching the range. READ COMMITTED (default): prevents dirty reads, allows non-repeatable and phantom reads. Each statement sees a fresh snapshot of committed data at statement start. REPEATABLE READ: prevents dirty and non-repeatable reads. Snapshot is taken at transaction start — consistent for the entire transaction. Still allows phantoms in the SQL standard, but PostgreSQL's REPEATABLE READ prevents phantoms too. SERIALIZABLE: prevents all phenomena. Uses SSI (Serializable Snapshot Isolation).

SELECT FOR UPDATE and Row Locking

MVCC means reads don't block writes. But sometimes you need to lock rows for update: SELECT FOR UPDATE acquires a row-level exclusive lock and ensures no other transaction can update those rows until you commit. Essential for: preventing double-booking (select seat, lock it, then insert booking — no other transaction can book the same seat). SELECT FOR SHARE acquires a shared lock: other transactions can read but not update. SELECT FOR UPDATE SKIP LOCKED skips already-locked rows — ideal for queue implementations where multiple workers process different rows. SELECT FOR UPDATE NOWAIT fails immediately if the row is already locked — prevents waiting indefinitely. PostgreSQL implements the SERIALIZABLE isolation level via Serializable Snapshot Isolation (SSI), not traditional lock-based serializability. From PostgreSQL 14 Internals (Rogov): SSI tracks read-write dependencies between concurrent transactions using predicate locks — these do not block any operation but record which transaction read which data. When a dangerous read-write-write cycle is detected among three transactions, one is chosen as the 'victim' and aborted with a serialization failure (SQLSTATE 40001). Applications must retry on this error code. SSI has very low overhead when serialization conflicts are rare, making it practical for most OLTP workloads. The deadlock_timeout parameter (default 1s) controls how long PostgreSQL waits before running deadlock detection; in high-concurrency systems, lowering it to 100ms reduces the window during which a deadlock holds locks but increases detection CPU overhead.

Serializable Snapshot Isolation (SSI)

SERIALIZABLE in PostgreSQL uses SSI, not two-phase locking. SSI allows transactions to proceed optimistically using snapshots, then detects serialization anomalies at commit time. If the system detects that a set of transactions cannot have executed serially (based on read/write dependency cycles), it aborts one transaction with a serialization failure. Application code must retry on serialization failure. SSI provides true serializability without locking: read-only transactions never block. It has overhead for tracking dependencies but is much more scalable than 2PL. Serialization anomaly example: T1 reads A, T2 reads B, T1 writes B, T2 writes A — without SSI, both might commit in a state that could not have been produced by any serial execution.

Key Takeaways

READ COMMITTED is the default and prevents dirty reads. REPEATABLE READ takes a snapshot at transaction start — use for reporting queries that must see a consistent view. SERIALIZABLE for financial operations requiring true isolation.
SELECT FOR UPDATE is how you implement optimistic/pessimistic locking. SKIP LOCKED is the pattern for distributed queues. NOWAIT is for failing fast when a lock is contested.
SSI is PostgreSQL's SERIALIZABLE implementation. It detects anomalies at commit time and aborts conflicting transactions. Applications must retry on serialization failure (SQLSTATE 40001).

Code example

-- Prevent double-booking with SELECT FOR UPDATE\nBEGIN;\nSELECT id FROM seats WHERE id = 42 AND status = 'available' FOR UPDATE;\nUPDATE seats SET status = 'booked', user_id = 99 WHERE id = 42;\nCOMMIT;\n\n-- Queue worker with SKIP LOCKED\nBEGIN;\nSELECT id, payload FROM jobs\nWHERE status = 'pending'\nORDER BY created_at\nLIMIT 1\nFOR UPDATE SKIP LOCKED;\nCOMMIT;