Read Committed

At READ COMMITTED, every statement gets a fresh snapshot: it sees everything committed by the time it starts. Within one transaction, two identical reads can disagree — because the world moved between them.

Non-repeatable reads

A

B

SELECT balance→ 100

UPDATE balance = 200 (autocommit)

SELECT balance→ 200 — same txn, different answer

A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

A> BEGIN;
Query OK

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;
Query OK, 1 row affected

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

A> COMMIT;
Query OK

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

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected

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

A> COMMIT; -- releases the row lock
Query OK

⏵ B resumes:
Query OK, 1 row affected

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 MySQL 8.4.10 · Run it yourself · Scenario source

Phantoms

The same effect on a set of rows: a WHERE clause can match rows in the second reading that didn't exist in the first.

A

B

count(amount ≥ 100)→ 2

INSERT order 3 (autocommit)

recount→ 3, total 1500 — a phantom

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

A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

A> BEGIN;
Query OK

A> SELECT count(*) 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);
Query OK, 1 row affected

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

A> COMMIT;
Query OK

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

Verified against MySQL 8.4.10 · 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

read alice→ 50

alice −25

bob +25

COMMIT (transfer done)

read bob→ 75 (50 + 75 = 125!)

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> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

A> BEGIN;
Query OK

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

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance - 25 WHERE owner = 'alice';
Query OK, 1 row affected

B> UPDATE accounts SET balance = balance + 25 WHERE owner = 'bob';
Query OK, 1 row affected

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

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

A> COMMIT;
Query OK

Neither row was ever wrong — A read alice BEFORE the transfer and bob AFTER it. REPEATABLE READ pins every plain SELECT to one snapshot:

A> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

A> BEGIN;
Query OK

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

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
Query OK, 1 row affected

B> UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
Query OK, 1 row affected

B> COMMIT;
Query OK

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

A> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

After the wait, the re-check

When an UPDATE waits for a row lock and finally gets it, the row may no longer match its WHERE clause. READ COMMITTED re-evaluates and silently skips it (semi-consistent read):

A

B

UPDATE row1→ 20 (uncommitted)

UPDATE WHERE value = 10→ ⏳ waits

COMMIT — row1 is now 20

⏵ UPDATE WHERE value = 10→ completes

A> BEGIN;
Query OK

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

B targets WHERE value = 10. The latest committed version of row 1 still qualifies — but it's locked by A, so B waits.

B> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

B> BEGIN;
Query OK

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;
Query OK

⏵ B resumes:
Query OK, 0 rows affected

0 rows affected — the row slipped away.

B> COMMIT;
Query OK

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

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

A fresh snapshot per statement means no dirty reads, but non-repeatable reads and phantoms are routine — if a report has to be internally consistent, run it at REPEATABLE READ. And Query OK, 0 rows affected after a lock wait may mean the row escaped your WHERE, so code that assumes it updated exactly the rows it saw is wrong at this level. InnoDB also takes fewer gap locks here than at RR, which is one reason some heavy-write shops prefer it; locking is chapter 3.

Further reading

• MySQL docs: READ COMMITTED
• The same lesson on PostgreSQL