Definition of Isolation Levels
The "I" in ACID
stands for isolation. The degree of
isolation of a transaction determines how much or little other concurrent transactions can
affect the data that it operates on.
The SQL:1992
StandardTx1
and Tx2
:
Dirty read
– This occurs whenTx1
modifies an item, and thenTx2
reads that item beforeTx1
has committed the change. Then, ifTx1
never succeeds in committing the change, or rolls it back,Tx2
has read a value that never made it into the database.Non-repeatable read
– This happens whenTx1
reads an item, thenTx2
modifies or deletes that item and commits the change, and thenTx1
tries to reread the item.Tx1
now reads a different value than before, or finds that the item no longer exists.Phantom read
– This happens whenTx1
reads a set of items that satisfy a search criterion, and thenTx2
adds a new item that satisfies the search criterion, and thenTx1
repeats the search.Tx1
now obtains a different set of items than it did before.
Each of these three types of interaction can cause inconsistencies in the resulting data in a database.
The SQL:1992 standard defined four isolation levels that have different guarantees in terms of the three types of interaction and the inconsistencies that they can produce. At all four levels, a transaction can be guaranteed to execute completely or not at all:
READ UNCOMMITTED
– Allows all three kinds of interaction (that is, dirty reads, non-repeatable reads, and phantom reads).READ COMMITTED
– Dirty reads are not possible, but nonrepeatable and phantom reads are.REPEATABLE READ
– Neither dirty reads nor nonrepeatable reads are possible, but phantom reads still are.SERIALIZABLE
– None of the three types of interaction phenomena can occur.
Multiversion concurrency control (MVCC) allows one other kind of isolation, namely SNAPSHOT isolation. This guarantees that a transaction operates on a snapshot of data as it exists when the transaction begins, and that no other transaction can change that snapshot.