CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sam
Sam

Reputation: 1908

sp_getapplock transaction within transaction

I have a very quick question in regards to sp_getapplock. Is calling sp_getapplock with the same resource (lock id) from one stored procedure to another allowed?

For example:

CREATE PROCEDURE [uspTest1]
(
    ...
    ...
)
AS
BEGIN TRY
    BEGIN TRANSACTION
        ...
        ...
        EXEC @RC = sp_getapplock @Resource = "Test 123",
                                 @LockMode = 'Exclusive',
                                 @LockOwner = 'Transaction',
                                 @LockTimeout = 60000,
                                 @DbPrincipal = 'public';
        IF @RC NOT IN (0, 1) THROW 50000, 'Unable to obtain lock', 1;
        ...
        ...
        EXEC [uspTest2] ..., ..., ...
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW
END CATCH


-- Test 2
CREATE PROCEDURE [uspTest2]
(
    ...
    ...
)
AS
BEGIN TRY
    BEGIN TRANSACTION
        EXEC @RC = sp_getapplock @Resource = "Test 123",
                                 @LockMode = 'Exclusive',
                                 @LockOwner = 'Transaction',
                                 @LockTimeout = 60000,
                                 @DbPrincipal = 'public';
        IF @RC NOT IN (0, 1) THROW 50000, 'Unable to obtain lock', 1;
        ...
        ...
        ...
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW
END CATCH

Since the @Resource (lock id) are the same, will it fail?

Upvotes: 2

Views: 2541

Answers (2)

Rachel Ambler
Rachel Ambler

Reputation: 1604

Yes, you can call it multiple times - however if you call it twice you MUST release it twice. If the second call is stronger than the first then the result will be a union of the two.

Source BOL - sp_getapplock (Transact-SQL)

If sp_getapplock is called multiple times for the same lock resource, but the lock mode that is specified in any of the requests is not the same as the existing mode, the effect on the resource is a union of the two lock modes. In most cases, this means the lock mode is promoted to the stronger of the lock modes, the existing mode, or the newly requested mode. This stronger lock mode is held until the lock is ultimately released even if lock release calls have occurred before that time. For example, in the following sequence of calls, the resource is held in Exclusive mode instead of in Shared mode.

Source BOL - sp_releaseapplock (Transact-SQL)

Remarks

When an application calls sp_getapplock multiple times for the same lock resource, sp_releaseapplock must be called the same number of times to release the lock

Upvotes: 1

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

I would expect that the call to sp_getapplock from within uspTest2 return 0, i.e. "The lock was successfully granted synchronously", because you are holding this lock already in this transaction.

I think you are still in the same transaction and it is OK to call sp_getapplock several times within the same transaction.

You should try and check.

You may have other problems with nested transactions, if you use them as you've shown, though. For example, if for any reason ROLLBACK is called from uspTest2, it will rollback all nested transactions, everything to the point when BEGIN TRANSACTION was called in uspTest1. Do you really want it?

Instead of using nested BEGIN TRANSACTION in uspTest2 consider using SAVE TRANSACTION savepoint_name and then ROLLBACK savepoint_name.

Upvotes: 3

Related Questions