Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

REPEATABLE READ

Repeatable read Isolation in SQL Server

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

 

Example:

 
--Query1
 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:05' 
SELECT * FROM Employee 
ROLLBACK
 
--Query2
Update Employee set UpdatedDate=getdate()

 

Result:

Run query 1 and then query 2, from query 1 notice that  both select statement returns same data even though you ran a query 2 to modify the data before the second select run. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.


 


After Transaction completes and update query executed, Select statement returns modified record:

 


 

 

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

 

For above same use case Read returns:


 

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted; it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

 

Phantom read with repeatable read:

 

--Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK 
 
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES ('Anushka', ‘England’,'English','London',5) 

 


 

 

Invalid entry,please enter valid data.

Loading