Transaction Isolation
Levels in DBMS
Mahdiyar Zerehpoush
De nition
Isolation levels de ne the degree to which a
transaction must be isolated from the data
modi cations made by any other transaction
in the database system.
fi
fi
fi
Phenomena
A transaction isolation level is de ned by the
following phenomena
• Dirty Read
• Non-Repeatable Read
• Phantom Read
fi
Dirty Read
A Dirty read is a situation when a transaction
reads data that has not yet been committed.
Non Repeatable read
Non-realatable read occurs when a
transaction reads the same row twice and
gets a di erent value each time.
ff
Phantom Read
Phantom Read occurs when two same
queries are executed, but the rows retrieved
by the two, are di erent.
ff
Isolation Levels
Based on these phenomena, The SQL
standard de nes four isolation levels:
• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable
fi
Read Uncommitted
Read Uncommitted is the lowest isolation
level.
In this level, one transaction may read not yet
committed changes made by other
transactions, thereby allowing dirty reads.
At this level, transactions are not isolated from
each other.
Read Committed
This isolation level guarantees that any data
read is committed at the moment it is read.
Thus it does not allow dirty read.
The transaction holds a read or write lock on
the current row, and thus prevents other
transactions from reading, updating, or
deleting it.
Repeatable Read
This is the most restrictive isolation level.
The transaction holds read locks on all rows it
references and writes locks on referenced
rows for update and delete actions.
Since other transactions cannot read, update
or delete these rows, consequently it avoids
non-repeatable read.
Serializable
This is the highest isolation level.
A serializable execution is guaranteed to be
serializable.
Serializable execution is de ned to be an
execution of operations in which concurrently
executing transactions appears to be serially
executing.
fi
Conclusion
The choice of isolation level depends on the
speci c requirements of the application.
Higher isolation levels o er stronger data
consistency but can also result in longer lock
times and increased contention, leading to
decreased concurrency and performance.
Lower isolation levels provide more
concurrency but can result in data
inconsistencies.
fi
ff
See Also
In addition to the standard isolation levels,
some DBMS may also support additional
custom isolation levels or features such as
snapshot isolation and multi-version
concurrency control (MVCC) that provide
alternative solutions to the problems
addressed by the standard isolation levels.
