Reapeatableread Table Hint in SQL Server

The Reapeatableread table hint enforce repeatable read isolation for a specific table access regardless of the transaction’s currently isolation level. This ensure that read within a transaction cannot be modify or deleted by other transaction until the current transaction completes. 

It prevents dirty read. It prevents non repeatable read (Data read once will remain unchanged for the duration of the transaction). It allows phantom read (new row can be still inserted by other transactions). It uses shared locks(s-lock) that are hold for the duration of the transaction. Othe transaction can still insert new row which can cause phantom read.

See example below.

Without using Reapeatableread hint (using the default read committed)

begin tran  select * from Employees where EmployeeID=5  --commit

Reading the data in transaction

in other session we are trying to update this records.

update Employees set Department='ADMIN3' where EmployeeID=5

We are able to update this record.

See the updated record in other session as well

Now we are using this table hint. See the below example

begin tran  select * from Employees with (repeatableread) where EmployeeID=5  --commit

 Transaction is open we have not yet committed.

Now we are trying the update this record into another session. Let’s see

Here we are not able to update this record. Let’s commit the transaction.

As soon as committing this transaction update has been completed.

See the updated record.

Modification and deletions are blocked by other transaction until this transaction is committed. However, new rows can still be inserted leading the phantom reads. It prevents dirty reads and non-repeatable read. Other transaction cannot modify or delete read rows until the transaction completed.

Locks hold for the duration of transaction shared locks (s lock) remain until the transaction commits which can cause blocking and deadlocking in the high concurrency environments. It does not prevent phantom reads, other transaction can insert the new row, so consecutive reads in the same transaction might return additional rows. Long running transactions with Reapeatableread can lead to high contention and reduced concurrent transaction.