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.