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:
|
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. |
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.