Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using sp_getapplock as a transaction level in a procedure which in turn calls another procedure where again we use sp_getapplock with the same resource names. I wanted to know whether this scenario will cause the deadlock issue.

create proc a as
begin

begin tran
exec sp_getapplock @Resource=index_234, @LockMode='Shared', @LockOwner='Transaction', @DbPrincipal = N'dbo';

exec sp_getapplock @Resource=IndexItem_Insert_234, @LockMode='Exclusive', @LockOwner='Transaction', @DbPrincipal = N'dbo';

.... several transactions


exec proc b

commit

end



create proc b as
begin

begin tran

exec sp_getapplock @Resource=index_234, @LockMode='Exclusive', @LockOwner='Transaction', @DbPrincipal = N'dbo';

exec sp_getapplock @Resource=IndexTag_234, @LockMode='Exclusive', @LockOwner='Transaction', @DbPrincipal = N'dbo';

.... several transactions


commit

end

When calling the procedure b directly from the UI seems to work fine, but when the call is made to Procedure a it causes a deadlock issue.

Please provide your ideas on this scenario.

share|improve this question
    
@Yasir sp_getapplock is a SQL server stored procedure. –  StanleyJohns Mar 28 '13 at 11:33
add comment

1 Answer

up vote 3 down vote accepted

.... several transactions

What exactly does that mean? You mean several statements? Or are you really starting new transactions?

That being said, acquiring repeatedly is OK. If a transaction already has a resource, reacquiring it again will always succeed. Callee can acquire the resource again, provided the callee request is in the same mode as the caller. But in such scenarios is difficult to avoid escalation attempts (caller acquires Shared and calee asks for Exclusive) which will lead to deadlocks. The situation you encounter is a clear example of exactly this: caller acquires index_234 in shared and callee acquires the same index_234 in Exclusive.

Ideally you should not have to do this though. The workflow should acquire the locks only once, directly at the right level. Ambiguity between caller and callee, like in your case, indicates laps in the design as is not clear who's responsibility is to acquire the lock. The more cases of such ambiguity you have, the more likely things will go wrong. And the workflow should never attempt to escalate a Shared to Exclusive.

Usually the solution is to separate the work into external facing API and internal use one. The external one acquires the lock, the internal one does the work with the resources already locked:

create procedure sp_a
 ...
 acquire X
 ...
 exec sp_b_internal
 ...

create procedure sp_b
 ...
 acquire X
 ...
 exec sp_b_internal


create procedure sp_b_internal
 ...

As a general advice: try to restrict your app to at most one app lock at a time. As soon as you try to joggle two locks simultaneously you will deadlock contiguously unless you know exactly what you're doing.

share|improve this answer
    
Thanks a lot that saved me big time. –  SQL_yogi Mar 28 '13 at 12:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.