Interview Prep Zoneby CuriouserLabs

Question 3 of 5 · staff level

Walk through isolation levels and their anomalies. What do real databases actually give you?

🎤 Say this first

Levels trade consistency for concurrency: READ_UNCOMMITTED (dirty reads) → READ_COMMITTED (no dirty reads; non-repeatable reads remain) → REPEATABLE_READ (stable rows; phantoms possible in the standard) → SERIALIZABLE (as-if-sequential). Reality check: Postgres/Oracle default to READ_COMMITTED via MVCC (readers never block writers); MySQL InnoDB defaults to REPEATABLE_READ; Postgres has no read-uncommitted at all, and its SERIALIZABLE is optimistic (SSI — expect serialization-failure retries). Most systems run READ_COMMITTED + explicit locking/optimistic versioning where it matters.

The full picture

LevelDirty readNon-repeatable readPhantom
READ_UNCOMMITTEDpossiblepossiblepossible
READ_COMMITTED—possiblepossible
REPEATABLE_READ——possible (std) / mostly prevented in InnoDB
SERIALIZABLE———
  • The anomaly that matters in practice: lost update / write skew under READ_COMMITTED — two transactions read a balance, both compute, last write wins. Isolation levels alone don't save you; you need optimistic versioning (@Version), atomic UPDATE…WHERE, or SELECT FOR UPDATE.
  • MVCC intuition: each transaction sees a snapshot; writers create row versions instead of blocking readers — why 'readers block writers' folklore is wrong on Postgres/Oracle.
  • Spring specifics: @Transactional(isolation=…) applies per transaction (SET TRANSACTION); on a joined transaction the setting is ignored (or rejected with validateExistingTransaction). Also: isolation is a DB concept — don't confuse with propagation.
  • SERIALIZABLE operationally means retry loops: catch serialization failures (SQLState 40001) and re-run — infrastructure your service must own if you choose that level.

🔄 Likely follow-up questions

  • Show a write-skew example that REPEATABLE_READ doesn't prevent but SERIALIZABLE does.
  • How does InnoDB's gap locking change the phantom story?
  • When would you actually raise isolation vs add explicit locking?