Skip to content

CONCEPT Cited by 3 sources

Read committed isolation

READ COMMITTED is the ANSI-SQL isolation level at which each SELECT in a transaction reads the latest committed view of the database. Dirty reads are forbidden; non-repeatable reads and phantom reads are permitted. Each statement takes a fresh snapshot at the moment it runs, so two SELECTs on the same row in the same transaction may return different values if another transaction commits between them.

Why it is the practical sweet spot

Per Sugu Sougoumarane's canonical PlanetScale pedagogy post, READ COMMITTED is "the least contentious of the isolation levels" and the one most scalable applications should default to: (Source: sources/2026-04-21-planetscale-pitfalls-of-isolation-levels-in-distributed-databases.)

  1. Contention-free by default. Readers don't block writers and writers don't block readers (on the SELECT path). A long-running read-modify-write transaction on an unrelated row does not couple its duration to any other concurrent transaction.
  2. On-demand upgradeable to Serializable. The application can acquire a shared or exclusive lock on specific rows via locking reads (SELECT … LOCK IN SHARE MODE or SELECT … FOR UPDATE) — giving per-row Serializable guarantees exactly where the application needs them. See patterns/on-demand-serializable-read. This is the load-bearing lever that makes READ COMMITTED practical as a default — it does not mean the application gives up correctness; it means the application pays the locking cost only on the rows where it matters.
  3. Default in PostgreSQL. PostgreSQL ships with READ COMMITTED as the session default. MySQL defaults to REPEATABLE READ, but READ COMMITTED is a common MySQL choice for high-throughput OLTP workloads.

Semantics

From weakest to strongest ANSI-SQL reads:

Anomaly READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE
Dirty read allowed prevented prevented prevented
Non-repeatable read allowed allowed prevented prevented
Phantom read allowed allowed allowed* prevented

* MySQL's REPEATABLE READ additionally prevents phantoms on locking reads via gap locks; the SQL-standard definition permits phantoms.

Under READ COMMITTED, plain SELECT statements do not take row locks — they use the storage engine's MVCC visibility machinery (InnoDB's undo log in MySQL; per-row version chains in Postgres) to read the latest committed version.

The bank-withdrawal race, resolved

Sougoumarane's canonical bank-withdrawal worked example:

BEGIN;
SELECT balance FROM user WHERE id = ?;   -- A reads
INSERT INTO activity (...) VALUES (..., amount);
UPDATE user SET balance = balance - amount WHERE id = ?;
COMMIT;

Under bare READ COMMITTED, two concurrent withdrawals can both see the original balance and both subtract — a lost update. Fix: add an exclusive lock on the SELECT:

SELECT balance FROM user WHERE id = ? FOR UPDATE;

Per Sougoumarane's counter-intuitive but correct finding: write-lock is preferable to read-lock for this case because the write lock serialises the transactions cleanly. Read locks (LOCK IN SHARE MODE) re-create the Serializable deadlock shape (both transactions acquire the read lock, then race to upgrade). See concepts/shared-lock-vs-exclusive-lock. (Source: sources/2026-04-21-planetscale-pitfalls-of-isolation-levels-in-distributed-databases.)

Fresh snapshot per statement

Critical divergence from Snapshot Isolation: under READ COMMITTED each statement sees a new snapshot, so multi-statement reads are not internally consistent. Applications needing a stable multi-statement read view should either:

  • Upgrade to REPEATABLE READ (Snapshot Isolation) for the duration, or
  • Acquire explicit locking reads on the relevant rows.

(Source: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work.)

Engine specifics

  • PostgreSQL default. Every statement snapshot advances. Postgres's row-version storage means old versions coexist on-heap and VACUUM reclaims them later.
  • MySQL (InnoDB). READ COMMITTED creates a fresh snapshot per SELECT by reading through the undo log to the latest committed version. Release of locks on non-matching rows after a WHERE filter is more aggressive than under REPEATABLE READ (which holds gap locks).

Not sufficient on its own for distributed correctness

Per Sougoumarane's distributed-coupling argument, READ COMMITTED on a sharded database still incurs the cross-partition visibility-coordination tax of concepts/atomic-distributed-transaction|2PC when a transaction spans shards. See concepts/distributed-isolation-coupling-cost. The three-part prescription pairs READ COMMITTED with "avoid distributed transactions by keeping related rows within the same shard."

Seen in

Last updated · 550 distilled / 1,221 read