Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

Explain the SQL isolation levels and the anomalies each one allows.

What the interviewer wants to hear

  • READ UNCOMMITTED allows dirty reads
  • READ COMMITTED prevents dirty reads but allows non-repeatable reads
  • REPEATABLE READ gives a stable snapshot; phantoms depend on the engine
  • SERIALIZABLE prevents all anomalies including write skew
  • Defaults: PostgreSQL READ COMMITTED, MySQL InnoDB REPEATABLE READ
  • Stricter levels may need retry logic

Model answer

The SQL standard defines four levels. READ UNCOMMITTED can see other transactions' uncommitted changes, called dirty reads; PostgreSQL treats it as READ COMMITTED. READ COMMITTED, the PostgreSQL default, only sees committed data, but each statement gets a new snapshot, so reading a row twice can give different results. REPEATABLE READ, the InnoDB default, keeps one snapshot for the whole transaction. In PostgreSQL this also prevents phantoms; InnoDB uses next-key locks for locking reads. Both can still allow write skew, where two transactions read the same data and make conflicting decisions. SERIALIZABLE makes the result equal to some serial order; PostgreSQL uses SSI and aborts conflicting transactions. Higher levels are safer but cause more aborts, so applications must retry on serialization errors.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions