Isolation Level of MS SQL Server

Introduction

Before understanding the Isolation Level of Microsoft SQL Server, we must recall the first class of Relational Database Management system (RDBAMS) and the ACID property of RDBMS.

A	Atomic
C	Consistency
I	Isolation This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction.
D	Durability

Types of Isolation Level in MS SQL Server

There are five type of Isolation level in MS SQL Server

1.    Read Committed (The Default Isolation Level of MS SQL Server)

2.    Read Uncommitted

3.    Repeatable Read

4.    Serializable

5.    Snapshot

Before further proceed with Isolation level we have to clear understanding about two things

Dirty Reads

This is when we read uncommitted data, when doing this there is no guarantee that data read will ever be committed.

Phantom Reads

This is when data that we are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.

To Check the Current Isolation Level

DBCC useroptions

Read Committed Isolation Level

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

-- Query-1 of Session -1

BEGIN TRAN 

     UPDATE tbl_Test SET Col1 = 2; 

     WAITFOR DELAY '00:00:10'; 

ROLLBACK

--Query-2 of Session -2

SELECT * FROM tbl_Test;

In the above example   the Query-2 is waiting for Query-1 to complete. When the Query-1 is completed then the Query-2 retrieves records.

Read Uncommitted Isolation Level

In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

-- Query-1  of Session - 1

BEGIN TRAN 

     UPDATE tbl_Tests SET Col1 = 2; 

     WAITFOR DELAY '00:00:10'; 

ROLLBACK

-- Query-2 of Session - 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM tbl_Tests;

Here in this example Query-2 is not waiting for Query-1. Query-2 returns records without waiting for Query-1 my cause of Dirty data.

Repeatable Read Isolation Level

This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn't allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

 --Query - 1  of Session - 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 

BEGIN TRAN 

     SELECT * FROM tbl_Tests; 

     WAITFOR DELAY '00:00:10'; 

     SELECT * FROM tbl_Tests;   

ROLLBACK

--Query - 2  of Session - 2

UPDATE tbl_Tests  SET Col1 = -1

In the above example Query-1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query (Query-2) was forced to wait for Query-1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

Serializable Isolation Level

This Isolation level do not allow any transaction to read the data unless the other transactions completed their data modification operation. Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert,delete,update) for the entire range of records that the transaction is going to affect.

--Query - 1 of Session -1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN 

     SELECT * FROM tbl_Tests; 

     WAITFOR DELAY '00:00:10'; 

     SELECT * FROM tbl_Tests;

ROLLBACK

--Query - 2 of Session -2

INSERT INTO tbl_Tests(Col1,Col2,Col3) 

VALUES (100,100,100);

In the above example we will see that the insert in Query-2 waits for Query-1 to complete before it runs eradicating the chance of a phantom read.

If we change the isolation level in Query-1 to repeatable read, we will see the insert no longer gets blocked and the two seleclt statements in Query-1 return a different amount of rows.

Snapshot Isolation Level

In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

ALTER DATABASE myDb 

SET ALLOW_SNAPSHOT_ISOLATION ON;

Hope you like it.

Posted by: MR. JOYDEEP DAS