Repeatable Reads

A transaction performing repeatable reads locks all rows or pages read during the transaction. After one query in the transaction has read rows, no other transaction can update or delete the rows until the repeatable-reads transaction completes.

Repeatable-reads transactions do not provide phantom protection by performing range locking, as serializable transactions do. Other transactions can insert values that can be read by the repeatable-reads transaction and can update rows so that they match the search criteria of the repeatable-reads transaction.

A transaction performing repeatable reads locks all rows or pages read during the transaction. After one query in the transaction has read rows, no other transaction can update or delete the rows until the repeatable reads transaction completes. However, repeatable-reads transactions do not provide phantom protection by performing range locking, as serializable transactions do. Other transactions can insert values that can be read by the repeatable-reads transaction and can update rows so that they match the search criteria of the repeatable-reads transaction.

Note: Transaction isolation level 2 is supported only in data-only-locked tables. If you use transaction isolation level 2 (repeatable reads) on allpages-locked tables, isolation level 3 (serializable reads) is also enforced.

To enforce repeatable reads at a session level, use:

set transaction isolation level 2
or:
set transaction isolation level repeatable read

To enforce transaction isolation level 2 from a query, use:

select title_id, price, advance
from titles 
at isolation 2
or:
select title_id, price, advance
from titles 
at isolation repeatable read

Transaction isolation level 2 is supported only at the transaction level. You cannot use the at isolation clause in a select or readtext statement to set the isolation level of a query to 2.

Related concepts
Change the Isolation Level for a Query