Read Committed

The PostgreSQL 18 Read Committed manual documents the default level. A plain SELECT, without FOR UPDATE or FOR SHARE, uses a snapshot of data committed before that query began, plus earlier changes from its own transaction. It excludes other transactions' uncommitted changes and commits that occur during that query. Updating and locking commands have additional rules.

No dirty reads, even if you ask for them

B requests READ UNCOMMITTED and still reads 100 rather than A's uncommitted 999. The manual's level mapping documents READ COMMITTED behavior for READ UNCOMMITTED; this one read illustrates it.

A

B

UPDATE balance = 999 (uncommitted)

isolation = read uncommitted← setting accepted

SELECT→ 100 ← A's 999 stays invisible

A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

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

The SQL standard permits dirty reads at READ UNCOMMITTED; PostgreSQL maps that request to READ COMMITTED behavior.

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.6 · Run it yourself · Scenario source

Non-repeatable reads

A's two plain SELECTs return different values around B's committed UPDATE. The second part also asserts that a competing UPDATE waits for a row lock.

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

The plain SELECTs above did not wait for a row lock. The following UPDATE of the same row makes B wait for A's row lock; SELECT can still wait for conflicting table locks.

A> BEGIN ISOLATION LEVEL READ COMMITTED;
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.6 · Run it yourself · Scenario source

Phantoms

A's later aggregate includes B's newly committed matching row, changing its count from two to three inside the same transaction.

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.6 · Run it yourself · Scenario source

Read skew: a total that never existed

Separate reads around a committed transfer yield 50 and 75, totaling 125 rather than 100. The second schedule uses REPEATABLE READ and its two reads total 100. This concerns separate queries, not a single plain SELECT over both accounts.

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 ISOLATION LEVEL READ COMMITTED;
BEGIN

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

B> BEGIN ISOLATION LEVEL READ COMMITTED;
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 ISOLATION LEVEL READ COMMITTED;
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.6 · Run it yourself · Scenario source

The subtle one: UPDATE re-checks its WHERE clause

The Read Committed updating-command contract documents that an updater can wait, then operate on a newly committed target version after rechecking its predicate. It can therefore use a target version outside its initial snapshot. A single updating command is not necessarily a consistent snapshot of all rows it consults.

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 ISOLATION LEVEL READ COMMITTED;
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.6 · Run it yourself · Scenario source

Here B affects zero rows because value = 10 no longer matches after A commits 20. Check affected-row counts when application success depends on a row being changed. Plain SELECTs do not wait for these row-update locks, but they can wait for conflicting table locks, as the table-lock manual documents. The snapshot contract concerns transactional table rows; sequences have separate visibility and rollback rules.

Further reading

• Lost updates
• The same lesson on MySQL