Assure the release of an application lock

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  July 28, 2009 at 2:30 am

  #133513

  Hello,

  we have a long running procedure that should not run parallel. If it runs in a transaction we get problems with the transaction log, so we decided to use an application lock

  sp_getapplock

  At the end of the procedure the lock is released, also if errors occure that we can catch.

  sp_releaseapplock

  The Problem: when somebody aborts the procedure, that application lock will stay alive. How can we handle this best? Maybe there is a way to determine and displace "zombie locks" at the begin of the procedure?

  Thanks!

  Tobias

• Ken McKelvey

  SSCoach

  Points: 18484

  More actions

  July 28, 2009 at 3:12 am

  #1030903

  You could try putting sp_releaseapplock in the CATCH block as well as the TRY block.

  As you seem to be using SESSION applocks I think you would have difficulty is determining which ones are 'zombies'.

• shamassaeedmr

  SSC-Addicted

  Points: 441

  More actions

  July 28, 2009 at 5:45 am

  #1030966

  if you have only problem of calling procedures by mutlipul users then use NOLOCK in your query.