CODEWITHSUNDEEP
sql-serverconcurrency
ren
ren

Reputation: 3993

when to use sp_getapplock

I have the following situation: stored procedure uses intermittently two tables. I have to execute this sp concurrently (like 50 at the same time). This gives me deadlocks at about 33% cases. The question is: is it appropriate to use sp_getapplock here? All I do is add:

exec sp_getapplock @Resource = 'resource_name', @LockMode = 'exclusive',@LockTimeout = '60000', @DbPrincipal = 'dbo'

as a first command in a transaction and everything seems to be working. Except for concurrency, but that's ok. What's unsettling is that I'm trying to do what database should actually do. Perhaps there are better alternatives or serious drawbacks of this approach?

Upvotes: 2

Views: 7132

Answers (1)

Dale K
Dale K

Reputation: 27387

You've probably resolved this now, but in case it helps. Large complex databases under load will almost certainly encounter deadlocks from time to time. It would be too difficult for the database to look ahead to the extent required to be able to determine whether there will be contention for resources later in the process. There are a lot of things you can do to minimise deadlocks, including good indexes, well structured table access and more (plenty of info out there). However there can legitimately be times where you are unable to structure code in such a way that deadlocks are avoided, and making use of sp_getapplock is definitely not a bad thing. As you have noted it does cause a bottleneck because you no longer have concurrency. But the benefit of eliminating deadlocks probably more than makes up for it performance wise. Unfortunately for me, I tried something similar in one of my SP's but the call to sp_getapplock is itself causing a deadlock - so its not always guaranteed to solve the problem.

Fundamentally it does come down to what you are trying to achieve. But be sure that the more complex database you are working with, the more chance you will need to manually tune aspects of it - it won't do it all for you, and thats why there is still a demand for good database gurus. I would say that unless you have a specific requirement for an SP to be single threaded that using sp_getapplock shouldn't be your first attempt at solving an issue - I've only used it 3 times or something. However given your situation I would definitely go with it because it solves your problem, doesn't require you to understand the complex internals of the SP and probably doesn't have any bad side effects. The most likely would be that it might slow down - but doesn't sound like you have noticed that?

Upvotes: 7

Related Questions