Isolation level in SQL Server With Read Phenomenon

ISOLATION : I  From ACID Of Database, Means every transaction should be separated from each other until they are finished . (committed or rolled back)
SQL Server 2008 Has Following ISOLATION LEVELS.

• Read UnCommitted
• Read Committed   (Default Isolation Level In Sql Server)
• Repeatable Read
• Serializable
• Snapshot (Since 2005)

Before Starting Isolation Levels I’ll Share Some Information About Dirty,Non Repeatable and Phantom Reads.


Dirty Read : As the Name Says Read, Means Reading of those data which has been modified by an in-flight transaction (The Transaction which has not been Committed or Rolled Back).
Check the Following Example For Dirty Read.
Table Name : Employee

EmployeeId	EmployeeName
1	A Purvs
2	Samantha Blackett
3	Shane

Query Window 1

Begin Transaction

Update Employee Set EmployeeName=’Amanda Purvs’ Where EmployeeId=1

Note :  Transaction Is In-Flight (Not Committed Yet)

Query Window 2

Select * From Employee Where EmployeeId=1

Output Of Query Window 2 :

EmployeeId	EmployeeName
1	Amanda Purvs

Now No One Can Guarantee The Correctness of Data User Looking at  Query Window 2
If Transaction At Query Window 1 is Committed The Result of Query Window 2 is Correct, But What If Transaction At Query Window 1 is Rolledback then Result returned by Query Window 2 Is Incorrect (Which Is a Form Of Dirty Read)

Non Repeatable Read : Non Repeatable Read occurs when a Transaction reads data twice with same condition Or Clause and gets different results. Let’s See following example to make Non Repeatable Read More Clear.
Table Name : BillDetails

BillId	BillAmount
1	250
2	160
3	690

Query Window 1

Begin Transaction

Select Sum(BillAmount) As TotalAmount From BillDetails

--You Get output of Above Query : 1100 And Transaction is Still Pending Since No Commit and Rollback has been issued


Query Window 2

Begin Transaction
Update BillDetails Set BillAmount=560 Where BillId=2
Commit Transaction

--Note Transaction Has Been Committed

Query Window 1
Following Is Your Old Query Which You Wrote Before Modification
Begin Transaction
Select Sum(BillAmount) As TotalAmount From BillDetails
--You Get output of Above Query : 1100 And Transaction is Still Pending Since No Commit and Rollback has been issued
Let’s Fire The Same Query Again And See The Result Note

Select Sum(BillAmount) As TotalAmount From BillDetails

--Now Your result is : 1500 Which is different From Previous Result

So As You can see in Above Example User At Query Window 1 Gets Different Results Within One Transaction On Executing Same Query (This Phenomenon Is Known As Non Repeatable Read )

Phantom Read : This Phenomenon occurs When Two Identical Query (With same Condition or same Where clause)  is executed and returns different no of rows.

See The Following Example:

Table Name : Employee

EmployeeID	EmployeeName	Age
1	Sachin	12
2	Vikas	15
3	Sandeep	11
4	Ramesh	19

Query Window 1

Begin Transaction
   Select * From Employee Where Age Between 10 and 18

--Returns Following Output

EmployeeID	EmployeeName	Age
1	Sachin	12
2	Vikas	15
3	Sandeep	11

Query Window 2

Begin Transaction

Insert Into Employee(EmployeeId,EmployeeName,Age) 
Values(5,’Harinder’,16)

Commit Transaction

--Note Transaction Is Committed
Now Let’s Go Back To Query Window 1

Query Window 1

Your Old Query
Begin Transaction
   Select * From Employee Where Age Between 10 and 18

EmployeeID	EmployeeName	Age
1	Sachin	12
2	Vikas	15
3	Sandeep	11

The First Query Gave You Above Result, note that the transaction is still pending.
Now Let’s Fire The Same Query Again

Select * From Employee Where Age Between 10 and 18

EmployeeID	EmployeeName	Age
1	Sachin	12
2	Vikas	15
3	Sandeep	11
5	Harinder	16

As You can see the result returned by second query has one extra row. So that Extra Row Is Nothing but Phantom Read.
Now I Hope From the Above Example Dirty, Non Repeatable and Phantom Read Must Be Clear To You All.

See The Following Table To Get information About Read Phenomenon Against Each Isolation Level

Isolation Levels	Dirty Read	Non Repeatable Read	Phantom Read
Read UnCommited	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No
Snapshot	No	No	No

I’ll Write About Isolation Levels In My Next Blog.