Limiting concurrent execution of a T-SQL stored procedure

Hello,

I have a quite large set of legacy stored procedures. The stored procedures are not reentrant, i.e. can not safely be executed in parallel by multiple threads. The procedures are not called multiple times simultaneously in the system, because of this restriction. There's however no explicit constraint to prevent multiple calls to these stored procedures and thus it's possible to e.g. accidentally call the procedures many times simultaneously.

My problem is, how to prevent multiple simultaneous executions of the stored procedures? Is there a native way to do this? In modern programming languages there are language constructs to prevent simultaneous access to code and variables (synchronized in Java, lock in C#). Is there something similar in T-SQL? What could be a workaround?

One options could be to use SQL Server jobs to prevent simultaneous accesss - only one instance of a job can be running at time. This is, however, problematic since if the job cannot be started because another instance is already running, the second instance will never be started. This is not what I won't.

JM

Refer to Books Online about the use of sp_getapplock and sp_releaseapplock.

Also, refer to this post. And this one.

|||

Thanks Arnie for your quick and good answer!

r,

JM

|||

Yes..

You can achive it using few tricks..using sp_getapplock.

But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.

Sample:

Code Snippet

Create table MyTable

(

RowId int identity(1,1),

HitStartedAt datetime,

HitTimestamp datetime,

UserName varchar(100)

)

Go

Create proc LegacyProc (@.user varchar(100), @.CalledTime datetime)

as

Begin

Insert Into MyTable

Values(@.CalledTime, getdate(), @.user);

--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action

WAITFOR DELAY '000:00:10'

End

Go

Create Proc MyProc

(

@.user varchar(100)

)

as

Begin

Declare @.PorcName as NVarchar(1000), @.CalledTime datetime

Begin Tran

--To get the Current SP Name, it should be unique for each SP / each batch

SET @.PorcName =object_name(@.@.ProcID)

SET @.CalledTime = Getdate()

--Lock the Current Proc

Exec sp_getapplock @.Resource = @.PorcName, @.LockMode = 'Exclusive'

--Execute Your Legacy Procedures

Exec LegacyProc @.user, @.CalledTime

--Release the lock

Exec sp_releaseapplock @.Resource = @.PorcName

Commit Tran

End