SQL Isolation Levels

What each isolation level promises, which anomalies slip through (dirty reads, lost updates, read skew, phantoms, write skew), and how PostgreSQL and MySQL actually implement them.

Overview

A transaction groups several reads and writes so that they succeed or fail together. When many transactions run at once, a second question comes up: how much can one transaction see of the others while they are still running? The isolation level is the answer. It is a promise about which strange results, called anomalies, can and cannot happen.

The SQL standard names four levels: Read Uncommitted, Read Committed, Repeatable Read and Serializable. Each forbids more anomalies than the one before and usually costs more, in waiting, in aborted transactions, or both. Only Serializable promises that the result is the same as running the transactions one at a time, in some order.

The catch is that the names do not mean the same thing in every database. The standard defines the levels by three anomalies, and a well-known 1995 paper by Berenson, Bernstein, Gray and others showed those definitions are ambiguous and miss important cases, such as snapshot isolation. PostgreSQL's Repeatable Read, MySQL's Repeatable Read and Oracle's Serializable all behave differently. This chapter explains the anomalies, what each level really gives you in PostgreSQL and MySQL, and how to write code that stays correct.

What you will be able to do

  • Describe each anomaly with a two-transaction example, including lost update and write skew.
  • Say which anomalies each level allows in PostgreSQL and in MySQL's InnoDB.
  • Explain snapshot isolation and why it still allows write skew.
  • Explain how PostgreSQL's Serializable level (SSI) catches write skew, and why the application must retry.
  • Pick a level for a feature and defend the choice in an interview.

Key concepts

  • Dirty read
  • Non-repeatable read
  • Phantom read
  • Lost update
  • Write skew
  • Read Committed
  • Snapshot isolation
  • Serializable (SSI)
  • Retrying 40001 errors

Interview questions

  • Explain the four SQL isolation levels.
  • What is write skew, and how do you prevent it?
  • How does PostgreSQL implement Serializable?
  • How do MySQL and PostgreSQL differ at Repeatable Read?
  • Does Serializable mean transactions run one at a time?
  • Which isolation level would you use for a bank transfer?
  • What is a phantom read, and how do databases prevent it?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio