SQL 2005 Session Synchronization

From CDYNE Wiki
Jump to navigation Jump to 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.

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

 2     @RC INT,
 3     @ResourceName NVARCHAR(255)
 5 -- the resource can be any unique name to lock on.
 6 SET @ResourceName = 'MyApplicationSpecialLock'
 8 -- lock the resource so that other users must wait. We will wait for a second.
 9 EXEC @RC = sp_getapplock 
10     @Resource = @ResourceName, 
11     @LockMode = 'Exclusive', 
12 /*
13  Is the owner of the lock, which is the lock_owner value when the lock was requested.
14  The value can be Transaction (the default) or Session. When the lock_owner value is Transaction, 
15  by default or specified explicitly, sp_getapplock must be executed from within a transaction.
16 */ 
17     @LockOwner = 'Session', 
18     @LockTimeout = 1000 -- expressed in Milliseconds (1 second)
20 /* 
21 RC=
22 0 The lock was successfully granted synchronously.
23 1 The lock was granted successfully after waiting for other incompatible locks to be released.
24 -1 The lock request timed out.
25 -2 The lock request was cancelled.
26 -3 The lock request was chosen as a deadlock victim.
27 -999 Indicates a parameter validation or other call error.
28 */
30 IF @RC NOT IN (0,1)
32    RAISERROR( 'Timeout getting the Resource Lock', 16, 1 )
33    GOTO ErrorHandler
34 END
36     /** Do some critical section work here **/
37     -- note, you should see a lock Type == App in this result set
38     exec sp_lock  -- this can be removed, it is only showing you the lock.
40 -- clean up as soon as another user can get access to the resource
41 EXEC @RC = sp_releaseapplock 
42     @Resource = @ResourceName,
43     @LockOwner = 'Session'
45 ErrorHandler:
46     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