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

gracesoft
gracesoft used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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
David ToddSenior Database Administrator

Commented:
Hi,

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

HTH
  David
ste5anSenior Developer
Commented:
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?
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial