SQL 2005 sp_GetAppLock --- 何时调用 sp_ReleaseAppLock? [英] SQL 2005 sp_GetAppLock --- When to call sp_ReleaseAppLock?

问题描述

对最后一条消息感到抱歉.我在我的问题上做了一个粘贴.长问题简短，当在 try/catch 块内使用 sp_GetAppLock 时，我应该在捕获异常时调用 sp_ReleaseAppLock 吗?

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

推荐答案

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

但是，如果您想安全一点，我会在 CATCH 块之后进行，并首先使用 APPLOCK_TEST.通常，这将是我们没有的 FINALLY 块.

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.

我会把它放在这里，所以它总是被执行.如果会话继续，或者连接池让它保持活动状态(是吗?现在忘记了)，那么你依赖 COMMIT/ROLLBACK，如果它不是在退出之前.当然，任何错过 CATCH 块的东西无论如何都将是一个严重的中止错误......

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

这篇关于SQL 2005 sp_GetAppLock --- 何时调用 sp_ReleaseAppLock?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！