Using the at isolation clause

You can change the isolation level for all tables in the query by using the at isolation clause with a select or readtext command. The options in the at isolation clause are:

Level to use

Option

Effect

0

read uncommitted

Reads uncommitted changes; use from level 1, 2, or 3 queries to perform dirty reads (level 0).

1

read committed

Reads only committed changes; wait for locks to be released; use from level 0 to read only committed changes, but without holding locks.

2

repeatable read

Holds shared locks until the transaction completes; use from level 0 or level 1 queries to enforce level 2.

3

serializable

Holds shared locks until the transaction completes; use from level 1 or level 2 queries to enforce level 3.

For example, the following statement queries the titles table at isolation level 0:

select *
from titles
at isolation read uncommitted

For more information about the transaction isolation level option and the at isolation clause, see the Transact-SQL User’s Guide.