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)
