Prevention of Anomalies in Isolation Levels
In this lesson, we will identify which isolation level prevents which anomalies.
We'll cover the following
Isolation level that prevents all of the anomalies
There is one isolation level that prevents all of these anomalies: the serializable one.
Like the consistency models presented in the Consistency Models lesson, this level provides a more formal specification of what is possible, e.g., which execution histories are possible. More specifically, it guarantees that the result of the execution of concurrent transactions is the same as that produced by some serial execution of the same transactions. This means that we can only analyze serial executions for defects. If all the possible serial executions are safe, then any concurrent execution by a system at the serializable level will also be safe.
However, serializability has performance costs since it intentionally reduces concurrency to guarantee safety.
Other isolation levels
Isolation levels other than the serializable ones are less strict and provide better performance via increased concurrency at the cost of decreased safety.
These models allow some of the anomalies we described previously. The following illustration contains a table with the most basic isolation levels, along with the anomalies they prevent.
Isolation levels and prevented anomalies
Dirty Writes | Dirty Reads | Fuzzy Reads | Lost Updates | Read Skew | Write Skew | Phantom Reads | |
Read Uncommitted | not possible | possible | possible | possible | possible | possible | possible |
Read Committed | not possible | not possible | possible | possible | possible | possible | possible |
Snapshot Isolation | not possible | not possible | not possible | not possible | not possible | possible | possible |
Repeatable Read | not possible | not possible | not possible | not possible | not possible | not possible | possible |
Serializability | not possible | not possible | not possible | not possible | not possible | not possible | not possible |
These isolation levels originated from the early relational database systems that were not distributed. Still, they are applicable in distributed datastores too.
Note: Based on the definition of Snapshot Isolation, one can think of how phantom reads are possible in this isolation level. This is due to the practical implementation of snapshot isolation using
, discussed in the lesson on Achieving Snapshot Isolation. multi-version concurrency control (MVCC) It is a technique where multiple physical versions are maintained for a single logical data item. As a result, update operations do not overwrite existing records, but they just write new version of these records. Read operations can then select a specific version of a record, possibly an older one, but it can also select the newer version of the data due to which phantom reads may occur.
Get hands-on with 1400+ tech skills courses.