SQL Server, prevent users from executing the same stored procedure at the same time. SP_GETAPPLOCK and SP_RELEASEAPPLOCK

Hi Guys,

Today just a short post in which we will see how to prevent users from executing the same stored procedure at the same time.

I think it's a very useful thing to know and in some cases it can save us the day.

Furthermore, this could be useful for building a more robust procedure.

 

Enjoy the reading!

 

Sp_getapplock & Sp_releaseapplock

First of all, you should know that SQL Server offers two procedure which have been developed for this purpose.

These two stored procedure are sp_getapplock and sp_releaseapplock.

The logic behind these functions is as follows: 

We have a resource that we want to be executed by only one process at a time.
With stored procedure Sp_GetApplock we ​​can set a lock on a resource so that a second process finds this resource already in use. The stored procedure sp_releaselock instead removes the lock from the resource.

Before giving an example to understand how to use these functions let's see the parameters:

The sp_getapplock puts the lock on the application resource and has the following parameters:

EXEC Sp_getapplock
    @Resource = 'resource_name',
    @LockMode = 'lock_mode',
    @LockOwner = 'lock_owner',
    @LockTimeout = 'value',
    @DbPrincipal = 'database_principal'; 

Return an integer value with the following meanings:

0    The requested lock was successfully granted synchronously
1    The requested lock was granted successfully after waiting for other locks to be released
-1   The requested lock timed out
-2   The requested lock was canceled by the caller
-3   The requested lock was chosen as a deadlock victim
999  This indicates the invalid parameter or other call error

The sp_releaseapplock release the lock and has the following parameters:

EXEC Sp_releaseapplock
    @Resource = 'resource_name',
    @LockOwner = 'lock_owner',
    @DbPrincipal = 'database_principal'  

and return an integer value with the following meanings:

0    The lock was released successfully
999  This indicates the invalid parameter or other call error

And now as usually 

.....it is the time of the example!

The example

Suppose you have a procedure MYPROC (or a query, it does not matter) that perform some operation.

The following SP will execute our MYPROC Stored procedure preventing users from executing it at the same time.

First of all we will use for the sp the TRY / CATCH blocks.

In the TRY block we call the sp_getapplock procedure. Now, If the lock is granted we can call the MYPROC procedure otherwise an error will be returned.

Always inside the TRY clock if i had acquired a lock i must release it calling the SP_ReleaseLock.

CREATE PROCEDURE SP_Test_Get_And_Release_Lock AS
BEGIN
    DECLARE @returnCode INT

    BEGIN try

		EXEC @returnCode = Sp_getapplock
			 @Resource    = 'SP_Test_Get_And_Release_Lock',
			 @LockMode    = 'Exclusive',
			 @LockOwner   = 'Session',
			 @LockTimeout = 50
 
		/* 
			0 - The requested lock was successfully granted synchronously
			1 - The requested lock was granted successfully after waiting for other locks to be released 
		*/
			 
		IF @returnCode not in (0,1)
		BEGIN
		  RAISERROR ( 'Acquire exclusive Lock on the SP_Test_Get_And_Release_Lock is not possible', 16, 1)
		  RETURN
		END

		EXEC MYPROC
		
 
		EXEC @returnCode = Sp_releaseapplock
		     @Resource  = 'SP_Test_Get_And_Release_Lock',
		     @LockOwner = 'Session'

    END try

    BEGIN catch

		IF @returnCode in (0,1)
		BEGIN
		  EXEC @returnCode  = Sp_releaseapplock
			   @Resource    = 'SP_Test_Get_And_Release_Lock',
			   @LockOwner   = 'Session',
			   @DbPrincipal = 'public'
		END

		DECLARE @ErrMsg VARCHAR(4000)
		SELECT @ErrMsg = Error_message()
		RAISERROR(@ErrMsg,15,50)

    END catch
END  

Note:

In this example the LockOwner is the session and since we are not inside a transaction we have to call the SP_ReleaseLock in the Catch Block.

We can call the SPGetAppLock with the parameter LockOwner equal to transaction.

In this case "also" the lock will be under transaction, so in case of an error the lock will rollbacked and it is not necessary to call the SP_ReleaseLock.

 

Isn't it really that simple?

yes, simple but yet very userful technique. So, tell me if you found useful too!!

That's all for today mates!

Stay tuned for the next post... i promise i will take you to work with me!

See you soon!

 

Luca

 

 

 

 

Here the "support me" button:  

 

Previous post: SQL Server, Today I tell you why your Query is slow. Recompilation problems