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.10 · 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.10 · 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.10 · 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