Isolation Level – Summarized.
Data concurrency: means that many users can access data at the same time.
Data consistency: means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
Isolation : is a property that defines how/when the changes made by one operation become visible to other concurrent operations. Isolation is one of the ACID property.
Lower isolation levels increase transaction concurrency at the risk of allowing transactions to observe a fuzzy or incorrect database state. These incorrect state you need to manage at application design.
4 Isolation Levels:
The ANSI/ISO SQL-92 specifications define four isolation levels:
(1) READ UNCOMMITTED.
(2) READ COMMITTED.
(3) REPEATABLE READ.
Lower Isolation level —> Higher concurrency, Data consistancy low, Reducing the locking overhead.
Higher Isolation Level —> Lower Concurrency, High Data Consistancy, Possible More Deadlock in multi user enviorment.
Three preventable phenomena
P1 (Dirty Read): Transaction T1 modifies a data item. Another transaction T2 then reads that data item before T1 performs a COMMIT or ROLLBACK. If T1 then performs a ROLLBACK, T2 has read a data item that was never committed and so never really existed.
P2 (Non-repeatable or Fuzzy Read): Transaction T1 reads a data item. Another transaction T2 then modifies or
deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified value or discovers
that the data item has been deleted.
P3 (Phantom): Transaction T1 reads a set of data items satisfying some
then creates data items that satisfy T1’s
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible