One snapshot per statement, or one per transaction
Read committed sees each new commit at its next statement. Repeatable read keeps the snapshot from its first statement, not from BEGIN.
Same name, same level?
Bad: RR is RR
PostgreSQL’s aborts a conflicting write with 40001. MySQL’s writes over the newer row.
Good: Compare the writes
Ask what a write does when it meets a newer row.
Read committed in PostgreSQL
- Your UPDATE meets a row someone is changing
- Wait for them to commit or roll back
- Re-check your WHERE on the new version
- Still matches, apply. No longer matches, skip silently.
Rows hold 9 and 10. A adds 1 to each; B deletes the 10s and waits.
The trap
B re-checks only the row it waited for, now 11. DELETE 0, yet a row now holds 10.
MySQL repeatable read runs two kinds of read
Plain SELECTs read the snapshot. UPDATE reads and locks the latest rows, including order 150 that another session committed later.
Read committed for writes, repeatable read for reports
Guard the write in one UPDATE and check rows affected. Give multi-query reports one read-only snapshot, ideally on a replica.
Migrating MySQL to PostgreSQL? Check three habits.
- Multi-query reads lose their stable snapshot
- Gap locks no longer keep competing inserts out
- Its repeatable read throws 40001, so add retries
Remember
A snapshot per statement, or one per transaction. The real difference is what a write does when it meets a newer row.