Isolation Anomalies Across Databases — Postgres vs MySQL vs SQLite

The same isolation level name (REPEATABLE READ) means different things in different databases. Senior interviews probe whether you actually understand the anomalies, not just the names.

The anomalies (recap)

ANSI SQL isolation levels

                      Dirty   Non-repeat  Phantom
READ UNCOMMITTED      yes     yes         yes
READ COMMITTED        no      yes         yes
REPEATABLE READ       no      no          yes
SERIALIZABLE          no      no          no

But implementations differ wildly from this spec.

Postgres

Default: READ COMMITTED.

Notable: Postgres’ REPEATABLE READ is stronger than the ANSI definition. Snapshot isolation. But it still allows write skew.

Write skew example in Postgres

-- T1                          -- T2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors
WHERE on_call = true;
-- returns 2
                              BEGIN ISOLATION LEVEL REPEATABLE READ;
                              SELECT count(*) FROM doctors
                              WHERE on_call = true;
                              -- also returns 2 (same snapshot)

UPDATE doctors SET on_call = false
WHERE id = 1;
COMMIT;
                              UPDATE doctors SET on_call = false
                              WHERE id = 2;
                              COMMIT;

-- Both commit; now 0 doctors on call.
-- Constraint violated.

Both transactions saw 2 doctors; each removed one. Each thinks “the other doctor is still there.” Now there are 0. SSI (SERIALIZABLE) detects this and aborts one transaction.

Postgres SERIALIZABLE

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... transaction work ...
COMMIT;     -- may raise serialization_failure (SQLSTATE 40001)

You must handle serialization failures by retrying the whole transaction:

for attempt in range(MAX_RETRIES):
    try:
        with conn.transaction():
            do_work()
        break
    except SerializationFailure:
        if attempt == MAX_RETRIES - 1:
            raise
        time.sleep(backoff(attempt))

SERIALIZABLE on Postgres is “optimistic” — it commits things, then checks at commit time. Throughput cost depends on conflict rate.

MySQL (InnoDB)

Default: REPEATABLE READ. Different mechanism than Postgres.

MySQL’s REPEATABLE READ — gap locks

To prevent phantoms in REPEATABLE READ, MySQL takes gap locks during certain reads:

SELECT * FROM orders WHERE user_id BETWEEN 10 AND 20 FOR UPDATE;

MySQL locks the range [10, 20] plus the gaps. Concurrent inserts of new rows in that range block.

Consequence: locking conflicts in MySQL are far more common than in Postgres. Two transactions touching disjoint rows can deadlock via gap locks.

Postgres doesn’t take gap locks; SSI catches the equivalent issue at commit time instead.

Write skew in MySQL REPEATABLE READ

MySQL’s REPEATABLE READ does prevent phantoms (via gap locks), but write skew can still occur depending on which rows are read with locks. SERIALIZABLE in MySQL converts SELECT to read-locking, which prevents most write skew at heavy lock-conflict cost.

SQLite

Different model entirely. SQLite is single-writer, multi-reader.

In WAL mode:

• Readers see a consistent snapshot from when they began (SERIALIZABLE-like).
• Writers serialize via BEGIN EXCLUSIVE or BEGIN IMMEDIATE.
• No phantoms; no write skew between concurrent writers (only one writer).

SQLite is effectively SERIALIZABLE in WAL mode, because writers can’t be concurrent. Throughput is bounded but consistency is strong.

Oracle

Oracle has the same default as Postgres — READ COMMITTED — and is also MVCC-based. Its SERIALIZABLE is more permissive than Postgres’; it’s roughly snapshot isolation without the SSI checks. So Oracle’s SERIALIZABLE allows write skew, contrary to the ANSI name.

When migrating between Postgres and Oracle, audit isolation assumptions.

Comparative summary

When write skew bites real systems

Classic scenarios:

• “Always at least one doctor on call” with concurrent goes-off-call updates.
• “Username uniqueness” via two separate SELECTs without unique constraint.
• “Wallet balance can’t go negative” with concurrent debits.
• “Schedule slot capacity” with concurrent bookings.

Fixes:

1. Use SERIALIZABLE (Postgres SSI handles it).
2. Use SELECT … FOR UPDATE to escalate the read to a row lock — manual approach to forcing serialization on the rows you read.
3. Use database constraints (unique constraints, exclusion constraints) — let the DB enforce invariants the application can’t reliably check.
4. Use advisory locks for explicit cross-transaction coordination.

Practical recommendations

For Postgres:

• Default READ COMMITTED is usually fine for typical OLTP.
• Use SERIALIZABLE for specific transactions with multi-row invariants (booking, wallet, scheduling). Wrap them with retry logic.
• Use SELECT FOR UPDATE for narrower locking when you only need to serialize on specific rows.
• Use unique / exclusion constraints to enforce invariants the DB can check directly.

For MySQL:

• Default REPEATABLE READ provides stronger isolation than Postgres’ RC, but at the cost of more locking.
• Watch for gap-lock deadlocks — they’re a frequent MySQL footgun.
• READ COMMITTED is often used in MySQL OLTP for less locking and better concurrency, at the cost of phantom reads.
• For write skew, SERIALIZABLE works but is heavy; explicit SELECT FOR UPDATE is usually preferred.

For SQLite:

• Use WAL mode (PRAGMA journal_mode=WAL).
• Accept single-writer; design for low write contention.
• Effectively SERIALIZABLE; less to worry about beyond write capacity.

Interview angle

• “Default isolation levels for Postgres and MySQL?” — Postgres: READ COMMITTED. MySQL: REPEATABLE READ. Different mechanisms (Postgres MVCC; MySQL MVCC + gap locks) and different anomaly profiles.
• “What’s write skew?” — two transactions read overlapping data, each writes to different (but related) rows; both commit, violating an aggregate invariant. Famous example: doctors-on-call — both transactions see 2 doctors on call, each removes one, ending with 0.
• “Does Postgres REPEATABLE READ prevent write skew?” — no. It’s snapshot isolation; prevents phantoms but allows write skew. Need SERIALIZABLE (SSI) for write skew prevention.
• “What does Postgres’ SERIALIZABLE actually do?” — Serializable Snapshot Isolation (SSI). Tracks predicate dependencies between transactions; aborts at commit if the schedule isn’t serializable. Optimistic — better than pessimistic locking for low conflict rates but throws serialization_failure that you must retry.
• “MySQL gap locks?” — under REPEATABLE READ, MySQL takes locks on gaps in the index range it’s reading to prevent phantom inserts. Side effect: locking conflicts and deadlocks more common than in Postgres. Often the actual reason a MySQL workload feels “more deadlocky” than equivalent Postgres.
• “Where would SQLite NOT serialize?” — SQLite (WAL mode) only allows one writer at a time; concurrent writes serialize. Concurrent readers + one writer is fine. Effectively SERIALIZABLE; the trade-off is throughput, not anomalies.
• “Same isolation level name, different behaviors. Example?” — REPEATABLE READ in Postgres = snapshot isolation (no phantoms, write skew possible). In MySQL = snapshot + gap locks (no phantoms, fewer write skews, more deadlocks). In Oracle, the name is similar but with different semantics again. Always check the engine’s docs, not just the level name.