Making locks less restrictive

In contrast to holdlock, the noholdlock keyword prevents Adaptive Server from holding any shared locks acquired during the execution of the query, regardless of the transaction isolation level currently in effect.

noholdlock is useful in situations where transactions require a default isolation level of 2 or 3. If any queries in those transactions do not need to hold shared locks until the end of the transaction, you can improve concurrency by specifying noholdlock with those queries.

For example, if the transaction isolation level is set to 3, which normally causes a select query to hold locks until the end of the transaction, this command releases the locks when the scan moves off the page or row:

select balance from account noholdlock
    where acct_number < 100

If the session isolation level is 1, 2, or 3, and you want to perform dirty reads, you can use the at isolation level read uncommitted clause.

The shared keyword instructs Adaptive Server to use a shared lock (instead of an update lock) on a specified table or view in a cursor.

See “Using the shared keyword” for more information.