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