Isolation Level Precedences

Precedence rules apply to 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

    SAP ASE issues a warning before executing the above query.