In SQL Server, the stored procedures sp_getapplock and sp_releaseapplock are used to put locks on application resources and release them, respectively. These procedures are useful when you want to prevent multiple users from accessing the same resource simultaneously.

sp_getapplock

The sp_getapplock procedure is used to put a lock on an application resource. It accepts the following arguments:

• @ResourceName: The name of the resource on which you want to put the lock.
• @LockMode: The mode of the lock that you want to put on the resource.
• @LockOwner: The owner of the lock.
• @LockTimeout: The timeout value for the lock.
• @DbPrincipal: The user, role, or application role that has permissions to access the object in the database.

The return value of sp_getapplock can be:

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

sp_releaseapplock

The sp_releaseapplock procedure is used to release a lock on an application resource. It accepts the following arguments:

• @ResourceName: The name of the resource on which you want to release the lock.
• @LockOwner: The owner of the lock.
• @DbPrincipal: The user, role, or application role that has permissions to access the object in the database.

The return value of sp_releaseapplock can be:

• 0: The lock was released successfully.
• 999: Indicates an invalid parameter or other call error.

Example

Let’s consider a scenario where we want to prevent users from executing the same stored procedure at the same time. We can achieve this by using sp_getapplock and sp_releaseapplock.

Here’s an example:

CREATE PROCEDURE [dbo].[ProcInsertEmployees]
    @EmployeeName VARCHAR(200),
    @JobTitle VARCHAR(150),
    @PhoneNumber VARCHAR(50),
    @PhoneNumberType VARCHAR(10),
    @EmailAddress VARCHAR(250)
AS
BEGIN
    DECLARE @returnCode INT

    BEGIN TRY
        EXEC @returnCode = sp_getapplock
            @Resource = 'ProcInsertEmployees',
            @LockMode = 'Exclusive',
            @LockOwner = 'Session',
            @LockTimeout = 50

        IF @returnCode NOT IN (0, 1)
        BEGIN
            RAISERROR('Unable to acquire exclusive lock on ProcInsertEmployees', 16, 1)
            RETURN
        END

        WAITFOR DELAY '00:00:15';

        -- Insert Employee Details
        INSERT INTO tblemployees (employeename, jobtitle, phonenumber, phonenumbertype, emailaddress)
        VALUES (@EmployeeName, @JobTitle, @PhoneNumber, @PhoneNumberType, @EmailAddress)

        EXEC @returnCode = sp_releaseapplock
            @Resource = 'ProcInsertEmployees',
            @LockOwner = 'Session'
    END TRY
    BEGIN CATCH
        IF @returnCode IN (0, 1)
        BEGIN
            EXEC @returnCode = sp_releaseapplock
                @Resource = 'ProcInsertEmployees',
                @LockOwner = 'Session',
                @DbPrincipal = 'public'
        END

        DECLARE @ErrMsg VARCHAR(4000)
        SELECT @ErrMsg = ERROR_MESSAGE()
        RAISERROR(@ErrMsg, 15, 50)
    END CATCH
END

In this example, the stored procedure ProcInsertEmployees inserts data into the tblemployees table. It acquires an exclusive lock on the resource ProcInsertEmployees using sp_getapplock and releases the lock using sp_releaseapplock after the data is inserted.

To test this scenario, you can execute the stored procedure in two different query windows simultaneously. The first session will successfully acquire the lock and release it after waiting for 15 seconds, while the second session will be unable to obtain the lock and exit.

By using sp_getapplock and sp_releaseapplock, you can ensure that only one user can execute a specific stored procedure at a time, preventing any conflicts or data inconsistencies.

Summary:

In this article, we have discussed the usage of sp_getapplock and sp_releaseapplock stored procedures in SQL Server. These procedures are useful for putting locks on application resources and releasing them, ensuring that only one user can access a specific resource at a time.