Semaphore is an abstract for controlling access by multiple threads (sessions) to a common resource. Binary semaphore, it can be called Mutex, or Lock/Unlock, allows only one thread access a resource at a time. Back old days, in C++ programming, critical section object is used for this purpose. In C#, keyword lock(obj){code block} and Monitor class implements binary semaphores. Once a object is locked by one thread, other threads who need to acquire the lock on the object will have to wait until the lock on the object is released by thread previously held. SQL Server is an multi-threading application. It uses semaphores internally to control the access to the record, for instance, while you are modify a record, an exclusive lock is placed on the record and released when modification finishes. This is a binary semaphore. In T-SQL, you can implement binary semaphore by locking row(s) and application locks.

The first, let’s use tables with built-in locking mechanism to implement the binary semaphore. Let’s use inventory as an example. You have an Inventory header table. The status which is recorded in the header can be Not Started, Calculating, and Calculated. When you calculate inventory for one header entry, you need only and only one session to perform the calculation.

use tempdb 
if OBJECT_ID('Inventory') is not null
	drop table Inventory
go
Create table Inventory( ID int primary key, Status varchar(20))
insert into Inventory values (1, 'Not Started')

---acquire semaphore
update Inventory with (readpast)
	set Status = 'Calculating'
where ID = 1
	and Status = 'Not Started'
if(@@ROWCOUNT =0)
begin
	--failed acquiring semaphore
	return
end
---semaphore is acquired sucessfully
---code to perform inventory calculation

--release semaphore
update Inventory
	set Status = 'Calculated'
where ID = 1

This is very straight forward. There is no way for 2 processes performing inventory calucate upon one inventory header entry. A consideration of this approach is that if the calculation process is terminated in middle by any means, the status will not automatically revert back to Not Started. Optionally, you can put entire code block into a big transaciton. You may notice that I used readpast hint purposely. This is becase when you run everything in a transaction, when one session takes very long time to perform the calculation on inventory 1, if the second session is trying to calculate the same entry, it will wait until the first session is complete. With readpast, the second session will quite (failed acquiring semaphore) while session one is calculating.

This is the traditional way to create and control semaphores. SQL Server provides procedure sp_getapplock to allow you to define your own locks and procedure sp_releaseapplock to release the locks you defined. For more information regarding appliction lock, please refer to SQL Server Book Onling. In my following code block, I will give you two procedures MonitorEnter and MonitorExist, which work exactly same as Monitor.Enter() and Monitor.Exist() in C#.

if object_id('dbo.MonitorEnter') is not null
	drop procedure dbo.MonitorEnter
go
if object_id('dbo.MonitorExit') is not null
	drop procedure dbo.MonitorExit
go
create procedure dbo.MonitorEnter (@SyncObject nvarchar(255))
as
begin
	declare @ret int
	exec @ret = sp_getapplock @Resource = @SyncObject, @LockMode = 'Exclusive', @LockOwner = 'Session'
	return @ret
end
go 
create procedure dbo.MonitorExit(@SyncObject nvarchar(255))
as
begin
	begin try
		exec sp_releaseapplock @Resource = @SyncObject, @LockOwner = 'Session'
	end try
	begin catch
		-- I don't care exceptions here
	end catch
end 
go

Now let’s one one query window and run

exec MonitorEnter 'Code 1'
print 'Code 1'

you will see “Code 1” printed. Now let’s open the second window and run exactly the same code

exec MonitorEnter 'Code 1'
print 'Code 1'

What you will see is that the session will wait. Now go back to the first session and run

exec MonitorExit 'Code 1'

and then check session 2 — “Code 1” is printed.
Since there’s already enough information in BOL, I will not repeat it here. Few things keep in mind

• Lock Resource: a string, same string presents the same lock resource. It’s similar to SyncObj in lock(SyncObj){} in C#.
• Lock Mode: It only supports IS, S, IX, X, and U locks but locks can be converted, you can also get SIX and UIX lock by conversion. See my post Locking and Blocking (4) – How It Works
• Scope: Locks can be session scope or transaction scope. Within same session, differt locks will can be granted to different scopes. Only locks within the same scope may tigger lock conversion. Session scoped locks will be released when you explicitly release them or session is terminated(disconnected). Transaction scoped locks will be released whenever transaction is completed or you explicitly release them by calling sp_releaseapplock
• Timeout: Waiting time in milliseconds for conflicting lock being release by other sessions.

In my next post of this series, I will talk about implementation of counting semaphore.