SQL Server Isolation Levels

This blog is very much inspired by Gavin Draper blog on isolation level. I tried to run the test in the more lively format for all isolation levels and share impacts in points which helps easy to remember

Isolation levels in SQL Server control the way locking works between transactions.
SQL Server supports the following isolation levels

• Read Committed (The default)
• Repeatable Read
• Serializable
• Read Uncommitted
• Snapshot

Before running concepts :

1. Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
2. Phantom Reads – This is when data that you 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.
3. Transaction:
4. Duration of Transaction:

We will use below table for running test :

CREATE TABLE Tests (A INT IDENTITY,B INT,C INT,D INT )
 GO
INSERT INTO Tests(B,C,D)
SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3

Read Uncommitted :

1. No locks (share / Exclusive)

2. Any data can be ready while modifying by other Transaction.

3. Any data can be modified while reading by other transaction.

4. This supports Dirty Reads

5. This Supports Phantom reads

Run Below transactions in 2 query analyzer with a difference of 3 seconds. Here both transactions are running for 10 seconds with overlapping of 7 seconds.

I have start transaction 1 @ 10:0 Am and transaction 2 @ 10:03 AM. So if the whole test run from 10:00 - 10:14 AM
Transaction 1 is modifying data at1st second of test and roll back just after 10th second of the test
Transaction 2 select data at the 3rd second of test and again select after13th second of the test

/**********Transaction 1 @ 10:0 Am ***********************/

BEGIN TRAN
UPDATE Tests SET B = 2
--After delay transaction will be rolled backWAITFOR DELAY '00:00:10'
ROLLBACK/*********************************/

/*********Transaction 2 @ 10:03 AM************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

go BEGIN
SELECT A,B,C,D FROM Tests
WAITFOR DELAY '00:00:10'
SELECT A,B,C,D FROM Tests
End
/*********************************/

Above screenshot shows different results in transaction 2. Here 1st select of transaction 2 overlaps with transaction 1.

So Sql server will behave like:

• Update date "Transaction 1"
• Select data "Transaction 2"
• Roll back Data "Transaction 1"
• Select data "Transaction 2"

IN SQL Engine terms :

UPDATE Tests SET B = 2 -- @10:00 AM

SELECT A,B,C,D FROM Tests -- @10:03 AM

ROLLBACK -- @10:10 AM

SELECT A,B,C,D FROM Tests -- @10:13 AM