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.
