Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1717
  • Last Modified:

SQL stored procedure - prevent execution by multiple users at the same time

Hi experts,

Is there any way to prevent multiple users from executing a stored procedure at the same time?

Maybe it could just pause execution of their script while the other user completes the  execution?
0
JC_Lives
Asked:
JC_Lives
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Simone BSenior E-Commerce AnalystCommented:
0
 
JC_LivesAuthor Commented:
Ok this tells me how to cause the second execution to error-out... but can the second excution just wait until the first is complete? And give a message in the "messages" section saying that it's waiting for another call to execute?
0
 
Simone BSenior E-Commerce AnalystCommented:
No, this will not queue up the requests to run the sp. However, you can customize the message the second user receives, so that they know they have to try again in x amount of time.

I don't recall anything in SQL that will auto-execute the second request. Anyone else out there have an idea?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Is there any way to prevent multiple users from executing a stored procedure at the same time?
No direct way.  Stored Procedure are inherently designed for multiuser requests.

Maybe it could just pause execution of their script while the other user completes the  execution?
Just create a table and have a flag value put in it.  At the beginning of the procedure turn the flag to 1 meaning somebody is running the procedure and turn it off to 0 at the end of the procedure.  You then simply have to put a verify condition of the flag: if the flag is 0 run the query else do nothing.
0
 
JC_LivesAuthor Commented:
I see what you mean - but if the flag is 1 can we have the second query wait until the flag is back to zero?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
but if the flag is 1 can we have the second query wait until the flag is back to zero?
Yes you can pause the execution by using the WAITFOR DELAY statement..Something like...

declare @flag as bit
select @flag=column from flagtable

while @flag=1
begin
waitfor delay '00:00:02'
end
update flagtable set column=1
select......(your instructions)
update flagtable set column=0

Open in new window

Hope this helps...
0
 
Anthony PerkinsCommented:
And of course you would need to add TRY ... CATCH to your Stored Procedure to reset the flag to 0 even if there was an error.  Otherwise it is going to be a very long wait...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Otherwise it is going to be a very long wait>>
Indeed.  Many many things could go wrong and let the flag set to 1 creating an infinite loop.  That is the drawback in changing SQL Server inherent behavior.
0
 
JC_LivesAuthor Commented:
Thanks!! Thant really helps!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now