Making locks more restrictive

If isolation level 1 is sufficient for most work, but some queries require higher levels of isolation, you can selectively enforce the higher isolation level using clauses in the select statement:

The holdlock keyword makes a shared page, row, or table lock more restrictive. holdlock applies:

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, row, 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.

If the session isolation level is 0, and only committed changes must be read from the database, you can use the at isolation level read committed clause.