The READPAST locking hint skips locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation level and will read only past row-level locks. This only applies to the SELECT statement.
The locking hint clause, such as READPAST, can only be specified in the main SELECT statement, but not in the subquery SELECT statement in the search condition of the WHERE clause.
The SELECT statement uses the following syntax:
SELECT column_list FROM table_list[WITH(NOLOCK)][ WHERE search_condition][ GROUP BY grouping_condition][ HAVING search_condition][ ORDER BY ordering_condition][ WITH locking_hints][ FOR UPDATE update_condition];
The WITH NOLOCK option, when used as part of the table reference, allows the SELECT statement to perform a dirty read.
The WITH phrase uses the following syntax:
WITH ( READPAST [ NOWAIT ][ WAIT timeout ] )
NOWAIT
Causes the SELECT statement to skip (read past) the row immediately if a lock cannot be acquired on a row in the selection set because of the lock held by some other transaction. The default behavior is for the transaction to wait until it gets the required lock or until it times out waiting for the lock.
WAIT timeout
Overrides the default lock-wait time-out. The time-out value is in seconds and can be given a 0 or any positive number.
The SELECT statements in the following examples illustrate the use of the READPAST locking hint.
SELECT * FROM Customer WHERE "CustNum" < 100 ORDER BY "CustNum" FOR
UPDATE
WITH (READPAST NOWAIT);
SELECT * FROM Customer WHERE "CustNum" < 100 ORDER BY "CustNum" FOR
UPDATE
WITH (READPAST WAIT 1);
