Transaction Isolation Levels

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 un-committed Allowed Allowed Allowed
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s