Databases and servers
MySQL vs PostgreSQL Default Isolation Level: What Changes for Your Transactions
In short
The MySQL vs Postgres isolation level gap bites when code moves between them: the same name, REPEATABLE READ, fails in opposite ways. InnoDB uses it by default and lets an UPDATE touch rows its snapshot never showed. PostgreSQL defaults to READ COMMITTED, and its REPEATABLE READ aborts a conflicting write with SQLSTATE 40001 that your code must retry.
Which isolation level does each engine start in?
The MySQL 8.4 manual says InnoDB offers all four SQL:1992 levels and that REPEATABLE READ is the default. A session can switch with SET TRANSACTION, and the server-wide default comes from the --transaction-isolation startup option.
PostgreSQL, in the current (version 19) documentation, starts every transaction at Read Committed. It also accepts Read Uncommitted as a keyword but treats it exactly like Read Committed, so a PostgreSQL client never sees another session's uncommitted rows.
So an app that never sets a level runs one rung higher on MySQL than on PostgreSQL. That difference is invisible in single-user tests and shows up only under concurrent writes.
How is REPEATABLE READ built in each engine?
InnoDB fixes a snapshot at the first consistent read of the transaction, and every later plain SELECT reads that snapshot. Locking reads, UPDATE and DELETE work differently: with a unique index and a unique search condition only the found index record is locked, while any other condition locks the scanned range with gap or next-key locks so other sessions cannot insert into it.
The manual adds a detail that surprises people. The snapshot governs SELECT, not necessarily DML. An UPDATE in a REPEATABLE READ transaction can change rows another session committed after the snapshot, rows a plain SELECT in the same transaction could not see, and once updated those rows become visible to it.
PostgreSQL takes its snapshot at the first statement that is not transaction control, and the docs name the technique Snapshot Isolation. When a row it wants to change was modified by a transaction that committed after that snapshot, PostgreSQL does not apply the change to the newer row. It rolls the transaction back with could not serialize access due to concurrent update.
Which anomalies does each manual admit to?
PostgreSQL publishes a phenomena table per level. The MySQL manual has no equivalent table, so the InnoDB column below describes mechanisms the manual documents rather than a list of guarantees.
| Level | InnoDB (MySQL 8.4 manual) | PostgreSQL (current docs) |
|---|---|---|
| READ UNCOMMITTED | Plain reads may return an earlier, uncommitted row version; the manual calls this a dirty read | Behaves as Read Committed; no dirty reads |
| READ COMMITTED | Fresh snapshot per consistent read; gap locks only for foreign-key and duplicate-key checks, so phantom rows can appear | Snapshot per statement; nonrepeatable reads, phantoms and serialization anomalies possible |
| REPEATABLE READ | Snapshot from the first read; next-key locking blocks phantoms for locking reads and range writes | No nonrepeatable or phantom reads; serialization anomalies still possible |
| SERIALIZABLE | Plain SELECT becomes SELECT ... FOR SHARE when autocommit is off, so readers block | Serializable Snapshot Isolation with predicate locks that never block; conflicts end in 40001 |
What does the MySQL vs Postgres isolation level gap mean for retries?
Both engines expect the application to retry, but the unit of retry is not the same.
- PostgreSQL at Repeatable Read or Serializable: retry on SQLSTATE
40001every time, and rerun the whole transaction, including the code that decided which SQL to send. PostgreSQL has no automatic retry because it cannot redo that logic for you, and a busy row may need several attempts. - PostgreSQL deadlocks (
40P01) are also worth retrying. Unique violations (23505) and exclusion violations (23P01) sometimes are, but they can be permanent, so check before looping. - InnoDB deadlock: the whole transaction is rolled back, so rerun all of it. The manual calls deadlocks normal and harmless on a busy server.
- InnoDB lock wait timeout: out of the box InnoDB undoes just the statement that timed out, leaving earlier work in the transaction open. Retry that statement, or the full transaction if the server runs with
--innodb-rollback-on-timeout. The lock wait timeout fix page covers finding the blocker. - InnoDB duplicate-key error: only the statement is undone unless
IGNOREwas given.
Where does code break when it moves between the two?
Read the documented behaviour against the common read-then-write pattern and four risks stand out, plus one habit that covers them:
- MySQL to PostgreSQL with defaults kept: two reads in one transaction can disagree, because Read Committed gives each statement its own snapshot. Ask for Repeatable Read explicitly and add the 40001 retry.
- PostgreSQL to MySQL: under repeatable read, an
UPDATEin InnoDB acts on the latest committed row version, not on the snapshot your earlierSELECTsaw, while a PostgreSQL transaction in the same spot is told to retry. Port the retry logic and read withSELECT ... FOR UPDATEwhen the write depends on what you read. - Mixed reads in one InnoDB transaction: the manual advises against combining locking and plain reads at REPEATABLE READ and says that in such cases you usually want SERIALIZABLE.
- Business rules such as "no more than N rows": PostgreSQL warns that Repeatable Read is unlikely to enforce them without explicit locks. Serializable plus a retry loop is the documented route.
- Because of these, I would set the level in code on every transaction that writes, rather than trusting whatever default the server or a managed service ships with.
What else do people ask?
Does PostgreSQL allow phantom reads at Repeatable Read?
No. The SQL standard permits them at that level, but the PostgreSQL table marks them as allowed by the standard and not possible in PostgreSQL.
Can MySQL be switched to behave like the PostgreSQL default?
Setting InnoDB to READ COMMITTED gives each read a new snapshot and drops most gap locking. The manual notes it supports only row-based binary logging, and with binlog_format=MIXED the server switches to row-based logging on its own.
Sources
- MySQL 8.4 Reference Manual, transaction isolation levels, fetched 2026-11-20
- MySQL 8.4 Reference Manual, consistent nonlocking reads, fetched 2026-11-20
- MySQL 8.4 Reference Manual, phantom rows, fetched 2026-11-20
- PostgreSQL documentation (current), transaction isolation, fetched 2026-11-20
- PostgreSQL documentation (current), serialization failure handling, fetched 2026-11-20
- MySQL 8.4 Reference Manual, InnoDB error handling, fetched 2026-11-20
- MySQL 8.4 Reference Manual, how to minimize and handle deadlocks, fetched 2026-11-20
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Built from the MySQL 8.4 Reference Manual and the current PostgreSQL documentation as read on 20 Nov 2026; no database was run for this page. MariaDB, Aurora variants, managed-service default overrides and performance are left out. Found a mistake? Tell me.