SQL 2005 sp_GetAppLock --- When to call sp_ReleaseAppLock?

Question

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN  

DECLARE @res INT

EXEC @res = sp_getapplock 
 @Resource = 'This a Lock ID 3',
 @LockMode = 'Exclusive',
 @LockOwner = 'Transaction',
 @LockTimeout = 60000,
 @DbPrincipal = 'public'


if @res < 0 
begin
   declare @errorMessage nvarchar(200)

       set @errorMessage = case @res
  when -1 then 'Applock request timed out.'
  when -2 then 'Applock request canceled.'
  when -3 then 'Applock involved in deadlock'
  else 'Parameter validation or other call error.'
end

raiserror (@errorMessage,16,1)
end

/*
 INSERT AND UPDATE STATEMENTS HERE
 ...
 ...
*/

COMMIT TRANSACTION -- THIS RELEASES THE APPLOCK 

RETURN 0;
END TRY

BEGIN CATCH

-- ROLLBACK TRANSACTION IF NEEDED
IF @@TRANCOUNT > 0
   ROLLBACK

/*
Exception handling stuff here. Should I call sp_releaseapplock?
...
...
*/

-- return the success code
RETURN -1;      

END CATCH

Answer 1

From sp_getapplock

Locks associated with the current transaction are released when the transaction commits or rolls back.

So, it's not needed because you roll back.

However, if you'd like to be safe, I'd do it after the CATCH block and test first with APPLOCK_TEST. Normally, this would be a FINALLY block which we don't have.

I'd have it here so it's always executed. If the session continues, or connection pooling keeps it alive (does it? forget right now) then you rely on the COMMIT/ROLLBACK if it was not just before exit. Of course, anything that misses the CATCH block is going to be a severe aborting error anyway...