UPDLOCK / READPAST / ROWLOCK

READPAST
Specifies that the Database Engine not read rows that are locked by other transactions.

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken.

UPDLOCK ( UPDLOCK , READPAST )
Specifies that update locks are to be taken and held until the transaction completes.

Example -

-- Use the READPAST optimizer hint to skip the locked row & at the same time
-- requesting for UPDLOCK
IF ( SELECT au_id FROM authors WITH( UPDLOCK , READPAST )
WHERE au_id = '172-32-1176' ) IS NULL
PRINT 'Row is locked for update / delete.'
ELSE
PRINT 'Row is not locked for update /delete.'

-- To handle multiple locking of multiple rows that satisfy a condition
-- Count of rows should be know before to use this check

if COALESCE( ( SELECT COUNT( * ) FROM authors WITH( UPDLOCK , READPAST )
WHERE state = 'CA' ) , 0 ) <> 15
PRINT 'Some rows are locked for update / delete.'
ELSE
PRINT 'Rows are not locked for update /delete.'


-- Using LOCK_TIMEOUT to timeout the SELECT statement
SET LOCK_TIMEOUT 1000 -- 1 second timeout
DECLARE @au_id
SET @au_id = ( SELECT au_id FROM authors WITH( UPDLOCK )
WHERE au_id = '172-32-1176' )
IF @@ERROR <> 0
PRINT 'Row is locked for update / delete.'
ELSE
PRINT 'Row is not locked for update / delete.'


To get more info visit http://msdn2.microsoft.com/en-us/library/ms187373(SQL.90).aspx