# Isolation Levels — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/x-isolation

> What a transaction sees of others' changes.

## 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.

```python
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).

**Quiz:** Under Read Committed, can two SELECTs in one transaction see different data?

- [x] Yes, each statement sees the latest committed data
- [ ] No, never
- [ ] Only if the table is empty
- [ ] Only with Serializable

*Answer:* Yes, each statement sees the latest committed data. Each statement gets a new snapshot.