SQL Isolation Levels Deep Dive

How PostgreSQL, MySQL InnoDB and SQL Server actually build each isolation level, with snapshots, row locks, gap locks and conflict checks, and what that means for blocking, errors and retries.

Overview

The SQL standard says which anomalies each isolation level must prevent. It does not say how. Every engine picks its own machinery, and that machinery, not the level's name, decides what your application sees: whether a query waits, whether it gets an error, and which bugs remain possible.

There are two basic tools. Locks make a conflicting transaction wait. Snapshots, built on multi-version concurrency control (MVCC), let each transaction read a fixed committed state while others keep writing. PostgreSQL builds all its levels from snapshots plus conflict checks. MySQL's InnoDB mixes snapshots for plain reads with row and gap locks for writes and locking reads. SQL Server uses locks by default and snapshots when you turn them on.

This chapter goes level by level through those three engines. It assumes you know the anomalies themselves (dirty read, non-repeatable read, phantom, lost update, write skew); the SQL Isolation Levels topic introduces them.

What you will be able to do

  • Explain how a lock-based and a snapshot-based engine each prevent the same anomaly, and what each costs.
  • Describe PostgreSQL's Read Committed re-check, Repeatable Read's first-updater rule, and how SSI detects write skew.
  • Explain InnoDB's consistent reads, locking reads and next-key locks, and why its Repeatable Read behaves differently from PostgreSQL's.
  • Say what SQL Server's READ_COMMITTED_SNAPSHOT and SNAPSHOT options change.
  • Name the error each engine raises when a transaction must be retried.

Key concepts

  • Locking vs snapshots
  • Statement vs transaction snapshot
  • Re-check on update
  • First updater wins
  • SSI and SIRead locks
  • Consistent and locking reads
  • Gap and next-key locks
  • Read Committed Snapshot
  • Serialization failures

Interview questions

  • How does PostgreSQL implement each isolation level?
  • Why can a lost update happen at MySQL's Repeatable Read but not at PostgreSQL's?
  • Explain gap locks and next-key locks.
  • How does SSI detect write skew?
  • What does InnoDB's Serializable level change, and what does it cost?
  • What are READ_COMMITTED_SNAPSHOT and SNAPSHOT in SQL Server?
  • Which isolation level would you choose for an inventory reservation service?
  • How would you prove which anomalies your database allows?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio