You can set the transaction isolation level used by select commands:
For all queries in the session, with the set transaction isolation level command
For an individual query, with the at isolation clause
For specific tables in a query, with the holdlock, noholdlock, and shared keywords
When choosing locking levels in your applications, use the minimum locking level that is consistent with your business model. The combination of setting the session level while providing control over locking behavior at the query level allows concurrent transactions to achieve the results that are required with the least blocking.
If you use transaction isolation level 2 (repeatable
reads) on allpages-locked tables, isolation level 3 (serializing
reads) is also enforced.
For more information on isolation levels, see the System Administration Guide.
