Over a million developers have joined DZone.

Isolation Level of MS SQL Server

DZone's Guide to

Isolation Level of MS SQL Server

· Java Zone ·
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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.
This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction .
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
  UPDATE tbl_Test SET Col1 = 2; 
  WAITFOR DELAY '00:00:10'; 
--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
  UPDATE tbl_Tests SET Col1 = 2; 
  WAITFOR DELAY '00:00:10'; 
-- Query-2 of Session - 2
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
  SELECT * FROM tbl_Tests; 
  WAITFOR DELAY '00:00:10'; 
  SELECT * FROM tbl_Tests; 
--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
  SELECT * FROM tbl_Tests; 
  WAITFOR DELAY '00:00:10'; 
  SELECT * FROM tbl_Tests;
--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.
Hope you like it.

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}