Exploring Database Isolation Levels

A Deep Dive into Anomalies

Jan 15, 2025

📅 Last updated: October 2, 2025

☕ Welcome to The Coder Cafe! Today, we will explore the main database isolation levels, detailing exactly which anomalies each one prevents. Get cozy, grab a coffee, and let’s begin!

In a previous issue (Isolation Level), we introduced the concept of isolation level. In short, an isolation level refers to the degree to which concurrent transactions1 are isolated from each other. Understanding the different levels is important for any software engineer working with databases. As I mentioned previously, I have already faced significant problems with customer data because we didn’t properly tune our PostgreSQL config.

In this issue, we will explore the different isolation levels and the anomalies each one prevents. In this context, an anomaly refers to an undesirable behavior that can occur when multiple transactions execute concurrently. Said differently, if something unexpected happened because we have two transactions running at the same time, we faced an anomaly.

Here are the different isolation levels and all the anomalies they prevent:

Flowchart illustrating database isolation levels from Weakest/Fastest (No isolation) to Strongest/Slowest (Serializability). Each higher level prevents additional anomalies: Read Uncommitted prevents dirty writes, Read Committed prevents dirty reads, Repeatable Reads/Snapshot Isolation prevent fuzzy reads, SSI prevents write skews, and Serializability prevents phantom reads.

For example:

The read uncommitted level prevents dirty writes
The read committed level prevents dirty reads and dirty writes as it’s built on top of read uncommitted

The higher the isolation level and the more anomalies are prevented. Yet, as with anything in computer science, it’s not free and it comes at a cost of reduced performance. For example, in a benchmark I observed a drop of around 80% in throughput by tuning the isolation level2.

No Isolation

Anomalies prevented: none.

The no isolation level implies that we don’t impose any isolation at all. This is only suitable in scenarios where we can ensure no concurrent transactions will occur. In practice, this is quite rare.

Read Uncommitted

Anomalies prevented: dirty writes.

Dirty Writes

A dirty write occurs when a transaction overwrites a value that was previously written by another transaction that is still in flight3. If either of these transactions rollback, it’s unclear what the correct value should be.

To illustrate this anomaly, let’s consider a banking account database with two transactions doing concurrent operations on the same account of Alice:

Initial state:
- Alice’s balance: $0
Transactions:
- T1 adds $100 to Alice’s account but will rollback
- T2 adds $200 to Alice’s account
Expected balance: $200

Here’s a possible scenario with a database that doesn’t prevent dirty writes (the operation that causes problems is highlighted in red):

A timeline diagram showing a Dirty Write anomaly between two transactions, T1 and T2. T1 starts, reads Alice's balance ($0), and updates it to $100. T2 then reads the uncommitted $100 balance and updates it to $300 (highlighted in red). T1 then Rolls back its change, and T2 Commits $300, resulting in an incorrect final balance of $300 instead of the expected $200. — Dirty write anomaly

At the end of this scenario, Alice’s balance is $300 instead of $200. This is a dirty write. Dirty writes are especially bad as they can violate database consistency4. In general, databases prevent dirty writes by locking rows that will be written until the end of the transaction.

If our database is tuned with the read uncommitted isolation level or higher, we won’t face dirty write anomalies. Otherwise, dirty writes may occur.

Read Committed

Anomalies prevented: dirty reads.

Dirty Reads

A dirty read occurs when one transaction observes a write from another transaction that has not been committed yet. If the initial transaction rollbacks, the second transaction has read data that was never committed.

Let’s consider the same banking account example:

Initial state:
- Alice’s balance: $0
Transactions
- T1 adds $100 to Alice’s account but will rollback
- T2 reads Alice’s balance
Expected T2 read: $0

Here’s a possible scenario with a database that doesn’t prevent dirty reads:

A timeline diagram illustrating a Dirty Read anomaly. Transaction T1 starts, reads Alice's balance ($0), and updates it to $100 but does not commit. Transaction T2 then performs a read (highlighted in red) and sees the uncommitted balance of $100. T1 immediately Rolls back its change. T2 has read data that was never officially committed to the database. — Dirty read anomaly

The balance read by T2 is $100, which corresponds to a dirty reads. Indeed, $100 on Alice’s account is something that never really existed as it was rollbacked by T1. With dirty reads, decisions inside transactions can be taken based on data updates that can be rolled back, which can lead to consistency violations, just like dirty writes.

At the read-committed isolation level, databases address this problem by ensuring that any data read during a transaction is committed at the time of reading.

Repeatable Reads

Anomalies prevented: fuzzy reads.

Fuzzy Reads

A fuzzy read occurs when a transaction reads a value twice but sees a different value in each read because a committed transaction updated the value between the two reads.

Let’s again consider the same banking account example:

