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
