Isolation Level Precedences

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

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.

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.