Transaction Isolation Levels in Postgres and MySQL: A Practical Comparison

The SQL standard defines four transaction isolation levels. Most database textbooks reproduce the same table: Read Uncommitted prevents nothing useful, Read Committed blocks dirty reads, Repeatable Read blocks non-repeatable reads, and Serializable blocks everything. Clean and orderly — and, when it comes to production databases, only partially true.

Postgres and MySQL InnoDB both claim to implement all four levels. What they actually implement diverges from the standard in ways that matter for application correctness. Postgres silently upgrades Read Uncommitted to Read Committed, and its Repeatable Read is not the SQL-92 Repeatable Read — it is full snapshot isolation, which also prevents phantom reads. MySQL's Repeatable Read similarly goes beyond the standard, using gap locks to block phantoms. These aren't pedantic distinctions: an application that assumes "Repeatable Read allows phantom reads" and compensates with explicit locks is doing unnecessary work on Postgres. A team that reaches for Serializable expecting two-phase locking will instead get Serializable Snapshot Isolation (SSI) with different failure modes.

The four levels and the three anomalies

SQL-92 defined isolation in terms of three read anomalies that transactions should or should not be allowed to observe.

Dirty read: transaction A reads a row that transaction B has modified but not yet committed. If B rolls back, A has read data that never officially existed.

Non-repeatable read: transaction A reads the same row twice and gets different values, because transaction B committed a change between A's two reads. The row existed both times — it just changed.

Phantom read: similar but affects a set of rows. A runs a range query and gets five rows. B inserts a new row that matches the predicate and commits. A repeats the query and now gets six rows. The phantom is the new row.

SQL-92 then defined isolation levels as a permission matrix against these three anomalies. Read Uncommitted permits all three. Read Committed prevents dirty reads only. Repeatable Read prevents dirty + non-repeatable but allows phantoms. Serializable prevents all three. That's the textbook. Now for what actually ships.

Postgres reality

Read Uncommitted — accepted but ignored. Postgres accepts SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED without error and then runs the transaction at Read Committed instead. The Postgres transaction isolation docs state this directly: "PostgreSQL's Read Uncommitted mode behaves like Read Committed." Dirty reads are impossible in Postgres regardless of the level requested.

Read Committed — the Postgres default. Each statement within a transaction sees a snapshot of committed data as of the moment that statement begins. Two SELECTs in the same transaction can see different committed data — non-repeatable reads possible, phantoms possible. For most OLTP workloads, this is the right choice.

Repeatable Read — snapshot isolation, not SQL-92 RR. This is where Postgres diverges most. SQL-92 RR allows phantom reads. Postgres RR prevents them, because it implements full snapshot isolation: every statement in the transaction sees a consistent snapshot as of the moment the transaction began, not the moment each statement begins. New rows committed by other transactions simply don't appear in the current transaction's view.

The tradeoff is write conflicts. If two concurrent transactions read a snapshot and both write based on what they read, Postgres detects the conflict and the second writer receives ERROR: could not serialize access due to concurrent update. The application must retry or abort.

Serializable — SSI, not two-phase locking. Most traditional databases implement Serializable with two-phase locking (2PL): shared locks on reads, exclusive locks on writes, released only on commit. This blocks any non-serial interleaving but creates contention and deadlock risk proportional to lock count. Postgres has used Serializable Snapshot Isolation (SSI) since version 9.1, released in 2011, based on Cahill, Röhm, and Fekete's "Serializable Isolation for Snapshot Databases" (SIGMOD 2008). Rather than blocking concurrent access, SSI tracks read and write dependencies between transactions and detects dangerous anomalies at commit time. Transactions that would violate serializability are aborted with SQLSTATE 40001 (serialization failure). Applications must catch this and retry.

The anomaly Repeatable Read still misses is write skew: two transactions each read the same set of rows, conclude a condition is safe, and each writes a different row — leaving the database in a state impossible under serial execution. SSI catches this. The classic example is the on-call doctor pair: two doctors are on call, both read "two of us are on call, I can step out," both update their own row to off-call, and now nobody is on call. Repeatable Read allows it; Serializable aborts one transaction and forces a retry.

MySQL InnoDB reality