Initial state:
- Alice’s balance: $0
Transactions:
- T1 reads Alice’s balance one time and then a second time
- T2 adds $100 to Alice’s balance
Expected T1 reads should be consistent: either $0 and $0 or $100 and $100, depending on the execution order

Here’s a possible scenario with a database that doesn’t prevent fuzzy reads:

A timeline diagram illustrating a Fuzzy Read anomaly (also known as a non-repeatable read). Transaction T1 starts and reads Alice's balance as $0. Concurrent transaction T2 reads $0, updates the balance to $100, and Commits. T1 then performs a second read (highlighted in red) and sees a different value of $100, meaning the same transaction got inconsistent results for the same query. — Fuzzy read anomaly

Here the problem is that T1 reads Alice’s balance two times and got two different results: $0 and $100. This is the reason why fuzzy reads are also called non-repeatable reads. We can easily see the impact of fuzzy reads, if a transaction doesn’t expect two reads for the same value not to be the same, it can lead to logical errors or even rule violations.

At the repeatable reads isolation level, databases, in general, prevent fuzzy reads by ensuring that a transaction locks the data it reads, preventing other transactions from modifying it.

Snapshot Isolation (SI)

Anomalies prevented: fuzzy reads, lost updates, and read skew.

We already introduced fuzzy reads in the context of the repeatable reads isolation level. Therefore, if we want to prevent fuzzy reads, we have to choose at least the repeatable read or SI isolation level.

One thing to note, though, is that preventing fuzzy reads at both isolation levels is done differently. We said that at repeatable reads level, transactions lock the data it reads. Yet, snapshot isolation works with consistent snapshot of the database at the start of the transaction, isolating it from changes made by other transactions during its execution. This technique also helps to prevent lost updates and read skew, which we will introduce now.

Lost Updates

A lost update occurs when two transactions read the same value, try to update it to two different values but only one update survives.

Same banking account example:

Initial state:
- Alice’s balance: $0
Transactions:
- T1 adds $100 to Alice’s account
- T2 adds $200 to Alice’s account
Expected balance: $300

Here’s a possible scenario with a database that doesn’t prevent lost updates:

A timeline diagram illustrating a Lost Update anomaly. Both transactions, T1 and T2, start by reading Alice's balance as $0. T1 updates the balance to $100 and Commits. T2 then performs its update (highlighted in red) based on its original read of $0, setting the balance to $200 and Committing. T1's update of $100 is overwritten and lost, resulting in an incorrect final balance of $200 instead of the expected $300. — Lost update anomaly

The final balance is $200, which reflects T2’s write, but T1’s update of +$100 is lost. This illustrates why this anomaly is called a lost update and it’s problematic as it can lead to incorrect data, violating the expectation that both additions should be applied to Alice’s account.

At the SI level, as transactions work with a consistent snapshot of data, the database can detect a conflict if two transactions modify the same data and typically rollback the transaction.

Read Skew

A read skew occurs when a transaction reads multiple rows and sees a consistent state for each individual row but an inconsistent state overall because the rows were read at different points in time with respect to changes made by other transactions.

If you understood the definition right away, congratulations. Otherwise, let’s make it clearer with a banking database example and consider two accounts, Alice and Bob:

Initial state:
- Alice’s balance: $100
- Bob’s balance: $0
Transactions:
- T1 reads Alice’s and Bob’s balance
- T2 transfers $100 from Alice’s account to Bob’s account
Expected T1 reads should be consistent: $100 for Alice and $0 for Bob or $0 for Alice and $100 for Bob depending on the execution order

Here’s a scenario that illustrates a read skew anomaly (the transfer operation is simplified for readability reasons):

A timeline diagram illustrating a Read Skew anomaly using Alice ($100) and Bob ($0) bank balances. Transaction T1 starts by reading Alice's balance as $100. Concurrent transaction T2 then transfers $100 from Alice to Bob and Commits. T1 then reads Bob's balance (highlighted in red) and sees $100. T1 has observed an inconsistent state where both balances are $100, totaling $200. — Read skew anomaly

Before and after these transactions, the sum of Alice’s and Bob’s balances is $100; yet, this isn’t what was observed by T1 in this scenario. Indeed for T1, Alice’s and Bob’s balance are both $100 which represents a sum of $200. This illustrates a read skew: a consistent state for each individual balance, but an inconsistent state overall with the total balance. Read skew can also lead to consistency rules to be violated.

At the SI level, snapshots also allow to prevent read skews as in this example, T1 would work on a consistent snapshot, leading to consistent data.

Serializable Snapshot Isolation (SSI)

Anomalies prevented: write skew.

Write Skew

A write skew may occur when two transactions read the same data, make decisions based on that data, and then update that data.

For this anomaly, let’s consider an oncall scheduling system for doctors at a hospital. The hospital enforces a consistency rule that at least one doctor must be oncall at any given time:

