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.
Thanks in advance Experts.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
Hi,
I've used the first technique previously, especially with several independent services that ran independently.
HTH
David
I've used the first technique previously, especially with several independent services that ran independently.
HTH
David
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.
This will limit the number of concurrent users drastically.
Why not using a queue for booking jobs and async execution using SQL Server Broker?
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/
if you would like to do it in another stored procedure, see here:
http://stackoverflow.com/q
if you want to do it manually in your stored procedure:
(pseudo code)
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.
https://www.mssqltips.com/
if you would like to do it in another stored procedure, see here:
http://stackoverflow.com/q
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial