REPEATABLE READ Isolation Level

 

In my blog about Read Phenomenon against each isolation level . we saw and understood what are Dirty, non repeatable, Phantom reads.

Also in my last blogs on  Read Uncommitted and Read Committed Isolation Level we saw that non repeatable read phenomenon exists with them.

with above se saw read phenomenons that exists with Repeatable Read. And we saw

With Repeatable Read : Dirty Read( Not Exists) ,  Non Repeatable Read (Not Exists), Phantom Read( Exists).

Ok. So Lets Prove Above

 

---Script To Create Test 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

 

CASE -1

CASE – 1 ( Does REPEATABLE READ 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

--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).

 

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Select * From CheckIsolationLevel Where Id=1

 

As you see Query at Query Window 2 waits till Transaction at Query Window 1 Completes. So here while reading data or records which are in-flight or between transaction wait till transaction completes (which is preventing dirty read).

The Above Query At Query Window 2 Can be Written In Following Way Also.

Syntax 1 : Using “REPEATABLEREAD” Hint

Select * From CheckIsolationLevel (REPEATABLEREAD)  Where Id=1

 

Conclusion of CASE-1 (“YES”, Repeatable Read Isolatiton Prevents Dirty Read)

 

CASE –2

 

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

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

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

-- Also note that we have only fetched record where Id=1

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

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

--Note The Above “Update Query” Updates The Records

--Now Lets Fire Below Update Query

Update CheckIsolationLevel Set Name=’NEW VALUE’ Where Id=1

--And you noted that Above Query waits. The reason is that to prevent Non-Repeatable Read. Query at Query Window 1 Puts Shared Lock On the Record fetched and shared lock prevents data from being modified by other transactions until shared lock is released at Query Window 1.

--at Query Window 1 just fetched Record where ID=1 that is why Query at Query Window 2 Allowed us to Update Data Other Than 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 You see the Data Is Same As We Read Before With the Same Query, So It Clearly Proves That It Prevents No-Repeatable Read

Conclusion Of CASE –2 ( “YES” , Repeatable Read Isolation Prevent Non-Repeatable Read)

 

CASE – 3

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

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

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”,Repeatable Read Isolation Does Not Prevents Phantom Read)