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

This page was last updated on May 01, 2006 04:28 PM.