You can specify isolation level 0 for the queries in transactions along with the isolation levels 1 and 3 supported in release 10.0. Isolation level 0 prevents other transactions from changing data that has already been modified by an uncommitted transaction. The other transactions are blocked from modifying that data until the transaction commits. However, other transactions can still read the uncommitted data (known as dirty reads).
Queries executing at isolation level 0 do not acquire any read locks for their scans, so they do not block other transactions from writing to the same data (and vice versa). Applications that are not impacted by dirty reads may see better concurrency and reduced deadlocks when accessing the same data using isolation level 0. However, transactions that require data consistency should not use isolation level 0.
You can specify isolation level 0 for the transactions of a session as follows:
set transaction isolation level 0
Since this command makes all queries execute at isolation level 0, you should be wary of using it for any transaction that requires data consistency. Instead, you can selectively choose isolation level 0 for a query in a transaction using the at isolation clause as follows:
select * from titles at isolation read uncommitted
For information about transactions and isolation levels, see Chapter 18, “Transactions: Maintaining Data Consistency and Recovery,” in the Transact-SQL User’s Guide.
