You can change the isolation level for a query by using the at isolation clause with the select or readtext statements. The at isolation clause supports isolation levels 0, 1, and 3. It does not support isolation level 2. The read uncommitted, read committed, and serializable options of at isolation represent isolation levels as listed below:
at isolation option |
Isolation level |
|---|---|
read uncommited |
0 |
read committed |
1 |
serializable |
3 |
For example, the following two statements query the same table at isolation levels 0 and 3, respectively:
select * from titles at isolation read uncommitted select * from titles at isolation serializable
The at isolation clause is valid only for single select and readtext queries or in the declare cursor statement. Adaptive Server returns a syntax error if you use at isolation:
With a query using the into clause
Within a subquery
With a query in the create view statement
With a query in the insert statement
With a query using the for browse clause
If there is a union operator in the query, you must specify the at isolation clause after the last select.
The SQL-92 standard defines read uncommitted, read committed, and serializable as options for at isolation and set transaction isolation level. A Transact-SQL extension also allows you to specify 0, 1, or 3, but not 2, for at isolation. To simplify the discussion of isolation levels, the at isolation examples in this manual do not use this extension.
You can also enforce isolation level 3 using the holdlock keyword of the select statement. However, you cannot specify noholdlock or shared in a query that also specifies at isolation read uncommitted. (If you specify holdlock and isolation level 0 in a query, Adaptive Server issues a warning and ignores the at isolation clause.) When you use different ways to set an isolation level, the holdlock keyword takes precedence over the at isolation clause (except for isolation level 0), and at isolation takes precedence over the session level defined by set transaction isolation level.
See the Performance and Tuning Series: Locking and Concurrency Control.
