Comparisons and explanations · Intermediate

Read Committed versus Repeatable Read

Compare PostgreSQL isolation levels through snapshot timing, update conflicts and the anomalies that remain possible.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Name the implementation being compared
  2. Trace two reads around a concurrent commit
  3. Stable snapshots do not imply serializability
  4. Choose using anomalies and retry boundaries
  5. Ask Logeia about this articleCheck your understanding

Name the implementation being compared

Isolation-level names describe standard categories, but actual guarantees differ between database implementations. This comparison concerns PostgreSQL 18 and ordinary reads. Read Committed obtains a new snapshot for each statement; Repeatable Read uses the snapshot established for the transaction. Both can see the transaction’s own changes, so a fixed snapshot does not mean that every query must return identical text regardless of what the transaction itself does. Locking clauses and update commands need their own behavioral analysis.

Trace two reads around a concurrent commit

Session A reads a balance of one hundred. Session B then commits one hundred twenty, and A reads again. Read Committed can show one hundred twenty on the second statement, while Repeatable Read keeps one hundred from its established snapshot. B’s change is not missing from storage; visibility differs. The moment of snapshot establishment matters, because BEGIN alone is not a universal instruction to freeze the snapshot immediately. Moving B’s commit before A’s first relevant statement changes the comparison.

Stable snapshots do not imply serializability

Imagine two on-call doctors. Each transaction sees both doctors on call and independently sets its own row to off duty. The writes target different rows, so this is not simply two writers contending for one row. Decisions from compatible-looking snapshots can still leave nobody on call, a write-skew anomaly. PostgreSQL Repeatable Read prevents phantom reads as implemented, but does not prevent every serialization anomaly. A business invariant is not automatically enforced merely because a transaction uses a stronger-sounding isolation-level name.

Choose using anomalies and retry boundaries

Repeatable Read is useful when several statements need a stable view, but concurrent updates can require retrying the entire transaction. Read Committed can also be appropriate when a single conditional UPDATE, database constraints or explicit locking enforce the needed rule. Serializable still requires handling transaction failures rather than assuming every attempt succeeds. Avoid abandoned long transactions that retain old versions. Specify the forbidden anomaly, write shape, retry boundary and transaction lifetime before choosing an isolation level for an application workflow.

A snapshot governs database visibility rather than freezing the real-world resource. Reading a stock quantity repeatedly from a stable view does not reserve that stock for a later order. Conditional updates, constraints or an appropriate locking protocol may still be necessary when the order is accepted. Connect the chosen isolation level to the write that enforces the invariant, rather than treating repeatable display values as a reservation mechanism.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path