Backend / Databases / SQL / 23_isolation_anomalies_across_dbs.md

Isolation anomalies across databases

Updated 5 interview angles 8 min read source
On this page9
  1. The anomalies, by name
  2. The matrix
  3. PostgreSQL: snapshots, then SSI
  4. MySQL InnoDB: two kinds of read
  5. SQLite, Oracle, SQL Server
  6. Where it breaks in production
  7. The trade not taken
  8. Related
  9. Interview angle

Isolation anomalies across databases

Article.

REPEATABLE READ in PostgreSQL and REPEATABLE READ in MySQL are different guarantees with the same name. Senior interviews probe exactly this: name the anomaly, say which engine allows it at which level, and what the application sees when the engine refuses, an error, a deadlock, or nothing at all.

Which engine silently loses a concurrent counter increment at its default level? The answer is in the table below. Every result was run, not quoted: PostgreSQL 18.4 (from Transaction isolation levels), MySQL 8.4.11 InnoDB, and SQLite 3.50.4, with two Python sessions each.

The anomalies, by name

The ANSI definitions are ambiguous enough that two papers renamed them, and Jepsen reports use the later names:

Anomaly What happens Formal name
Dirty write overwriting uncommitted data P0 / G0
Dirty read reading uncommitted data P1 / G1a
Non-repeatable read one row read twice, two values P2
Phantom one query run twice, new rows P3
Lost update read, compute, write; one write vanishes P4
Read skew two rows read at different moments A5A / G-single
Write skew disjoint writes break a shared rule A5B / G2-item

P0-P4 and A5 are from Berenson et al., “A Critique of ANSI SQL Isolation Levels” (1995); G0-G2 from Adya’s thesis (1999). Dirty writes are prevented by row locks in every engine here.

The matrix

Anomaly PostgreSQL MySQL InnoDB
Dirty read never (RU acts as RC) READ UNCOMMITTED only
Non-repeatable read RC; gone at RR RC; gone at RR for plain reads
Phantom RC; gone at RR RR hides it from reads, not writes
Lost update RC silent; RR raises 40001 silent at RC and RR
Read skew RC; gone at RR RC; gone at RR
Write skew RC, RR; SER raises 40001 RC, RR; SER deadlocks (1213)

The defaults differ too: PostgreSQL runs at READ COMMITTED, InnoDB at REPEATABLE READ. So the answer to the opening question is both: PostgreSQL’s default and MySQL’s default each lose a read-modify-write silently; only PostgreSQL’s REPEATABLE READ refuses it.

PostgreSQL: snapshots, then SSI

REPEATABLE READ is snapshot isolation: one snapshot for the transaction, and first-updater-wins, a write to a row changed since the snapshot fails with could not serialize access due to concurrent update. SERIALIZABLE adds SSI, which tracks reads and cancels one transaction of a dangerous pair. Both are optimistic: nobody waits, somebody retries. The details and the two-session outputs are in Transaction isolation levels.

MySQL InnoDB: two kinds of read

InnoDB’s REPEATABLE READ also keeps a snapshot, but only for consistent reads: plain SELECT. UPDATE, DELETE, INSERT ... SELECT and locking reads (FOR UPDATE, FOR SHARE) do a current read of the latest committed rows. One transaction can mix both, and that is where its anomalies come from.

The update that reads past the snapshot

text 
T1: SELECT bal            -> 100
T2: UPDATE bal = 150; COMMIT
T1: SELECT bal            -> 100      snapshot
T1: UPDATE bal = bal + 10
T1: SELECT bal            -> 160      not 110

The UPDATE applied to the latest row, 150, and from then on T1 sees its own version. PostgreSQL’s REPEATABLE READ would have raised 40001 at the UPDATE instead.

Lost updates, silently

The same read-in-Python, add-10, write-back as in PostgreSQL:

text 
READ COMMITTED    final 110 (want 120), no error
REPEATABLE READ   final 110 (want 120), no error

The second writer’s UPDATE waited for the first, then overwrote it. Jepsen’s analysis of MySQL 8.0.34 (December 2023) reached the same conclusion: InnoDB REPEATABLE READ allows lost updates, read skew between consistent and current reads, and write skew. Use SET bal = bal + 10, or SELECT ... FOR UPDATE before computing.

Phantoms hidden from reads, not from writes

text 
T1: SELECT count(*) WHERE user_id = 7     -> 3
T2: INSERT a row for user 7; COMMIT
T1: SELECT count(*) WHERE user_id = 7     -> 3    snapshot
T1: UPDATE ... WHERE user_id = 7          -> 4 rows touched
T1: SELECT count(*) WHERE user_id = 7     -> 4

The phantom was invisible to the SELECT and updated by the UPDATE. A transaction that counts, then updates “the rows it counted”, updated one more.

Gap locks

A plain SELECT takes no locks: an insert by another session right after it took 4 ms. A locking read takes next-key locks, the rows read plus the gaps between index entries, so inserts into the range wait:

text 
T1: SELECT * FROM booking WHERE slot = 5 FOR UPDATE      -- no row with slot 5
T2: INSERT INTO booking VALUES (6, 'x')
    -> 1205 Lock wait timeout exceeded (after 3.0 s)

Slot 6 was never read; it was in the gap the lock covered. This is why InnoDB deadlocks more often than PostgreSQL, and also why the empty-slot booking that double-booked in PostgreSQL did not here:

