Session synchronization is useful when you need different sql server
sessions to be synched. You can use Logical Locking with Microsoft's
built in lock manager.
We use this for our Phone Notify service when
we synchronize our dialer boxes asking for phone numbers to dial. This
allows us to be 100% sure that the same call won't be picked up by 2 or
more dialing boxes at the same time. Using this method we have been able
to scale our systems to dial 1000's of numbers within seconds.
DECLARE
@RC INT,
@ResourceName NVARCHAR(255)
-- the resource can be any unique name to lock on.
SET @ResourceName = 'MyApplicationSpecialLock'
-- lock the resource so that other users must wait. We will wait for a second.
EXEC @RC = sp_getapplock
@Resource = @ResourceName,
@LockMode = 'Exclusive',
/*
Is the owner of the lock, which is the lock_owner value when the lock was requested.
The value can be Transaction (the default) or Session. When the lock_owner value is Transaction,
by default or specified explicitly, sp_getapplock must be executed from within a transaction.
*/
@LockOwner = 'Session',
@LockTimeout = 1000 -- expressed in Milliseconds (1 second)
/*
RC=
0 The lock was successfully granted synchronously.
1 The lock was granted successfully after waiting for other incompatible locks to be released.
-1 The lock request timed out.
-2 The lock request was cancelled.
-3 The lock request was chosen as a deadlock victim.
-999 Indicates a parameter validation or other call error.
*/
IF @RC NOT IN (0,1)
BEGIN
RAISERROR( 'Timeout getting the Resource Lock', 16, 1 )
GOTO ErrorHandler
END
/** Do some critical section work here **/
-- note, you should see a lock Type == App in this result set
exec sp_lock -- this can be removed, it is only showing you the lock.
-- clean up as soon as another user can get access to the resource
EXEC @RC = sp_releaseapplock
@Resource = @ResourceName,
@LockOwner = 'Session'
ErrorHandler:
PRINT 'Timeout'
Special thanks to Todd Carrico for putting me in the correct direction.
If you run his code in the master database it works fine. But, due to
the master.dbo.sp_getapplock and not using the master.dbo. in the
releaselock statements, it would only work on the master database.
http://sqljunkies.com/WebLog/tcarrico/archive/2005/04/12/11296.aspx