Isolation levels

An isolation level is a database setting. It decides which concurrency anomalies a transaction may see. Stronger levels block more anomalies and cost more waiting or retries. The default in most databases does not block the withdrawal bug from the anomalies page, so the level you run at matters.

The SQL-92 standard defined four levels by which anomalies they forbid. A 1995 paper, A Critique of ANSI SQL Isolation Levels (Berenson et al.), showed those definitions were ambiguous. It also pointed out that they missed lost update and write skew, and it introduced snapshot isolation. Real databases now implement levels that do not map neatly onto the standard.

The levels, as they behave

Read this table with care. "Prevented (error)" means the database stops one transaction with an error, and your code must retry. "Possible" means no error is raised and the wrong result gets saved.

Write skew, step by step

Write skew is the subtlest anomaly. Two transactions read the same data, each makes a decision from what it read, and each writes a different row. Neither overwrites the other, so no conflict is detected, yet together they break a rule that each one checked.

The rule: at least one doctor must stay on call. Two doctors, Alice and Bob, are both on call (count = 2). Both feel ill and press "go off call" at the same time.

Under snapshot isolation, both commits succeed. Each transaction saw a count of 2, and they wrote different rows, so there was no write-write conflict to report. Zero doctors are on call.

Details that bite in practice

• Postgres read committed takes a fresh snapshot for each statement. An UPDATE ... WHERE re-checks its condition against the newest committed row after waiting for a lock.
• Postgres repeatable read keeps one snapshot for the whole transaction. If you update a row that changed after your snapshot, you get could not serialize access due to concurrent update and must retry.
• MySQL InnoDB repeatable read gives plain SELECTs a consistent snapshot. But UPDATE and locking reads use the latest committed data. You can read a stale value and write based on it with no error.
• Serializable differs by database. Postgres (since 9.1) uses Serializable Snapshot Isolation and aborts one transaction when it sees a dangerous pattern. MySQL makes plain SELECTs take shared locks, which blocks writers. Oracle's "serializable" is really snapshot isolation, so it allows write skew.

Common mistakes

• Assuming "serializable" means the same thing everywhere. Oracle's version still allows write skew.
• Assuming repeatable read stops lost updates in MySQL. A plain read from the snapshot, followed by an UPDATE based on it, can still lose an update.
• Trusting the default without checking. Run SHOW transaction_isolation; in Postgres to see what your session uses.
• Ignoring retries. Repeatable read and serializable can abort your transaction. Without a retry loop, that becomes a user-facing error.

Check yourself

1. Why does snapshot isolation allow write skew? The two transactions write different rows, so there is no write-write conflict to detect, even though each check relied on a rule that both writes break.
2. Which Postgres level turns a lost update into an error instead of a silent overwrite? Repeatable read. Updating a row that changed after your snapshot fails with a serialization error, and the app retries.

Key takeaways

• An isolation level is a list of allowed anomalies, and vendors use the same names for different behavior.
• Postgres defaults to read committed, MySQL to repeatable read, and CockroachDB to serializable.
• Snapshot isolation prevents lost updates in Postgres but still allows write skew.
• Serializable is correct but needs retries (Postgres) or blocking (MySQL two-phase locking).

Further learning

The Hermitage project tests each database's real behavior against every anomaly. It is the best check when a vendor's documentation is vague.

GitHub - ept/hermitage: What are the differences between the transaction isolation levels in databases? This is a suite of test cases which differentiate isolation levels.What are the differences between the transaction isolation levels in databases? This is a suite of test cases which differentiate isolation levels. - ept/hermitageGitHub

Next, read MVCC and snapshots to see how a snapshot is built without blocking anyone.