Hi Guys,
Furthermore, this could be useful for building a more robust procedure.
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.
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';
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'
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!
Here the "support me" button:
Previous post: SQL Server, Today I tell you why your Query is slow. Recompilation problems