Read Uncommitted and Read Committed behave as the SQL-92 standard describes. RU permits dirty reads (rare in production); RC is reasonable for high-write workloads but not the MySQL default.

Repeatable Read — MySQL's default, gap-locked. Like Postgres RR, this is stronger than SQL-92. InnoDB prevents phantom reads using next-key locking: a record lock on the row plus a gap lock on the range before it. When a transaction reads a range, InnoDB locks the matching rows and the gaps between them, preventing other transactions from inserting rows that would satisfy the same predicate. Per the MySQL 8.0 InnoDB isolation levels reference: "InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking."

Postgres has no gap locks — it uses snapshots. The MySQL approach blocks concurrent inserts into locked ranges (rather than letting them proceed silently as Postgres would). Less phantom anomaly, more lock contention and deadlock risk under high insert volume into indexed ranges.

Serializable — plain reads become locking reads. MySQL converts every plain SELECT into SELECT ... LOCK IN SHARE MODE. Reads acquire shared locks; concurrent writers block until commit. Closer to classical 2PL than Postgres SSI. Failure mode is deadlock (error 1213) rather than serialization failure. Both require application retry, but the locking model makes long transactions more expensive and hot rows more contentious.

Concrete SQL: non-repeatable read at Read Committed

The clearest way to see the difference is two sessions interleaved. Set up a table:

CREATE TABLE account (id INT PRIMARY KEY, balance INT);
INSERT INTO account VALUES (1, 1000);

Session A opens at Read Committed:

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM account WHERE id = 1;
-- returns 1000

-- (Session B runs here, see below)

SELECT balance FROM account WHERE id = 1;
-- returns 1500   ← non-repeatable read
COMMIT;

-- Session B (between Session A's two SELECTs)
BEGIN;
UPDATE account SET balance = 1500 WHERE id = 1;
COMMIT;

At Read Committed, Session A sees 1000 then 1500. Switch Session A to Repeatable Read and both selects return 1000 — the snapshot was fixed at BEGIN, and Session B's committed change is invisible to Session A for the duration of the transaction.

Decision guide

• Most web applications: Postgres Read Committed (the default). Row-level locking handles concurrent writes to the same row; the application doesn't have to manage serialization failures.
• Read-heavy reports / multi-step reads: Repeatable Read for a consistent snapshot across queries within one transaction. On Postgres, this also kills phantom reads at no extra cost.
• Cross-row invariants (accounting balances, inventory counts, booking uniqueness): Postgres Serializable. SSI catches write skew that RR misses; the retry-on-40001 pattern keeps correctness without 2PL overhead.
• MySQL workloads with high insert volume: consider Read Committed over the default RR if gap-lock contention shows up. Tradeoff: phantom reads possible — evaluate whether the application tolerates that.

Tradeoffs and the retry pattern

Higher isolation isn't free. Postgres Read Committed and Repeatable Read use MVCC snapshots with no extra locking beyond row-level write locks. The jump to Serializable adds predicate tracking — Postgres maintains a per-transaction in-memory data structure of read predicates to detect dangerous read-write cycles. Under workloads where many transactions read overlapping ranges then write, the memory and CPU cost grow.

For Postgres Serializable, the retry pattern is mandatory. The application must catch SQLSTATE 40001 and re-run the entire transaction from BEGIN. Partial retries are wrong — the transaction's read set may be stale. A short backoff between attempts helps under contention. Without retries, Serializable will surface as flaky errors rather than as the safety property it actually provides.

Limitation worth saying out loud

This post compares Postgres and MySQL InnoDB — the two dominant open-source RDBMS. Oracle uses a read-consistent model based on undo segments where every query sees data as of the query's start SCN, not the transaction's start time. SQL Server's default Read Committed uses locking; READ_COMMITTED_SNAPSHOT_ISOLATION (RCSI) and Snapshot Isolation (SI) are separate database-level flags with their own behavior. Migrating between SQL Server and Postgres frequently surprises teams because "Read Committed" means different things in the two systems. That's its own dedicated post.

For related reading: timezone bugs in production covers another class of correctness issues where the standard and runtime behavior diverge in ways that cause data corruption. YAML parsing traps is a reminder that widely-used specifications often contain surprises that bite engineers who assume the name describes the behavior. And JWT storage tradeoffs covers the security-architecture decisions that often accompany database design.