Database Simplified Headline Animator

Database Simplified

Tuesday 26 July 2011

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.

No comments:

Post a Comment