Read Committed

READ COMMITTED is PostgreSQL's default isolation level — the one nearly all of your production code runs at. Its contract: every statement sees a fresh snapshot of everything committed before that statement began. Never uncommitted data; never data committed mid-statement.

That per-statement snapshot is both its strength (no waiting for readers, always-fresh data) and the source of every anomaly on this page.

No dirty reads, even if you ask for them

A

B

UPDATE balance = 999 (uncommitted)

isolation = read uncommitted← setting accepted

SELECT→ 100 ← A's 999 stays invisible

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1

B explicitly requests READ UNCOMMITTED — the one level that would permit dirty reads.

B> BEGIN ISOLATION LEVEL READ UNCOMMITTED;
BEGIN

B> SELECT current_setting('transaction_isolation') AS isolation; -- the setting is accepted…
    isolation     
------------------
 read uncommitted 
(1 row)

B> SELECT balance FROM accounts WHERE id = 1; -- …but A's uncommitted 999 stays invisible
 balance 
---------
     100 
(1 row)

B> COMMIT;
COMMIT

A> ROLLBACK;
ROLLBACK

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Non-repeatable reads

The flagship READ COMMITTED anomaly: the same query, twice, inside one transaction — two different answers.

A

B

SELECT balance→ 100

UPDATE balance = 200 (autocommit)

SELECT balance→ 200 — same txn, different answer

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

While A's transaction is still open, B updates the row and commits.

B> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1

A> SELECT balance FROM accounts WHERE id = 1; -- same query, same transaction — different answer
 balance 
---------
     200 
(1 row)

A> COMMIT;
COMMIT

Readers never block — but writers do. The same interleaving with UPDATEs makes B wait for A's row lock.

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1

B> UPDATE accounts SET balance = 400 WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT; -- releases the row lock
COMMIT

⏵ B resumes:
UPDATE 1

B> SELECT balance FROM accounts WHERE id = 1; -- B's write landed on top of A's committed 300
 balance 
---------
     400 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Phantoms

The same effect applies to sets of rows, not only values — new matching rows appear between your statements:

A

B

SELECT count→ 2

INSERT order 3 (700), committed

SELECT count→ 3, total 1500 ← a phantom row

COMMIT

A computes a report twice inside one transaction: count first, then the total.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

A> SELECT count(*)::int AS n FROM orders WHERE amount >= 100;
 n 
---
 2 
(1 row)

Between A's two statements, B commits a new order that matches A's WHERE clause.

B> INSERT INTO orders VALUES (3, 700);
INSERT 0 1

A> SELECT count(*)::int AS n, sum(amount)::int AS total FROM orders WHERE amount >= 100; -- a third row appeared out of nowhere — a phantom
 n | total 
---+-------
 3 |  1500 
(1 row)

A> COMMIT;
COMMIT

A's report now says '2 orders' in one place and '3 orders, total 1500' in another.

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Read skew: a total that never existed

Non-repeatable reads have a nastier multi-row cousin — read skew: every row you read was committed and correct, yet the combination existed at no point in time:

A

B

SELECT alice→ 50 (before the transfer)

UPDATE alice −25

UPDATE bob +25

COMMIT← transfer done, invariant intact

SELECT bob→ 75 ← 50+75=125, money that never existed

The invariant: alice + bob = 100 at every moment. A is an auditor summing the accounts row by row; B transfers 25 between them mid-audit.

A> BEGIN;
BEGIN

A> SELECT balance FROM accounts WHERE owner = 'alice';
 balance 
---------
      50 
(1 row)

B> BEGIN;
BEGIN

B> UPDATE accounts SET balance = balance - 25 WHERE owner = 'alice';
UPDATE 1

B> UPDATE accounts SET balance = balance + 25 WHERE owner = 'bob';
UPDATE 1

B> COMMIT; -- a perfectly correct transfer — atomic, invariant preserved
COMMIT

A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 50 + 75 = 125. The auditor found 25 that never existed.
 balance 
---------
      75 
(1 row)

A> COMMIT;
COMMIT

Neither row was ever wrong — A read alice BEFORE the transfer and bob AFTER it. Fresh-snapshot-per-statement cannot hold two rows still. REPEATABLE READ can:

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE owner = 'alice';
 balance 
---------
      25 
(1 row)

B> BEGIN;
BEGIN

B> UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
UPDATE 1

B> UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
UPDATE 1

B> COMMIT;
COMMIT

A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 25 + 75 = 100 — one snapshot, one moment in time
 balance 
---------
      75 
(1 row)

A> COMMIT;
COMMIT

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The subtle one: UPDATE re-checks its WHERE clause

What happens when your UPDATE has to wait for a lock, and the row changes while you wait? At READ COMMITTED, PostgreSQL re-evaluates the WHERE clause against the new row version — and silently skips rows that no longer match ("The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition" — the manual):

A

B

UPDATE id=1 value 10→ 20 (uncommitted)

UPDATE WHERE value=10→ ⏳ waits

COMMIT← row 1 is now 20

⏵ UPDATE WHERE value=10→ completes

SELECT→ 20, 30 ← B re-checked WHERE, matched 0 rows

A> BEGIN;
BEGIN

A> UPDATE items SET value = value * 2 WHERE id = 1; -- row 1: 10 → 20, uncommitted
UPDATE 1

B targets WHERE value = 10. In B's snapshot row 1 still qualifies — but it's locked by A, so B waits.

B> UPDATE items SET value = 99 WHERE value = 10;
⏳ B is waiting for a lock…

A commits. B wakes up and re-checks the row it waited for — against the NEW version, where value is 20.

A> COMMIT;
COMMIT

⏵ B resumes:
UPDATE 0

UPDATE 0 — the row slipped away.

B> SELECT id, value FROM items ORDER BY id;
 id | value 
----+-------
  1 |    20 
  2 |    30 
(2 rows)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

UPDATE 0 — no error, no warning. If your code assumes "the row matched a moment ago, so it was updated", this is where that assumption dies. Always check the affected-row count.

The pattern to hold onto: READ COMMITTED gives you a fresh snapshot per statement, so any single statement is internally consistent while two statements in the same transaction can flatly disagree. Dirty reads never happen in PostgreSQL, full stop. But that same per-statement snapshot is exactly why multi-statement read-modify-write logic here is exposed to lost updates, the most common real-world transaction bug, and why an UPDATE or DELETE that waited for a lock can affect fewer rows than you saw. Check the affected-row count, every time.

Further reading

• PostgreSQL docs: Read Committed Isolation Level
• The same lesson on MySQL