Isolation Levels

Isolation Levels :

What is Isolation levels?

It controls the behaviour of your read operations.Such as

• Whether locks are taken when data is read, and what type of locks are requested.

• How long the read locks are held.

Types of Isolation Levels ?

SQL Server supports five Isolation levels.
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
SQL Server 2005 Introduces two types of transaction Isolation levels.
READ COMMITTED ISOLATION and SNAPSHOT ISOLATION.Both Isolation levels uses row versioning.

The Lowest Isolation level is READ UNCOMMITTED.
READ UNCOMMITTED :
In this Isolation Level your read operations to not take any locks, and because SQL Server isn’t trying to acquire locks, it won’t be blocked by conflicting locks acquired by other processes. Your process is able to read data that another process has modified but not yet committed.
Read Uncommitted Isolation level wont gives the transactional consistency because locks are just ignored.
In this Level DIRTY READS,PHANTOM and NON REPEATABLE READ occurs.
Here Iam showing you one example to understand READ UNCOMMITTED ISOLATION level.
STEP 1:
1.Here Iam updating a table but not yet committed.
open a query window by pressing CTRL+N in Management studio and run the below syntax

begin tran
use pepsi
select * from clustered_dupes
update table clustered_dupes set col4 = 'ravi' where col1 = 1
 The table is updated and to see now
STEP 2 :
Open one more query window by pressing CTRL+N in management studio and run the below syntax
Begin Tran
Use Pepsi
Select * from dbo.clustered_dupes
If you run the above syntax the process will block and it is blocked by STEP 1 Process Because SQL Server try to puts the lock on table but already an exclusive lock is kept by STEP 1 process. The data is modified but still it is not committed and Until Commit Tran wont run on STEP 1 SQL server wont release the lock.

 Now open another window and run SP_WHO2 and you will see there is a process which is Blocked by step 1 process. 
Until you commit the transaction or rollback the transaction in step 1 process, the step 2 process wont get completed you need to kill the process STEP 2.
But this is default behaviour of SQL Server when Isolation level is READ COMMITTED
Now we are changing the Isolation level by using 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Issue the Same syntax of STEP 2 Process.
Begin tran
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use pepsi
select * from clustered_dupes
Now you can read the uncommitted data in STEP 1 Process without blocked by STEP 1 Process.

I think this example clearly illustrate the use of Isolation level READ UNCOMMITTED

READ COMMITTED :   
This is the default isolation level in SQL Server 2005. 
It ensures that an operation never reads data that another application has changed but not yet committed.In this level Dirty reads cannot occur but Phantom and nonrepeatable read occurs.
REPEATABLE READ : 
When this option is set, as data is read, locks are placed and held on the data for the duration of the transaction. These locks prevent other transactions from modifying the data you have read.This isolation level is more restrictive than READ COMMITTED and READ UNCOMMITTED, and can block other transactions.It prevents non repeatable reads but does not prevent the phantom rows.
Here Iam showing an example of REPEATABLE READ.
STEP 1:
Begin Tran
Use pepsi
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Select * from clustered_dupes
After running the above script locks are placed on table Clustered_dupes and it wont allow you to update the transactions but you can see the data.
STEP 2:
Open one more query window by pressing ctrl+N on management studio and run the below script
Begin tran
use  pepsi
update clustered_dupes set col4 = 'ravi' where col1 = 1


It wont allow you to update the table until STEP 1 process should commit and step 2 process is blocked by step 1
STEP 3 :
Open one more query window by pressing ctrl+N on management studio and run the below script 
Begin tran
Use pepsi
Select * from clustered_dupes
now you can see the data.

SERIALIZABLE :

This option is the most restrictive isolation level because it places a Key range lock on the data. This prevents any modifications to the data being read from until the end of the transaction. It also avoids phantom reads by preventing rows from being added or removed from the data range set.
 In addition, enforcing the Serializable isolation level requires that you not only lock data that has been read, but also lock data that does not exist!
READ COMMITTED SNAPSHOT : 
When this option is set, SQL Server uses row versioning instead of locking.
It can be enabled by using database option 
alter database 'database name' set read_committed_snapshot on
after enabling this option you can examine on column is_read_committed_snapshot_on  in sys.databases catalog view.
In this level dirty reads cannot occur but phantom and nonrepetable reads occur.
This isolation level cannot set on master,msdb and tempdb system databases.
When you are enabling read_committed_snapshot option for a database please ensure that there are no active transactions for that database. 
When this option is enabled for a database, the Database Engine maintains versions of each row that is modified. Whenever a transaction modifies a row, image of the row before modification is copied into a page in the version store. The version store is a collection of data pages in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain. Read operations using row versioning retrieve the last version of each row that had been committed when the transaction or statement started.
 For transactions using an isolation level based on row versioning, read operations do not request shared locks on the data. This means readers using row versioning do not block other readers or writers accessing the same data. Similarly, the writers do not block readers. However, writers will block each other even while running under row versioning-based isolation levels. Two write operations cannot modify the same data at the same time.

SNAPSHOT ISOLATION :

Snapshot isolation specifies that data read by any statement will only see data modifications that were committed before the start of the transaction. The effect is as if the statements in a transaction see a snapshot of the committed data as it existed at the start of the transaction. The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON for a transaction to specify the SNAPSHOT isolation level.

It makes a snapshot copy of the data being updated. During the update, readers can continue to read the original data. When the update is committed, it’s written back over the original data.