SQL 2005 sp_GetAppLock --- When to call sp_ReleaseAppLock?

Question

Sorry about the last message. I did a paste over my question. Long question short, when using sp_GetAppLock inside of a try / catch block, should I call sp_ReleaseAppLock when an exception is caught?

---

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.'endraiserror (@errorMessage,16,1)
             end

 SELECT...
 INSERT...
 UPDATE...

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...

Answer 2

If you roll back the transaction, there is no need to release the app lock in your catch.

From http://msdn.microsoft.com/en-us/library/ms189823.aspx:

Locks placed on a resource are associated with either the current transaction or the current session. Locks associated with the current transaction are released when the transaction commits or rolls back. Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.