SQL 2005 Session Synchronization

From CDYNE Wiki
Jump to: navigation, search

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.

Contents

Functions in SQL Server 2005

CDYNE's use

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.

Code Example

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'

Credits

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

Personal tools
Namespaces

Variants
Actions
Navigation
Tools

Trial Key