Monday, 17 June 2013

Isolation level in Sql Server

There are 5 types of isolation level in Sql Server 2008. These isolation level are as given below

1. Read Uncommited
2. Read Commited
3.Repetable Read
4.Serializable
5.Snapshot

1.Read Uncommited

Specify that statement can read row that have been modified by other transactions

 but not yet commited. For example i have two session of the Sql Server

SET ISOLATION TRANSACTION LEVEL

READ UNCOMMITED

Session I                                                                        
BEGIN TRAN                                                                   
UPDATE TABLE SET COL=1 WHERE                              
COL =2
WAIT FOR DELAY '00:00:10'
ROLL BACK TRAN

Session II
Select * from TABLE

When i execute the query for second session after the session I  then select query will given us uncommited record. In this case dirty read will be occurred.This is same as NOLOCK table for retrieving the record.

READ COMMITED :

Specify that statement cannot read data that has been modified but not commited by the transaction.It prevents dirty read data.


 

No comments:

Post a Comment