Counting semaphores restricts number of concurrent sessions from accessing the same resource. It’s useful when code blocks perform well when number of concurrent users is under a certain number. For instance, SQL Server merge replication implements semaphores to limit number of subscribers to access publication at the same time.
When acquiring a semaphore, you pass the name of the resource to identify the semaphore and the size of the semaphore. The procedure will use applock_mode to test if an IX lock on the name of the semaphore. If there is already one, it means the session already acquired a semaphore, which it should not get an extra one, then the procedure raise an error and return. If there isn’t, the session has never got a semaphore which it should get one, then the process will spin on all potential sub-resources until it can successfully acqure an X lock on a sub-resource and then return the value that can be used to generate the sub-resource name with the semaphore name — slot number. This number should be kept in a variable in you program and use it to release the semaphore.
create procedure AcquireSemaphore ( @Resource nvarchar(200), -- Name of the semaphore @TotalCount int = 3 -- Size of the semaphore ) as begin declare @ResourceInternal nvarchar(255) declare @i int = 1, @ret int = -1 if(applock_mode('public', @Resource, 'session') = 'IntentExclusive') begin raiserror('You have acquired semaphore', 16,1) return -1; end exec sp_getapplock @Resource = @Resource, @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = -1 while 1=1 begin select @i = 1 while @i <= @TotalCount begin select @ResourceInternal = cast(@i as varchar(10)) + '-' + @Resource exec @ret = sp_getapplock @Resource = @ResourceInternal, @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 0 if @ret >=0 return @i; select @i = @i +1 end waitfor delay '00:00:00.001' --sleep(1) end end go create procedure ReleaseSemaphore ( @Resource nvarchar(200), --name of the semaphore @Slot int -- slot number return by AcquireSemaphore ) as begin declare @ResourceInternal nvarchar(255) = cast(@Slot as varchar(10)) + '-' + @Resource exec sp_releaseapplock @ResourceInternal, 'Session' exec sp_releaseapplock @Resource, 'Session' end go
To test this, you can open 4 windows to run following code in each.
declare @slot int exec @slot = AcquireSemaphore 'CountingSemaphore' select @slot --exec ReleaseSemaphore 'CountingSemaphore', 1
The execution in the forth window will not return immediately unless you release one slot from the semaphore “CountingSemaphore” or you close one of the window that has successfully acquired semaphore. This is also the greatest feature by using application lock — the lock resources are released whenever the connection is broken.