Locking, Blocking and Deadlocking in sql server

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session “A”. In such type of scenario, SQL Server knows that once the resource will be free from session “B”, it will be available for session “A” and it puts the session “A” on wait until all the locks on resource are clear. Note that session “A” will wait but it would not be killed by SQL Server.

Deadlock

Deadlock occurs when two sessions are waiting for a lock to clear on the other while holding it’s own resources. If session “A” has a lock on resource “R1” and waiting for resource “R2” and another session “B” has a lock on resource “R2” and waiting for resource “R1”, this circular chain will generate the deadlock situation in database. This is a permanent blocking situation and would not be resolve by waiting. SQL server is capable to detect deadlocks and can declare one of the process as deadlock victim and kills that process. The process which declared as deadlock victim is the one which is less resources intensive and has less cost than another process. We can also set the DEADLOCK_PRIORITY using SET statement to control this behavior of SQL Server.

So, now you should clear about these confused terms Lock, Block and Deadlock. Put your comments below and don’t forget to rate this article.