CODEWITHSUNDEEP
sqldesign-patterns
Ranch
Ranch

Reputation: 475

sql design prevent dual procceesing

I have a SQL stored procedure call it "StPro", It is called by a web-service.
I want that at all times there will be no more than one "StPro" working.
So I need an idea, what can I do in "StPro" to prevent another "StPro" to start working along. Tried having a value in a table that tells me if there is already a working "StPro", when "StPro" starts it sets the value to 1, when it ends it sets it to 0, if the value is already 1 the procedure ends without doing anything.
Problam is, if you start two "StPro"s at the same time, the value doesnt change on time to prevent the second one from starting. Thanks

Upvotes: 0

Views: 27

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can make Table(ID int), put PK on that table, and at top of your stored procedure add insert into Table values(1). And at the end of proc delete * from Table. Add Try Catch block and return from proc if error occured.

Upvotes: 1

Related Questions