To restrict stored procedure to run just once when multiple users access at a time

In our online booking engine, when two users click to book at the same time, the stored procedure runs twice. How to retrict that. We want only one user to access at a time. Please guide us on the lock systems. We use C# and SQL server 2012. We would want to restrict this in either one of these.

Thanks in advance Experts.
gracesoftAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
In T SQL you can do this very simply by having a table with a record and checking the content of the record when starting your code. If it is says "Running" then the code should wait; if it says "Stopped" then the code should set it to say "Running" and then continue. The wait period should be a random time and can be set using the WaitFor command.

A more sophisticated technique in T SQL is the Application Lock, with which you can create an application semaphore. The Application Lock is not a lock on any object - except perhaps its name! Your application runs sp_getapplock with a name - the name of the application, perhaps - and if that name is not already locked it will be until that same application releases it. Another application requesting that same lock will be held waiting for the lock to be released. For information reference http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005.

Hope this helps

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
Hi,

I've used the first technique previously, especially with several independent services that ran independently.

HTH
  David
ste5anSenior DeveloperCommented:
On the database side you would normally use explicit transactions in the first place. In C# you would use a mutex (local) or more sophisticated IPC like MSQM over the network.

We want only one user to access at a time.

This will limit the number of concurrent users drastically.

Why not using a queue for booking jobs and async execution using SQL Server Broker?
chrisl6969Commented:
Here is how you do it using SQL:
https://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/

if you would like to do it in another stored procedure, see here:
http://stackoverflow.com/questions/19990810/wrapping-a-transacted-stored-procedure-inside-a-transaction

if you want to do it manually in your stored procedure:
(pseudo code)

declare int @record
begin trans
set @record = (select top 1 id from record_locks where id=@myid)
ISNULL ( @record , -1 )
if (@record is null) insert into record_locks @myid
commit trans

if (NOT(@record is null)) return --record is locked

--record is not locked

your code ...
your code ...
your code ...

delete from  record_locks  where id = @myid

Open in new window


just make sure there are ways to clean up the missed delete in case something happens.  maybe add a timestamp to the record lock table and have the record lock check to see if a certain time has elapsed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.