Prevent Stored Procedure Execution multiple time by same user at same time

Question

I tried SQL lock with stored procedure to prevent multiple execution of same stored procedure at a same time. but it seems it is not preventing. Below syntax will create getlock and run the same transaction five time. but all the 5 time it is achieving the lock. I might be wrong somewhere but i have tried same with my stored procedure but over there also same issue is happening.

BEGIN TRANSACTION
DECLARE @LockId INT
Exec @LockId =sp_getapplock @Resource='ABC', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 10
IF(@LockId<0)
BEGIN
PRINT 'LOCK FAILED'
ROLLBACK TRANSACTION
RETURN
END
PRINT 'LOCK SUCCEED'

COMMIT TRANSACTION
GO 5

Answer 1

Hi @dhruv jaiswal

but all the 5 time it is achieving the lock

When @LockOwner is set as "Transaction", the lock is released either with a call to sp_releaseapplock or when the transaction is committed or rolled back.
For more details , please refer to this article: Prevent multiple users from running the same SQL Server stored procedure at the same time

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, if you run the above in a single query window five times, you will get the lock five times, if no other process is holding the lock.

But before you run the above in a different query window:

BEGIN TRANSACTION
Exec sp_getapplock @Resource='ABC', @LockMode='Exclusive'

That is, leave the transaction open. Now when you run the script, you will find that you fail to get the lock every time.

Make sure that the windows are connected to the same window.