Understand Isolation Levels with Query Example

Isolation Levels in any databases like SQL,Oracle,DB2,etc are used for preventing data consistency issues. It is very essential for any developers to understand how isolation works as the coding written by users behave differently when isolation levels are specified differently. There are various isolation levels in SQL Server namely

Ø Read Uncommitted
Ø Read Committed
Ø Repeatable Read
Ø Serializable

It is better to understand each of this example rather by reading through definitions on what each means.

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'DOCS_HISTORY')
DROP TABLE DOCS_HISTORY
GO

-- Create table named DOCS_HISTORY
CREATE TABLE [dbo].[DOCS_HISTORY](
[DOC_ID] [int] IDENTITY(1,1) NOT NULL,
[DOC_NAME] [varchar](100) NOT NULL,
[DOC_SIZE] [bigint] NULL,
[DOC_UPD_DATE] [datetime] NULL,
CONSTRAINT [PK_DOCS_HISTORY] PRIMARY KEY CLUSTERED([DOC_ID] ASC))

-- Insert a committed row in DOCS_HISTORY
BEGIN TRAN
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC1',100,getdate()-3
COMMIT

-- Insert a uncommitted row in DOCS_HISTORY
BEGIN TRAN
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC2',200,getdate()-2

If we execute below commands on other session in SQL Management Studio
-- Read Uncommitted Isolation Level returns 2 rows and this can be used for generating reports
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM DOCS_HISTORY

-- Read Committed Isolation Level which is default doesn’t returns rows and it keeps on running
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM DOCS_HISTORY

This is the reason you would see DBAs running around to person who is blocking other users as user has a uncommitted transaction, so please keep the transaction short and commit or rollback transaction once the same is complete.

For understanding repeatable read we would need 3 sessions in SQL Management Studio
--Management Studio Window 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM [DOCS_HISTORY]
WAITFOR DELAY '00:01:00'
SELECT * FROM [DOCS_HISTORY]
COMMIT

--Management Studio Window 2
BEGIN TRAN
UPDATE [DOCS_HISTORY]
SET DOC_SIZE = DOC_SIZE + 500,
DOC_UPD_DATE = GETDATE()
COMMIT

-- Management Studio Window 3
BEGIN TRAN
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC3',300,getdate()-1
COMMIT TRAN

All 3 statements would get executed but the output from Window 1 would return 2 different outputs in case of read committed even when they are in a transaction.

Above same command when executed via Repeatable Read Isolation Level(SET TRANSACTION ISOLATION LEVEL REPEATABLE READ) which allows newer insert to happen and doesn’t allow Window 2 execution to complete as the same value was read in a transaction in Window 1 and it waits till Window 1 completes and then updates the result.

So Repeatable Read would not allow values to be changed for rows that was read inside a transaction until the same is complete however it allows newer records to be inserted which is a phantom record as the same was not present during execution of 1st select statement.
Last isolation level is Serializable and many would have guessed from previous isolation levels what would this do, yes it does not allow any data inconsistencies i.e. it will not allow Dirty Reads, non repeatable reads and Phantom reads.

Let us try to understand this also by means of same example.

--Management Studio Window 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM [DOCS_HISTORY]
WAITFOR DELAY '00:01:00'
SELECT * FROM [DOCS_HISTORY]
COMMIT

--Management Studio Window 2
BEGIN TRAN
UPDATE [DOCS_HISTORY]
SET DOC_SIZE = DOC_SIZE + 500,
DOC_UPD_DATE = GETDATE()
COMMIT

-- Management Studio Window 3
BEGIN TRAN
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC3',300,getdate()-1
COMMIT TRAN

BEGIN TRAN
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC4',400,getdate()
COMMIT TRAN

Run first Window 1 followed by Window 2 and Window 3. You would notice that Window 2 and Window 3 would be in wait state and would not execute until Window 1 is completed.

This is the top most isolation level with not data inconsistencies between reads but this is the isolation level would cause deadlocks.

There is also a new Isolation which is introduced in SQL 2005 which is SNAPSHOT isolation which is very handy and behaves mixed of SERIALIZABLE and READ COMMITTED as it always considers SNAPSHOT taken during start of transaction and displays the same inside the transaction and also it does not block new inserts or updates to happen.

You can use the same above example to understand the same with isolation level set as SET TRANSACTION ISOLATION LEVEL SNAPSHOT and understand the results.

So one need to decide on our business model and choose the appropriate isolation level based on our business model and READ COMMITTED is the widely accepted or used Isolation level.

P.S:- Thanks to Guru Charan for explaining this.