text 
both sessions: SELECT ... WHERE slot = 5 FOR UPDATE   -- gap locks, compatible
both insert slot 5
  T1: committed | T2: 1213 Deadlock found | bookings: 1

Gap locks conflict with inserts, not with each other, so both sessions got them, both inserts waited on the other’s gap, and InnoDB killed one. Correct, but the application must retry deadlocks. Under READ COMMITTED InnoDB does not take gap locks, so this protection disappears.

SERIALIZABLE by locking

InnoDB’s SERIALIZABLE turns plain SELECTs into FOR SHARE (when autocommit is off), so readers lock what they read. Write skew turned into a deadlock:

text 
doctors on call, each goes off call if the other is on
  REPEATABLE READ   both committed         on call: 0
  SERIALIZABLE      T2: 1213 Deadlock      on call: 1

Prevented, pessimistically: transactions wait on each other, and one of every conflicting pair is killed with error 1213, which is retried like PostgreSQL’s 40001.

SQLite, Oracle, SQL Server

  • SQLite has one writer at a time, so writes are serial; in WAL mode readers keep a snapshot. A transaction that read and then tries to write after another writer committed cannot upgrade:

    text 
    reader sees 100; another connection commits 150; reader still sees 100
reader's UPDATE -> database is locked  (SQLITE_BUSY_SNAPSHOT)

    That is SQLite’s serialization failure: roll back and retry the whole transaction. Waiting (busy_timeout) does not help. Start write transactions with BEGIN IMMEDIATE to take the write lock first.

  • Oracle defaults to READ COMMITTED and has no REPEATABLE READ. Its SERIALIZABLE is snapshot isolation without SSI: write skew is allowed, and a concurrent update fails with ORA-08177.

  • SQL Server defaults to locking READ COMMITTED; the SNAPSHOT level and READ_COMMITTED_SNAPSHOT option give PostgreSQL-like snapshots, and its SERIALIZABLE uses range locks, like InnoDB.

Where it breaks in production

Porting between PostgreSQL and MySQL

Code written for PostgreSQL REPEATABLE READ relies on 40001 to stop lost updates; on MySQL the same code loses them silently. Code written for MySQL relies on gap locks to stop double-booking; on PostgreSQL the same FOR UPDATE locks nothing. Re-check every concurrency assumption when the engine changes, not only the SQL dialect.

Deadlocks from gap locks

Two transactions insert into the same index range after locking reads, and InnoDB reports 1213 under modest load. Narrow the locked range with a precise index, use READ COMMITTED where phantoms do not matter, and retry on 1213 as on any serialization failure. SHOW ENGINE INNODB STATUS prints the latest deadlock.

Anomalies are silent

Lost updates and write skew raise no error at the levels that allow them; they show up later as wrong balances. Reconciliation queries (ledger total against stored balance), constraints as tripwires (CHECK (balance >= 0), exclusion constraints), and dashboards of 40001, 40P01 and 1213 rates are how they are caught.

Read-only anomalies

Snapshot isolation allows even a read-only transaction to see a state no serial order produces (Fekete, O’Neil and O’Neil, 2004): a report can be wrong without writing anything. PostgreSQL’s SERIALIZABLE READ ONLY DEFERRABLE waits for a snapshot where this cannot happen.

The trade not taken

The two engines chose opposite ways to give SERIALIZABLE. PostgreSQL’s is optimistic: run on snapshots, track reads, abort the loser; readers never block, and the price is retries.

InnoDB’s is pessimistic: lock what was read, including gaps, and let the deadlock detector pick a victim; correctness comes from waiting, and the price is lock contention and deadlocks even when no anomaly would have happened. Under low contention optimistic wins; under heavy contention on the same rows, both end up retrying, and the better fix is a design with less shared state.

Interview angle 5

  • “Is REPEATABLE READ the same in PostgreSQL and MySQL?” - no. PostgreSQL’s is snapshot isolation with first-updater-wins: a lost update raises 40001. InnoDB’s mixes snapshot reads with current reads: the same lost update committed silently, and an UPDATE after a snapshot read applied to the latest row (160, not 110).
    • Follow-up: “Which one silently loses a counter increment?” - InnoDB at both RC and RR, PostgreSQL at RC. Use SET n = n + 1, a locking read, or PostgreSQL’s RR with retries.
  • “Name an anomaly READ COMMITTED allows that is not a phantom.” - read skew: a transfer read halfway showed 250 where the total was 200. REPEATABLE READ removes it in both engines.
  • “Write skew vs lost update?” - a lost update is two writes to the same row; write skew is writes to different rows that together break a rule. Snapshot isolation stops the first in PostgreSQL but not the second.
    • Follow-up: “Why can’t FOR UPDATE fix write skew on a row that does not exist yet?” - there is nothing to lock in PostgreSQL. InnoDB’s gap locks do cover it, turning the race into a deadlock.
  • “What are gap locks?” - InnoDB locks on the gaps between index entries, taken by locking reads and writes at REPEATABLE READ, so inserts into a read range wait. They prevent phantoms for locking reads and cause most of InnoDB’s deadlocks.
  • “How does SQLite handle concurrent writers?” - one writer at a time; a reader that tries to write after someone else committed gets SQLITE_BUSY_SNAPSHOT and must restart.
Previous
WAL, Checkpoints, and fsync