Hello,
I have a quite large set of legacy stored procedures. The stored procedures are not reentrant, i.e. can not safely be executed in parallel by multiple threads. The procedures are not called multiple times simultaneously in the system, because of this restriction. There's however no explicit constraint to prevent multiple calls to these stored procedures and thus it's possible to e.g. accidentally call the procedures many times simultaneously.
My problem is, how to prevent multiple simultaneous executions of the stored procedures? Is there a native way to do this? In modern programming languages there are language constructs to prevent simultaneous access to code and variables (synchronized in Java, lock in C#). Is there something similar in T-SQL? What could be a workaround?
One options could be to use SQL Server jobs to prevent simultaneous accesss - only one instance of a job can be running at time. This is, however, problematic since if the job cannot be started because another instance is already running, the second instance will never be started. This is not what I won't.
JM
Refer to Books Online about the use of sp_getapplock and sp_releaseapplock.
Also, refer to this post. And this one.
|||Thanks Arnie for your quick and good answer!
r,
JM
|||Yes..
You can achive it using few tricks..using sp_getapplock.
But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.
Sample:
Code Snippet
Create table MyTable
(
RowId int identity(1,1),
HitStartedAt datetime,
HitTimestamp datetime,
UserName varchar(100)
)
Go
Create proc LegacyProc (@.user varchar(100), @.CalledTime datetime)
as
Begin
Insert Into MyTable
Values(@.CalledTime, getdate(), @.user);
--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action
WAITFOR DELAY '000:00:10'
End
Go
Create Proc MyProc
(
@.user varchar(100)
)
as
Begin
Declare @.PorcName as NVarchar(1000), @.CalledTime datetime
Begin Tran
--To get the Current SP Name, it should be unique for each SP / each batch
SET @.PorcName =object_name(@.@.ProcID)
SET @.CalledTime = Getdate()
--Lock the Current Proc
Exec sp_getapplock @.Resource = @.PorcName, @.LockMode = 'Exclusive'
--Execute Your Legacy Procedures
Exec LegacyProc @.user, @.CalledTime
--Release the lock
Exec sp_releaseapplock @.Resource = @.PorcName
Commit Tran
End
