This may be an alternative to writing your own logical locking architecture. Microsoft has provided a couple of hooks into the built in lock manager. Using the sp_getapplock and sp_releaseapplock, developers are able to implement a custom locking strategy outside of SQL Server internals. Here is an example of how these procs can be used:
DECLARE
@RC INT,
@ResourceName NVARCHAR(255)
-- the Application resource that we are trying to "lock"
SET @ResourceName = 'TableName'
-- lock the resource so that other users must wait. We will wait for a second.
EXEC @RC = master.dbo.sp_getapplock
@Resource = @ResourceName,
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 1000
-- Possible return codes 0, 1, -1, -2, -3, -999 (see BOL [sp_getapplock] for explanations)
IF @RC NOT IN (0,1)
BEGIN
RAISERROR( 'Timeout getting the Resource Lock', 16, 1 )
GOTO ErrorHandler
END
/** Do some work here **/
-- note, you should see a lock Type == App in this result set
exec sp_lock
-- clean up as soon as another user can get access to the resource
EXEC @RC = sp_releaseapplock
@Resource = @ResourceName,
@LockOwner = 'Session'
ErrorHandler:
PRINT 'Skipped work'