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)