IsolationLevel, Lock Modes, deadlocks and sp_getapplock in MS SQL Server

Question

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

Answer 1

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.