0

I am new to SQL server and the deadlocking like issues. I read articles about it. I want to understand following things:

  1. SQL server uses appropriate Lock Mode depending on the IsolationLevel I set while beginning the transaction. If this understanding is correct, what's the purpose of sp_getapplock ?
  2. I am running into the SQL deadlock issue. Several instances of my ASP.NET application running on different servers access the same database. If I use the appropriate IsolationLevel on transactions, do I still need to acquire SQL lock using sp_getapplock?

(I know more appropriate solution is to identify problematic transactions and fix them. But still it's more of a theoretical question.)

Learner
  • 4,661
  • 9
  • 56
  • 102

2 Answers2

0

Blocking and deadlocking are two different things. Please read this article for more details.

Following is copied from the article:

Developers and DBAs often think that their SQL Server instance is experiencing deadlocks when, really, it is experiencing severe blocking.

Blocking occurs when session A requests a lock on a resource (typically a row, page or table), but SQL Server cannot grant that lock because session B already holds a non-compatible lock on that resource.

This is a transient situation and can be completely resolved by the session B completing its work and releasing its locks. It is possible to have extensive blocking chains where multiple sessions are blocked waiting for a session that itself is blocked waiting for another session that is blocked and so on, repeating multiple time. However, at the head of the blocking chain will be a head ‘blocker’ that is not waiting for a lock. It may be waiting for some other resource, such as a latch, memory, or IO, but at least one session will not be waiting for a lock, and the blocking chain will clear as soon as the head blocker can continue processing.

A deadlock is different; it occurs when two or more sessions are waiting for each other, in such a way that none can complete. A deadlock can be viewed as a circular lock chain, where every single process in the blocking chain is waiting for one or more other processes in that same blocking chain.

Learner
  • 4,661
  • 9
  • 56
  • 102
0

sp_getapplock is not directly related to your problems. Applocks are custom defined locks, used by your application - for example when you need to synchronize with external data source, using some lengthy and complex procedure and you don't want multiple processes to run such procedure at once.

About deadlocks - you have to design your procedures to minimise deadlock possibility and then you need create some error handling to detect deadlocks and take appropriate action (eg retry 5 times, fail afterwards).

If you could post your deadlocking procedure code, then likely someone can help you redesign it.

Arvo
  • 10,349
  • 1
  • 31
  • 34
  • Thanks for your answer. The database is so huge and complex that detecting the resources causing deadlock is a challenge in itself. I have asked for the deadlock graph anyway. – Learner Mar 20 '17 at 11:30
  • Im my old code, often problem is in next succession: `begin tran; select xxxx; update xxxx; commit` - if xxxx contains same records for select and update, then deadlock is quick to appear. If you add `with(updlock)` hint to your `select`statement, many (not all) problems go away. – Arvo Mar 20 '17 at 11:40