Implementing Semaphore At Database Level (SQL Server)

Hi,

I am Bishnu Patro.
Recently, I had an requirement to execute a particular stored proc certain number of times (it should be configurable depending upon the available Memory). The reason, is if the stored proc is executed certain number of times simultaneously, it used to take more time to execute since the all available memory is consumed and there is no free memory left. So, we decided to limit the number of parallel execution at the stored procedure level and it should be configurable. If it configured to run the stored proc simultaneouly to a maximum of 2. Then when the next request comes for execution of the stored proc, this request will wait for one of the execution to complete.

I have implemented this by way of implementing customized semaphore at database level.

I have created a table MultipleInstanceJobConfiguration, to keep track of the number of simultaneous execution of the stored proc.


CREATE TABLE [dbo].[MultipleInstanceJobConfiguration](
[JobType] [nvarchar](50) NULL,
[Total] [smallint] NULL,
[Available] [smallint] NULL,
[IsEnabled] [bit] NULL
) ON [PRIMARY]


Here, jobtype is the name of the stored proc.
Total is the number of simultaneous execution of the stored proc.
Available is at any point how many simultaneous execution available.
IsEnabled is to whether you want to enable this feature or disable it.


The following statement inserts for a jobtype SI:

INSERT INTO [dbo].[MultipleInstanceJobConfiguration] ([JobType] ,[Total] ,[Available] ,[IsEnabled]) VALUES ('SI',2,2,1)
GO


The following stored proc is used to take a lock on an object:


CREATE Proc [dbo].[AcqLock]
@Resource_Name1 as nvarchar(255),
@Lock_Mode1 as nvarchar(32),
@Lock_TimeOut1 as int
As

DECLARE @LockResult int

EXECUTE @LockResult = sp_getapplock
@Resource = @Resource_Name1,
@LockMode = @Lock_Mode1,
@LockOwner = 'Session',
@LockTimeout = @Lock_TimeOut1


while( @LockResult <> 0)
BEGIN
WAITFOR DELAY '00:00:01'
EXECUTE @LockResult = sp_getapplock
@Resource = @Resource_Name1,
@LockMode = @Lock_Mode1,
@LockOwner = 'Session',
@LockTimeout = @Lock_TimeOut1
END
GO


The following stored proc is used to release a lock on an object:


CREATE Proc [dbo].[ReleseLock]
@Resource_Name1 as nvarchar(255)
As
EXECUTE sp_releaseapplock
@Resource = @Resource_Name1, @LockOwner = 'Session'
GO


The following stored proc is an example implementing semphore:


CREATE PROC [dbo].[StoredProcUsingSemaphore]
AS

--Updating Configuation Available Count On Start of stored proc
declare @total int
declare @available int
declare @isEnabled bit
select @total = Total,@available = Available,@isEnabled = IsEnabled from dbo.MultipleInstanceJobConfiguration with (nolock) where JobType like 'SI'
WHILE(@total > 0 and @isEnabled = 1)
BEGIN
if( @available > 0)
BEGIN
Begin Try
EXEC dbo. AcqLock 'MY_MULTIPLEINSTANCES','Exclusive',0
select @available = Available,@isEnabled = IsEnabled from dbo.MultipleInstanceJobConfiguration with (nolock) where JobType like 'SI'
if(@available > 0 and @isEnabled = 1)
BEGIN
Update dbo.MultipleInstanceJobConfiguration
Set Available = Available - 1
where JobType like 'SI'

INSERT INTO MetricsLoadProcessingLog VALUES (@loadID,'SI_Job_Configuration',null ,1 ,'Decremented Available Count',GETUTCDATE())

Exec dbo. ReleseLock 'MY_MULTIPLEINSTANCES'
BREAK
END
End Try
Begin Catch
Exec dbo. ReleseLock 'MY_MULTIPLEINSTANCES'
End Catch

END
WAITFOR DELAY '00:00:15'
select @available = Available,@isEnabled = IsEnabled from dbo.MultipleInstanceJobConfiguration with (nolock) where JobType like 'SI'

END


----Your piece of code will go here.....


-----Updating Available Count on Finish
if(@total > 0 and @isEnabled = 1)
BEGIN
EXEC dbo. AcqLock 'MY_MULTIPLEINSTANCES ','Exclusive',0

Update dbo.MultipleInstanceJobConfiguration
Set Available = Available + 1
where JobType like 'SI' and Available < Total


Exec dbo. ReleseLock 'MY_MULTIPLEINSTANCES'
END

GO


Please let me know if the post was useful....