I always get confuse about transaction isolation levels and different terms associated with that. Here I manage to get some understanding about these:-
Three type of problem can occur when there are many concurrent transaction exist in the system:
- Dirty Read :- A dirty read occurs when a transaction reads data from a row that has been modified by another transaction, but not yet committed.
- Non Repeatable Read :- This occurs when the same data read operation returns different result (of a field).
- Phantom Read : This occurs when we have already read a range of data based upon a key value and another transaction inserts a new row which happens to have a value between this range. The original range which was referred to would now become invalid. This is because a “Phantom row” was added. This is also a major problem.
Here is a table describing different Isolation levels and their locking behaviour:
|Isolation level||Dirty Read||Non Repeatable read||Phantom read|
|Read Committed||Not allowed||Allowed||Allowed|
|Repeatable||Not Allowed||Not Allowed||Allowed|
|Serializable||Not Allowed||Not Allowed||Not Allowed|
On the basis of this table we can say that:-
- Read Uncomitted:- No locking in required.
- Read Committed:- Read only committed data, but don’t read the value modified by other concurrent transaction or rows added by other transaction. In this isolation level, read locks are acquired on selected data but they are released immediately whereas write locks are released at the end of the transaction. Only committed data can be read and read lock is acquired in starting of the transaction.
- Repeatable:-Read committed data and read changes on the selected rows only.All data records read by a SELECT statement cannot be changed; however, if the SELECT statement contains any ranged WHERE clauses, phantom reads may occur. In this isolation level the transaction acquires read locks on all retrieved data, but does not acquire range locks. Keep lock on selected rows.
- Serilizable:-This iisolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions executed serially, one after the other. Keep lock on all rows in the table.