Using READPAST locking hint in an UPDATE statement

[msc...@eskimo.com]

I am executing an update statement similar to the following, which
includes a READPAST hint:

UPDATE t
SET processed = 1
FROM TxData t WITH ( ROWLOCK, UPDLOCK, READPAST )
WHERE processed = 0;

This statement does execute and skips locked rows as expected (which
is what I want).

My concern is that the SQL Server 2000 documentation explicitly states
that READPAST hints apply only to SELECT statements. Also, if I
rewrite the above statement as

UPDATE TxData WITH ( ROWLOCK, UPDLOCK, READPAST )
SET processed = 1
WHERE processed = 0;

the following error is thrown:
"The NOLOCK, READUNCOMMITTED, and READPAST lock hints are only allowed
in a SELECT statement."

So, is the original statement safe to use, or is this an undocumented
"feature" that I use at my own risk?
Any comments would be appreciated.

[Erland Sommarskog]

(msc...@eskimo.com) writes:
> My concern is that the SQL Server 2000 documentation explicitly states
> that READPAST hints apply only to SELECT statements. Also, if I
> rewrite the above statement as
>
> UPDATE TxData WITH ( ROWLOCK, UPDLOCK, READPAST )
> SET processed = 1
> WHERE processed = 0;
>
> the following error is thrown:
> "The NOLOCK, READUNCOMMITTED, and READPAST lock hints are only allowed
> in a SELECT statement."
>
> So, is the original statement safe to use, or is this an undocumented
> "feature" that I use at my own risk?

It seems that you are safe. I found this in Books Online for SQL 2005:

READPAST can be specified for any table referenced in an UPDATE or
DELETE statement, and any table referenced in a FROM clause. When
specified in an UPDATE statement, READPAST is applied only when reading
data to identify which records to update, regardless of where in the
statement it is specified.

Speaking of SQL 2005, in SQL 2005, you probably want to replace your
table with Service Broker, but that's another story.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[msc...@eskimo.com]

Erland,

OK, that does make sense that READPAST is applied when identifying
which records to update.
Also, since we are now supporting SQL Server 2005 for our product (in
addition to SQL Server 2000), I will look into using the Service
Broker in these scenarios.

Thank you for your insite on this issue.