Repeatable Read Isolation Level in SQL Server with example

Repeatable Read

In Repeatable Read, statements cannot read data that has been modified but not yet committed by other transactions. No other transaction can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. This isolation level prevents the Non Repeatable Read issue.

 IF OBJECT_ID('Product') is not null

    begin

    DROP TABLE Product

    end

create table Product

(ProdID int,

ProdName Varchar(50),

ProdPrice int

)

 insert into Product(ProdID,ProdName,ProdPrice)

values

( 1,'Black Shirt',1000),

( 2,'White Suits',2000),

( 3,'Red Kurta',700),

( 4,'Half Shirt',1700)

  

Open two sessions

Run both the sessions side by side 

Example 1:

Session 1:

set transaction isolation level repeatable read

begin tran

select * from Product where ProdID in(1,2)

waitfor delay '00:00:15'

select * from Product where ProdID in (1,2)

rollback

Session 2:

update Product set ProdPrice=999 where ProdID=1

Output Session 1:

Update command in session 2 will wait till session 1 transaction is completed because ProdID = 1 has been used in session 1.

Example 2:

Session 1:

set transaction isolation level repeatable read

begin tran

select * from Product

waitfor delay '00:00:15'

select * from Product

rollback

Session 2:

insert into Product(ProdID,ProdName,ProdPrice)

values ( 5,'Blue Jacket',4000)

 

Output Session 1:

session 2 will execute without any delay because it has insert query for new entry. This isolation level allows to insert new data but does not allow to modify data.

Now you can see a new record inserted.

 Example 3:

 Session 1:

set transaction isolation level repeatable read

begin tran

select * from Product where ProdID in(1,2)

waitfor delay '00:00:15'

select * from Product where ProdID in (1,2)

rollback

Session 2:

update Product set ProdPrice=999 where ProdID=3

Session 2 will execute without any delay because ID 1 and 2 are locked in session 1.

Click on Below links to see the Different types of isolation levels with example

• Read UnCommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot