Repeatable Read

At REPEATABLE READ (MySQL's default), the first read of the transaction takes one snapshot, and every later plain SELECT reads from that same frozen view: no non-repeatable reads, no phantoms.

But only plain SELECTs. UPDATE, DELETE, and SELECT … FOR UPDATE/SHARE are current reads: they operate on the latest committed data, snapshot be damned. That asterisk is where MySQL's RR differs most from PostgreSQL's, and where ported assumptions break.

One snapshot, no phantoms

A

B

SELECT→ 100, 50 (snapshot taken)

UPDATE id1 = 999 (committed)

INSERT carol (committed)

SELECT→ 100, 50 — no 999, no carol

COMMIT

SELECT→ 999 + carol (new snapshot)

A> BEGIN;
Query OK

The snapshot is taken by the FIRST query, not by BEGIN.

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     100 
  2 |      50 
(2 rows)

B changes an existing row AND inserts a new one — both committed instantly.

B> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected

B> INSERT INTO accounts VALUES (3, 'carol', 300);
Query OK, 1 row affected

A> SELECT id, balance FROM accounts ORDER BY id; -- not 999 — no non-repeatable read; and no carol — no phantom
 id | balance 
----+---------
  1 |     100 
  2 |      50 
(2 rows)

A> COMMIT;
Query OK

Only a NEW transaction gets a new snapshot.

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     999 
  2 |      50 
  3 |     300 
(3 rows)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Current reads punch holes in the snapshot

Watch a single transaction read 100, then compute +50 from a value it has never seen, then suddenly see 200:

A

B

SELECT balance→ 100 (snapshot)

UPDATE balance = 150 (committed)

SELECT balance→ 100 (snapshot holds)

UPDATE +50 (current read of 150)

SELECT balance→ 200 (150 + 50 — the hole)

COMMIT

A> BEGIN;
Query OK

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

B commits a change. A keeps READING its stale snapshot…

B> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected

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

…but A's UPDATE is a current read: it computes from B's committed 150, not from the snapshot's 100.

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

A> SELECT balance FROM accounts WHERE id = 1; -- 150 + 50 — and now A sees it: the snapshot has a hole
 balance 
---------
     200 
(1 row)

A> COMMIT;
Query OK

PostgreSQL would have aborted A's UPDATE with 40001 instead. MySQL quietly switches world views.

If the competing write is NOT yet committed, A first waits on the row lock…

A> BEGIN;
Query OK

A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
 balance 
---------
     200 
(1 row)

B> BEGIN;
Query OK

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

A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
⏳ A is waiting for a lock…

…and proceeds from B's value the moment B commits. No error here either.

B> COMMIT;
Query OK

⏵ A resumes:
Query OK, 1 row affected

A> SELECT balance FROM accounts WHERE id = 1; -- 300 + 50
 balance 
---------
     350 
(1 row)

A> COMMIT;
Query OK

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Porting from PostgreSQL?

PostgreSQL's REPEATABLE READ refuses to update a row that changed after your snapshot (SQLSTATE 40001, see the PostgreSQL lesson). MySQL never raises that error: the write goes through against the current version. Retry loops written for PostgreSQL have nothing to catch here, and lost updates that PostgreSQL would have blocked go undetected.

Your DELETE and your SELECT live in different worlds

Current reads get truly disorienting when a predicate is involved. A transaction can "delete every row matching X", delete nothing (the current data no longer matches), and then keep seeing snapshot rows that match X (Hermitage calls this the write-predicate variant of G-single):

A

B

SELECT→ 10, 20 (snapshot)

+5 to every balance

COMMIT (now 15, 25)

DELETE balance = 20→ 0 rows

SELECT balance = 20→ still sees bob

A opens a snapshot. B then reshuffles every balance and commits.

A> BEGIN;
Query OK

A> SELECT id, balance FROM accounts ORDER BY id; -- snapshot taken
 id | balance 
----+---------
  1 |      10 
  2 |      20 
(2 rows)

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance + 5;
Query OK, 2 rows affected

B> COMMIT; -- current data is now 15 and 25
Query OK

A closes every account with balance 20 — its snapshot says that's bob. But the DELETE is a current read: it scans the committed 15 and 25, finds nothing, and deletes nothing.

A> DELETE FROM accounts WHERE balance = 20;
Query OK, 0 rows affected

A> SELECT id, balance FROM accounts WHERE balance = 20 ORDER BY id; -- …yet A still SEES a row with balance 20. Deleted: no such rows. Visible: one.
 id | balance 
----+---------
  2 |      20 
(1 row)

A> COMMIT;
Query OK

A's writes ran in one world, its reads in another. PostgreSQL's REPEATABLE READ aborts the DELETE with a serialization failure instead; on MySQL the cure is a locking read (FOR UPDATE) or SERIALIZABLE.

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

REPEATABLE READ gives you one snapshot per transaction, taken by the first read rather than by BEGIN, and plain SELECTs stay phantom-free, stronger than the SQL standard asks for. Writes and locking reads bypass that snapshot as current reads, and once you modify a row your own SELECTs see the new version, so the snapshot is a default, not a wall. No 40001-style serialization errors fire at this level, which leaves the anomalies RR can't stop, lost updates and write skew, to be handled with locking reads or constraints.

Further reading

• MySQL docs: REPEATABLE READ
• MySQL docs: Locking Reads
• The same lesson on PostgreSQL