Read Committed ISOLATION LEVEL

In my last post about Read Phenomenon against each isolation level .

We saw Dirty, Repeatable & Phantom Reads occurrence. And these phenomenon exists in Read Uncommitted Isolation.

Before Starting Read Uncommitted Isolation level lets Understand functionality of default Isolation Level(i.e. READ COMMITTED)

As I explained in my last blog the default isolation level is Read Committed. You can check isolation level by running following query.

DBCC UserOptions

Go

How to change the isolation level ?

Following Setting can be used to set Isolation level.

SET TRANSACTION ISOLATION LEVEL  <READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE | SNAPSHOT>

GO

Note : Isolation Level Setting Scope is connection based, You cannot set Isolation level on Database Level. Once Connection is closed isolation level is lost.

ok lets see the effect of Default Isolation Level.

--Check the current isolation level (which must be READ COMMITTED)

DBCC UserOptions

Go

-- and as expected the isolation level is READ COMMITTED (because its default)

-- Create the following table.

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go

Id	Name	EncodedDate
1	Pankaj	2011-07-28 20:50:53.247
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

The above table is a Heap Table (coming soon on MSSQL GUIDE) since there is not cluster index. let’s work on READ COMMITTED Isolation level with Heap Table.

 

CASE -1

CASE – 1 ( Does READ COMMITTED ISOLATION Prevents Dirty Read ?)

Query Window 1

Begin Transaction
Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

 

Lets Open Another Connection

Query Window 2

Select * From CheckIsolationLevel Where Id=2

 

--So Query waits as the table is locked by pending transaction at Query Window 1. There is one question since query at window 1 is updating record with id=1 then why query at window 2 is locked while retrieving data for id=2. This is because table is HEAP TABLE and query at window 2 scans whole table to find record where id=2. AND query waits till pending transaction at query window 1 is committed or rolledback.

Note: But The Above Query At Window 2 will give result(will not wait) if you have cluster index on the table(called cluster table).

You can now commit the transaction at Query Window 1 and check the Query Window 2

This is how READ COMMITTED Isolation prevents Dirty Reads.

Conclusion of CASE-1 (“YES”, Read Committed Isolation Prevents Dirty Read.)

 

CASE -2

 

CASE – 2 (Does Read Committed Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

 

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

  Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As Expected Data is Changed even executing identical query within a transaction ( This is forming a Non-Repeatable Read).

Conclusion Of CASE –2 ( “NO” , Read Committed Isolation Doesn’t Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does Read Committed Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel 
Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel  Where Id Between 2 and 5

--Following result is Retrieved

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247
4	Sandeep	2011-07-28 21:17:25.820
5	Naren	2011-07-28 21:17:25.820

 

As You Can See The Above result with 2 New Rows which were not there in First Result (Causing Phantom Read)

Conclusion Of CASE – 3 (“NO”,Read Committed Isolation Does Not Prevents Phantom Read)

We Will See These Read Phenomenon Against Uncommitted Isolation Level In My Next Blog.

Thanks You All

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.