Sam Sam - 1 month ago 19
SQL Question

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?

Answer

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.