What ACID Actually Guarantees
ACID is a contract a database makes with your application: Atomicity means a transaction's writes either all happen or none do, Consistency means the database moves from one valid state to another (constraints, foreign keys, checks all hold), Isolation means concurrent transactions don't see each other's half-finished work, and Durability means once COMMIT returns, the data survives a crash. PostgreSQL implements atomicity and durability primarily through the write-ahead log (WAL): every change is logged before it is applied, and on crash recovery WAL is replayed to restore the exact committed state.
Cricket analogy: A run is only added to the scoreboard once the third umpire confirms a boundary via replay (durability), and a batter given out mid-over doesn't retroactively un-happen if the innings is interrupted by rain — the scorecard state is always consistent (atomicity) at any stoppage.
The Four SQL Isolation Levels
The SQL standard defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — that permit progressively fewer anomalies. PostgreSQL treats Read Uncommitted as an alias for Read Committed (it never allows dirty reads), so in practice you choose among three real levels. Read Committed is the default: each statement sees a fresh snapshot of committed data at the moment it starts, so two SELECTs in the same transaction can return different results if another transaction commits in between (a non-repeatable read).
Cricket analogy: Read Committed is like checking the live scoreboard app before each ball you bowl — you always see the latest confirmed score, but if you check again after two balls, the total may have changed because runs were added in between.
Repeatable Read, PostgreSQL's implementation of snapshot isolation, takes one snapshot at the start of the transaction and every statement in that transaction sees that exact same snapshot, eliminating non-repeatable reads and phantom reads. Serializable adds predicate locking on top of Repeatable Read and guarantees the outcome is equivalent to some serial (one-at-a-time) execution of all concurrent transactions; PostgreSQL implements this as Serializable Snapshot Isolation (SSI), which detects dangerous read-write dependency cycles and aborts one transaction with a serialization_failure error rather than allowing an anomaly, meaning your application must be prepared to retry.
Cricket analogy: Repeatable Read is like a commentator working from the scorecard printed at the start of the innings — even if the live score changes, their analysis stays consistent with that one fixed snapshot for the whole segment.
-- Read Committed (default): each statement gets a fresh snapshot
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- sees committed state at this moment
-- ... another session commits a change here ...
SELECT balance FROM accounts WHERE id = 1; -- may return a DIFFERENT value
COMMIT;
-- Serializable: whole transaction sees one snapshot, conflicts abort
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If a dangerous concurrent dependency is detected, PostgreSQL raises:
-- ERROR: could not serialize access due to read/write dependencies
-- SQLSTATE 40001 — the application must retry the whole transaction.Serializable transactions can fail with SQLSTATE 40001 even when no ordinary lock conflict occurred — this is expected behavior, not a bug. Every application using SERIALIZABLE isolation must wrap the transaction in a retry loop; PostgreSQL will never silently allow a non-serializable outcome.
PostgreSQL never permits dirty reads at any isolation level — the Read Uncommitted level exists only for SQL-standard compatibility and behaves identically to Read Committed.
- ACID = Atomicity, Consistency, Isolation, Durability; WAL is the mechanism behind atomicity and durability.
- PostgreSQL supports Read Committed (default), Repeatable Read, and Serializable — Read Uncommitted behaves like Read Committed.
- Read Committed re-reads committed data on every statement, permitting non-repeatable reads.
- Repeatable Read takes one snapshot for the whole transaction, eliminating non-repeatable and phantom reads.
- Serializable (SSI) guarantees a result equivalent to some serial ordering, aborting transactions that create dangerous dependency cycles.
- Serializable transactions can fail with SQLSTATE 40001 and must be retried by the application.
- Higher isolation levels reduce anomalies but increase the chance of serialization conflicts and retries.
Practice what you learned
1. Which isolation level does PostgreSQL use by default?
2. What SQLSTATE code indicates a Serializable transaction was aborted due to a dependency conflict?
3. Which PostgreSQL mechanism underlies both atomicity and durability?
4. How does Read Uncommitted behave in PostgreSQL?
5. What must an application do when it receives a serialization_failure error?
Was this page helpful?
You May Also Like
MVCC Explained
How PostgreSQL's Multi-Version Concurrency Control lets readers and writers avoid blocking each other by keeping multiple row versions and using visibility rules.
Locking in PostgreSQL
The row-level and table-level lock modes PostgreSQL uses to coordinate concurrent writers, and how to inspect and reason about lock contention.
Deadlocks and How to Avoid Them
How PostgreSQL detects circular lock-wait dependencies, what it does when it finds one, and practical patterns to prevent deadlocks in application code.