What Anomalies Distinguish SQL Isolation Levels?

Database isolation levels define how concurrent transactions interact and which data inconsistencies are permitted. In classical relational computing, four standard isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—are distinguished by four key concurrency anomalies: dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. Understanding which anomalies each level allows or prevents helps developers balance data consistency against system throughput.

Dirty Reads and Read Uncommitted

A dirty read occurs when a transaction reads data written by another concurrent transaction that has not yet committed. If the writing transaction later rolls back, the reading transaction has acted on invalid data that never officially existed in the database.

• Read Uncommitted is the lowest isolation level and permits dirty reads, along with all other anomalies.
• Read Committed and all higher levels prevent dirty reads by ensuring transactions only read committed data.

Non-Repeatable Reads and Read Committed

A non-repeatable read (also called a fuzzy read) happens when a transaction reads the same row twice and gets different values because another transaction committed an update or deletion to that row in between the two reads.

• Read Committed prevents dirty reads but still allows non-repeatable reads, because each statement sees a fresh snapshot of committed data.
• Repeatable Read and Serializable prevent non-repeatable reads by guaranteeing that once a transaction reads a row, subsequent reads of that same row within the transaction return the same values.

Phantom Reads and Repeatable Read

A phantom read occurs when a transaction executes a query with a search condition (such as a WHERE clause) twice and receives a different set of rows the second time because another transaction committed an INSERT or DELETE affecting rows that match the condition.

• Repeatable Read prevents dirty reads and non-repeatable reads, but under the ANSI SQL-92 standard, it still permits phantom reads. (Some modern database engines using Multi-Version Concurrency Control, or MVCC, also prevent phantoms at this level, though the standard does not require it.)
• Serializable prevents phantom reads by ensuring range and predicate locks—or equivalent conflict detection—block concurrent modifications that would alter the result set of a repeated query.

Serialization Anomalies and Serializable

A serialization anomaly occurs when the combined result of concurrently executing transactions cannot be reproduced by running those same transactions in any sequential order. A classic example is write skew, where two transactions each read an overlapping set of data, make decisions based on what they read, and then update disjoint rows, violating a business invariant.

• Serializable is the strictest isolation level and prevents all four anomalies, including serialization anomalies. It guarantees that the outcome of concurrent transactions is equivalent to some serial execution order.

Summary of Isolation Levels