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

Question

question

dhruvjaiswal-5393 asked May 12, '22 LiHongMSFT-3908 edited May 13, '22

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

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

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-12T09:12:14.063Z

LiHongMSFT-3908 answered May 12, '22 LiHongMSFT-3908 edited May 13, '22

Hi @dhruvjaiswal-5393

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.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dhruvjaiswal-5393 · May 12 at 10:09 AM

Hello @LiHongMSFT-3908,

So you are saying same name lock at a same time can be achieved. Because if you see all 5 request are happening at same time if you want you can also keep delay to check.

My real question is i want to prevent my stored procedure execution when same request comes multiple time.

I have userid as parameter and I keep resourcename : userid.

Now let say if I call sp at the same time with same userid then only one request should be executed and other should get lock is not acheived

0 ·

LiHongMSFT-3908 dhruvjaiswal-5393 · May 13 at 02:48 AM

Hi @dhruvjaiswal-5393
As I said, the lock is released when the transaction is committed.Which means if there is an transaction not committed, you will get 'LOCK FAILED' 5 times.
How about this:

 DECLARE @LockId INT
 Begin tran
 Exec @LockId =sp_getapplock @Resource='ABC', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000
 SELECT @@SPID [session_id], @LockId [return code], GETDATE()
 Waitfor Delay '00:00:08'            
 Commit

The sp_getapplock call takes out a lock to the resource "ABC" and holds it as long as the transaction is alive. In this case it will wait 8 seconds and then execute the COMMIT, which will release the lock.
For more details, please refer to this article:https: Prevent multiple users from running the same SQL Server stored procedure at the same time

Best regards,
LiHong

0 ·

Answer 2 · 2022-05-12T20:47:20.777Z

ErlandSommarskog answered May 12, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question