Preventing a Stored Procedure to run simultaneously

Question

Preventing a Stored Procedure to run simultaneously

Satheesh E.P.

SSCrazy

Points: 2509
More actions
July 28, 2010 at 6:17 am

#225519

Hi All,
I have a requirement where I have to stop a stored procedure to run simultaneously. (i.e) If user01 had executed a particular stored procedure, no other user should be able to run it until user01 finishes it's execution.
I didn't want to try by introduction a log table and executing based on that. Instead is there any way I can find out from any DMV's or any other method whether that particular stored procedure is currently executing now or not.
---------------------------------------------------
Thanks,
Satheesh.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, obviously it would be betrter to redesign the proc so it can run concurrently, but that's a future enhancement.

I think you are right, you'll have to use some sort of audit/log/semaphore table that the procedure checks for a value, and an IF..ELSE either runs or skips past based ont he value in the table.

Create Procedure OnlyOneAtATime

AS

BEGIN

--ignore if the flag was left accidentally on...assuming 5 minutes

IF NOT EXISTS(SELECT * FROM MyBusyTable WHERE IsRunning = 1 AND LastRun >= dateadd(minute,-5,getdate()) )

BEGIN

INSERT INTO MyBusyTable(hostname,appname,ipaddress,IsRunning,LastRun)

SELECT

host_name(),

app_name(),

client_net_address,

1,

getdate()

FROM sys.dm_exec_connections WHERE session_id = @@spid

--do stuff

--done doing stuff, remove the

DELETE FROM MyBusyTable

END

ELSE

BEGIN

--do nothing?, as someone is currently running it.

PRINT 'BUSY'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

or you can use Application Locks with the procedure name as the resource.

http://msdn.microsoft.com/en-us/library/ms189823.aspx

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 3

@lowell - Thanks for pointing me to a better solution.

@ken - I am analyzing your solution. I will get back if I have and doubt. Thanks.

---------------------------------------------------
Thanks,
Satheesh.

dant12 SSCrazy Points: 2712 More actions · Answer 4

i agree with ken. we have a similar requirement and we use this inside our proc

put this in the beginning of the proc

DECLARE @PROC VARCHAR(20)

DECLARE @SUCESS AS INT

SET @PROC = 'Your Proc Name'

EXEC @SUCESS = sp_getapplock @PROC, 'Exclusive', 'Session', 100

WHILE @SUCESS < 0

BEGIN

EXEC @SUCESS = sp_getapplock @PROC, 'Exclusive', 'Session',

100

END

and this at the end

EXEC sp_releaseapplock @PROC, 'session'

--
Thiago Dantas
@DantHimself

nigel. SSChampion Points: 11636 More actions · Answer 5

A cautionary note.

Take care with the error handling in the solution using sp_getapplock, you could get into an infinte loop.

In most cases the WHILE statement should read:

WHILE @SUCCESS = -1

...

Other error return values will need to be handled outside the loop.

See the documentation here: http://msdn.microsoft.com/en-us/library/ms189823.aspx

Nigel
How to post questions