SQL 2005 Session Synchronization
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
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.
1 DECLARE 2 @RC INT, 3 @ResourceName NVARCHAR(255) 4 5 -- the resource can be any unique name to lock on. 6 SET @ResourceName = 'MyApplicationSpecialLock' 7 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) 19 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 */ 29 30 IF @RC NOT IN (0,1) 31 BEGIN 32 RAISERROR( 'Timeout getting the Resource Lock', 16, 1 ) 33 GOTO ErrorHandler 34 END 35 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. 39 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' 44 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