Initial state:
- Alice and Bob are both oncall
- They both feel unwell
Transactions:
- Both decide to ask the system if they can leave: T1 represents Alice’s request and T2 represents Bob’s request
Expected result: Only Alice’s or Bob’s request is accepted

Now, let’s consider the following scenario that illustrates a write skew:

A timeline diagram illustrating a Write Skew anomaly in a doctor on-call system that requires at least one doctor. Both transactions, T1 (Alice) and T2 (Bob), start by reading the on-call count as 2. Both then determine it's safe to leave. T1 sets Alice to on-call=false and Commits. T2 then sets Bob to on-call=false and Commits (highlighted in red). The final state is 0 on-call doctors, violating the consistency rule because the decisions were based on a stale initial read. — Write skew anomaly

In this scenario, both transactions take the decision to accept the sick leave because they read a count of oncall doctors greater than 1. It results in a violation of the consistency rule to keep always at least one oncall doctor.

SSI is an advanced approach that extends SI. Usually, databases tuned at SSI prevent write skew by introducing additional checks during the commit phase, which ensures that the changes made by different transactions do not lead to inconsistent or conflicting states.

Serializability

Anomalies prevented: phantom reads.

Phantom Reads

A phantom read occurs when a transaction does multiple predicate-based read, while another transaction creates or deletes data that matched the predicate of the first transaction. A phantom read is a special case of fuzzy read (non-repeatable read).

Let’s consider the example of a database tracking the students of computer science courses. The predicate in this scenario will be WHERE course = ‘Algo101‘:

Initial state:
- Course Algo101 has a certain number of students
Transactions:
- T1 selects the max age and then the average age of students in the Algo101 course
- T2 updates the list of students to the Algo101 course
Expected T1 reads to be consistent

Here’s a scenario that illustrates phantom reads:

A timeline diagram illustrating a Phantom Read anomaly. Transaction T1 starts by selecting the max age of students for a course. Concurrent transaction T2 then adds new students to the same course and commits its change. T1 then performs a second query to select the average age (highlighted in red) for the same course. The result set of T1's second query is different from the first because the phantom rows added by T2 appeared between the two reads. — Phantom read anomaly

In this scenario, T2 affected T1’s result set. T1 first query returned a given result set that was different from the second query. This could lead to consistency rules violation as well. For example, if T2 inserts many “old“ students, T1 might as well compute an average age that could be higher than the max age. This is a phantom read: when the set of rows satisfying a predicate changes between reads within the same transaction due to another transaction.

The serializability isolation level ensures the same result as if transactions were executed in a sequential order, meaning not concurrently. Therefore, serializability prevents phantom reads by guaranteeing that no new rows are inserted, updated, or deleted in a way that would change the result set of the transaction’s query during its execution.

Serializability is the highest possible isolation level, and it refers to the I of ACID.

NOTE: There’s also the concept of strict serializability, which is even stronger than serializability. Strict serializability is not strictly an isolation level but rather exists at the intersection of isolation levels and consistency models. It combines the properties of serializability and linearizability to ensure both a serial order of transactions and a respect for real-time ordering. We will address this concept in a future issue.

Conclusion

Let’s summarize all the anomalies:

Dirty writes: When a transaction overwrites values that was previously written by another in-flight transaction.
Dirty reads: When a transaction observes a write from another in-flight transaction.
Fuzzy reads: When a transaction reads a value twice but sees a different value.
Lost updates: When two transactions read the same value, try to update it to different values, but only one update survives.
Read skews: When a transaction reads multiple rows and sees a consistent state for each individual row but an inconsistent state overall.
Write skews: When two transactions read the same data, and then update some of this data.
Phantom reads: When the set of rows satisfying a predicate changes between reads within the same transaction due to another transaction

As I mentioned, understanding the different isolation levels should be an important skill for any software engineer working with databases. While serializability offers the highest isolation guarantees, it can have a significant impact on performance, especially in high-concurrency systems. Therefore, it’s crucial to understand the anomalies that can occur at each isolation level and evaluate which ones are acceptable for our system. Sometimes, a system might tolerate certain anomalies in exchange for improved throughput.

By making informed decisions about the appropriate isolation level, we can balance the trade-offs between data consistency and system performance based on the needs of our systems.

The Coder Cafe: Learn One Concept With Your Coffee.

📚 Resources

Sources

Explore Further

A Critique of ANSI SQL Isolation Levels

❤️ If you enjoyed this post, please hit the like button.

💬 I’ve wanted to write about database isolation levels for years, and it’s been a lot of fun for me. If you enjoyed this post, I would love to hear from you! I think a similar deep dive into consistency models could be just as important, so let me know if you’d be interested in that as well.

2 Comments

Ready for more?