Isolation Levels

What are the isolation levels?

While executing queries, SQL Server often required to acquire locks on resources to prevent dirty reads, phantom reads etc. These locking behaviour will be depends on isolation levels. Based on isolation level of transaction, SQL server will acquire locks on resources.

There are different isolation levels

1. Read committed
2. Read uncommitted
3. Snap-shot
4. Serilizable
5. Repeatable Read

There are few problems, which raises bescause of resource handling in transactions.

1. Dirty read: If userA inserts a record in a transaction, before he commits userB reads the data from the same table he will see the newly inserted record. There is a possibility that userA can rollback his transaction so the record is not committed to database so userB will get incorrect information.
2. Non-Repeatable read : Often, we have the requirement of reading same row more than once in a query. There are possibilites that is displays different values for that row because of non-repeatable read. for example a non-repeatable read occurs of event happend in the following sequence in a query, UserA is reading a row twice, 1. UserA references Row1 first. 2. UserB updated the row 3. UserA again references Row1 second time, Now he will get a different value.
3. Phantom Read: Often in a transaction, we execute same select statement multiple times. If both times it got different results then it is called Phantom read.

To deal these problems, isolation levels will be used

1. Read Uncommitted isolation level: If transaction is set to this isolation level, it will read uncommitted data. it means there are possibilities that all three (Dirty, non-repeatable and Phantom) might occur.
2. Read committed isolation level: if transaction is set to this isolation level, it will read committed records only. So, here we can avoid dirty reads. But there are other 2 reads might occur here.
3. Repeatable read isolation level: Here the read and write locks will be held for entire transaction. Because of that we can avoid non-repeatable reads here. But still phantom read might occur.
4. Serizialable Isolation level: This is the highest isolation level, here read, write and also it will hold range locks, because of that other transactions cant insert a record in between. Here we can prevent all 3 reads.
5. Snapshot Isolation level: This is introduced in 2005 onwards, Serializable Isolation level will blocks the readers also to wait for resources, while the other transaction is modifying records. This will prevent the blocking of readers, as it maintains the updated version in Tempdb.