Isolation Levels

Read Committed, Repeatable Read, Serializable

PostgreSQL's default Read Committed gives each statement a fresh snapshot, so two reads in one transaction can see different committed data. Repeatable Read uses one snapshot for the whole transaction, so reads are stable (and conflicting updates fail with serialization errors to retry). Serializable additionally prevents anomalies that no serial order could produce, at the cost of more retries. PostgreSQL implements this with MVCC: readers never block writers and vice versa.

Two reads while another session commits, run

I ran this with Python 3, psycopg 3.3 and PostgreSQL 16.2, using separate connections to act as concurrent sessions. Between two reads in the same transaction, another session commits a change from 100 to 40. Under Read Committed the second read sees 40; under Repeatable Read it still sees 100.

import psycopg
URI = __import__("os").environ["DEMO_URI"]
setup = psycopg.connect(URI, autocommit=True)
setup.execute("CREATE TABLE accounts (id int PRIMARY KEY, balance int)")
setup.execute("INSERT INTO accounts VALUES (1, 100)")

for level in ["READ COMMITTED", "REPEATABLE READ"]:
    setup.execute("UPDATE accounts SET balance = 100 WHERE id = 1")
    reader = psycopg.connect(URI)
    reader.execute(f"SET TRANSACTION ISOLATION LEVEL {level}")
    first = reader.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]
    setup.execute("UPDATE accounts SET balance = 40 WHERE id = 1")       # another session commits
    second = reader.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]
    reader.commit(); reader.close()
    print(f"{level:<16} first read {first}, second read {second}")

Output:

READ COMMITTED   first read 100, second read 40
REPEATABLE READ  first read 100, second read 100

Retry serialization failures

If you use Repeatable Read or Serializable, wrap transactions in retry logic for serialization errors (SQLSTATE 40001).

Quick check: Under Read Committed, can two SELECTs in one transaction see different data?

• Yes, each statement sees the latest committed data
• No, never
• Only if the table is empty
• Only with Serializable