Using the Dirty Read Isolation Level

Use the Dirty Read option to copy rows from the database whether or not there are locks on them. The program that fetches a row places no locks and it respects none. Dirty Read is the only isolation level available to databases that do not implement transaction logging.

This isolation level is most appropriate for static tables that are used for queries of tables where data is not being modified, because it provides no isolation. With Dirty Read, the program might return an uncommitted row that was inserted or modified within a transaction that has subsequently rolled back, or a phantom row that was not visible when you first read the query set, but that materializes in the query set before a subsequent read within the same transaction. (Only the Repeatable Read isolation level prevents access to phantom rows. Only Dirty Read provides access to uncommitted rows from concurrent transactions that might subsequently be rolled back.)

The optional WITH WARNING keywords instruct the database server to issue a warning when DML operations that use the Dirty Read isolation level might return an uncommitted row or a phantom row. The transaction in the following example uses this isolation level:

BEGIN WORK;
SET ISOLATION TO DIRTY READ WITH WARNING;
...
COMMIT WORK;

The Dirty Read isolation level is sensitive to the current setting of the USELASTCOMMITTED configuration parameter and of the USELASTCOMMITTED session environment variable. For information about the behavior of the Dirty Read isolation level when either of these are set to DIRTY READ or to ALL, see The LAST COMMITTED Option to Committed Read.

When you use High Availability Data Replication, the database server effectively uses Dirty Read isolation on the HDR Secondary Server, regardless of the specified SET ISOLATION or SET TRANSACTION isolation level, unless the UPDATABLE_SECONDARY configuration parameter is enabled. For more information about this topic, see Isolation Levels for Secondary Data Replication Servers.