Prevent a Stored Procedure from being executed twice at the same time

Question

I have a stored procedure for SQL Server 2000 that can only have a single instance being executed at any given moment. Is there any way to check and ensure that the procedure is not currently in execution?

Ideally, I'd like the code to be self contained and efficient (fast). I also don't want to do something like creating a global temp table checking for it's existence because if the procedure fails for some reason, it will always be considered as running...

I've searched, I don't think this has been asked yet. If it has been, sorry.

Koblas

Which version of sql-server are you using? – Bonina

Answer 1

yes there is a way. use what is known as SQL Server Application locks.

EDIT: yes this also works in SQL Server 2000.

Inconvincible

I've not tried it yet but this looks like the answer I need. Thanks! – Koblas

Answer 2

You can use sp_getapplock sp_releaseapplock as in the example found at Lock a Stored Procedure for Single Use Only.

But, is that what you are really trying to do? Are you trying to get a transaction with a high isolation level? You would also likely be much better off handling that type of concurrency at the application level as in general higher level languages have much better primitives for that sort of thing.

Ankney

Answer 3

One of the initial external links shared in the replies had helpful info but personally I prefer for standalone answers/snippets to be right here on the Stack Overflow question page. See below snippet for what I used and solved my (similar) problem. If anyone has problems (or adjustment suggestions) please chime in.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyLockedAndDelayedStoredProcedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [GetSessionParticipantAnswersFromEmailAddressAndSessionName]
GO

CREATE PROCEDURE [MyLockedAndDelayedStoredProcedure]    
  @param1 nvarchar(max) = ''
AS
BEGIN

DECLARE @LockedTransactionReturnCode INT
PRINT 'MyLockedAndDelayedStoredProcedure CALLED at ' + CONVERT(VARCHAR(12),GETDATE(),114);
BEGIN TRANSACTION
EXEC @LockedTransactionReturnCode =sp_getapplock @Resource='MyLockedAndDelayedStoredProcedure_LOCK', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 10000
PRINT 'MyLockedAndDelayedStoredProcedure STARTED at ' + CONVERT(VARCHAR(12),GETDATE(),114);

-- Do your Stored Procedure Stuff here
Select @param1;

-- If you don't want/need a delay remove this line    
WAITFOR DELAY '00:00:3'; -- 3 second delay
PRINT 'MyLockedAndDelayedStoredProcedure ENDED at ' + CONVERT(VARCHAR(12),GETDATE(),114);
COMMIT

END

-- https://gist.github.com/cemerson/366358cafc60bc1676f8345fe3626a3f

Rationality

Thanks! Since this took a little while for me to digest, I'd note that the key part of the solution is the sp_getapplock wrapped in BEGIN TRAN / COMMIT; the rest is for demonstration purposes. – Fricassee

Answer 4

how about locking a dummy table? That wouldn't cause deadlocks in case of failures.

Aceous

i can't see how this would help in any way. can you elaborate more? – Inconvincible

Answer 5

At the start of the procedure check if piece of data is 'locked' if not lock it

At end of procedure unlock the piece of data.

ie

SELECT @IsLocked=IsLocked FROM CheckLockedTable Where spName = 'this_storedProcedure'

IF @IsLocked = 1
    RETURN
ELSE
    UPDATE CheckLockedTable SET IsLocked = 1 Where spName = 'this_storedProcedure'

.
.
.

-- At end of Stored Procedure
    UPDATE CheckLockedTable SET IsLocked = 0 Where spName = 'this_storedProcedure'

Landsman

I think this is a race condition. Technically two closely timed sprocs could both perform the SELECT before either performed the UPDATE, if the threads are scheduled just right. – Floaty