Using readpast

The readpast option allows a select command to access the specified table without being blocked by incompatible locks held by other tasks. You can perform readpast queries only on data-only-locked tables.

If the readpast option is specified for an allpages-locked table, the readpast option is ignored. The command operates at the isolation level specified for the command or session. If the isolation level is 0, dirty reads are performed, and the command returns values from locked rows and does not block. If the isolation level is 1 or 3, the command blocks when pages with incompatible locks must be read.

This table shows the interactions of session-level isolation levels and readpast on a table in a select command:

Effects of Session-Level Isolation Levels and readpast

Session Isolation Level

Effects

0, read uncommitted (dirty reads) readpast is ignored, and rows containing uncommitted transactions are returned to the user. A warning message is printed.
1, read committed Rows or pages with incompatible locks are skipped; no locks are held on the rows or pages read

Using readpast may produce duplicates and adding the distinct clause does not clear this problem.

To resolve this, when using readpast, use a group by clause in addition to a distinct clause to avoid duplicates.

2, repeatable read Rows or pages with incompatible locks are skipped; shared locks are held on all rows or pages that are read until the end of the statement or transaction; holds locks on all pages read by the statement until the transaction completes.
3, serializable readpast is ignored, and the command executes at level 3. The command blocks on any rows or pages with incompatible locks.
select commands that specify readpast fail with an error message if they also include any of the following:
  • An at isolation clause, specifying 0 or read uncommitted

  • An at isolation clause, specifying 3 or serializable

  • The holdlock keyword on the same table

If at isolation 2 or at isolation repeatable read is specified in a select query that specifies readpast, shared locks are held on the readpast tables until the statement or transaction completes.

If a select command with the readpast option encounters a text column that has an incompatible lock on it, readpast locking retrieves the row, but returns the text column with a value of null. No distinction is made, in this case, between a text column containing a null value and a null value returned because the column is locked.