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.
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.
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. See “Changing the isolation level for a query”.
