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.