Differences of Isolation levels

Here I am trying to explain the difference of isolation levels in plain English together with the scenarios in real situation which we can relate with those concepts .

There are two main concepts that are used to define the differences of isolation levels:

Dirty read:

Read data that has been modified but not committed by other transactions, put it in a simple way, read uncommitted data by other transactions.

None repeatable read:

In the same transaction, the  row(s) of data that are read later might be different from what were read before,   this means the data that was read earlier could be changed by someone else, obvious there is no lock on the data that is read in this case.

Now the isolation levels:

READ UNCOMMITTED:

Dirty read : yes,  not blocked by data locked by other trans, seeing uncommitted data

None repeatable read: yes, no lock on read data, others can change data, add data,delete data

READ COMMITTED:

Dirty read : no, cannot see uncommitted data by others

None repeatable read: yes, no lock on read data, others can change data, add data

REPEATABLE READ:

Dirty read : no, cannot see uncommitted data

None repeatable read: no,  lock on read data,  others cannot modify data, but others can insert and delete row within the read change.

So REPEATABLE READ only prevents existing data changes, but could find more records later on

This leads to a more protective isolation level.

SERIALIZABLE:

Dirty read : no, cannot see uncommitted data

None repeatable read: no,  lock on read data,  others cannot modify data, others cannot delete or insert row into the read range either. This could be great cause of dead lock. The default isolation level for transaction scope in c# is SERIALIZABLE

 

This entry was posted on Friday, December 13th, 2013 at 1:13 am and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

*