If isolation level 1 is sufficient for most of your work, but some queries require higher levels of isolation, you can selectively enforce the higher isolation level using clauses in the select statement:
Use repeatable read to enforce level 2
Use holdlock or at isolation serializable to enforce level 3
The holdlock keyword makes a shared page or table lock more restrictive. It applies:
To shared locks
To the table or view for which it is specified
For the duration of the statement or transaction containing the statement
The at isolation clause applies to all tables in the from clause, and is applied only for the duration of the transaction. The locks are released when the transaction completes.
In a transaction, holdlock instructs Adaptive Server to hold shared locks until the completion of that transaction instead of releasing the lock as soon as the required table, view, or data page is no longer needed. Adaptive Server always holds exclusive locks until the end of a transaction.
The use of holdlock in the following example ensures that the two queries return consistent results:
begin transaction
select branch, sum(balance)
from account holdlock
group by branch
select sum(balance) from account
commit transaction
The first query acquires a shared table lock on account so that no other transaction can update the data before the second query runs. This lock is not released until the transaction including the holdlock command completes.
