Isolation level precedences

The following describes the precedence rules as they apply to the different methods of defining isolation levels:

1. The holdlock, noholdlock, and shared keywords take precedence over the at isolation clause and set transaction isolation level option, except in the case of isolation level 0. For example:

   /* This query executes at isolation level 3 */
   select * 
       from titles holdlock
       at isolation read committed
   create view authors_nolock
       as select * from authors noholdlock
   set transaction isolation level 3
   /* This query executes at isolation level 1 */
   select * from authors_nolock
   

2. The at isolation clause takes precedence over the set transaction isolation level option. For example:

   set transaction isolation level 2
   /* executes at isolation level 0 */
   select * from publishers
       at isolation read uncommitted
   

   You cannot use the read uncommitted option of at isolation in the same query as the holdlock, noholdlock, and shared keywords.

3. The transaction isolation level 0 option of the set command takes precedence over the holdlock, noholdlock, and shared keywords. For example:

   set transaction isolation level 0
   /* executes at isolation level 0 */
   select * 
       from titles holdlock
   

   Adaptive Server issues a warning before executing the above query.