Deadlock and Blocking in SQL Server are two familiar terms seen in SQL Server. But, many are getting confused with these terms in the SQL Server. The users get messed up with these though they are not the same. Both these characteristics are different and you will get to know about the difference between deadlocks and blocking in SQL Server from the coming sessions. Before moving on to the difference, let’s get to know about these separately.
What Is Blocking?
As the word suggests ‘Blocking’, it’s stopping the user from accessing something. The resource maybe currently accessed by some other user, at a time only one of the user can access the resource; making other wait for the resource. Blocking is an inevitable characteristic of the RDBMS. To be more descriptive look the example;
Example: Suppose process A needs the resource R1 and at the same time process B too comes to collect the resource R1. Since A has R1, B cannot access it. Only one of the processes can access the resource at a time, the other has to wait. It’s just like a queue, where you will have to wait until the one before you get the things and leave.
The blocking occurs in SQL Server because of one SPID (Server process ID) holds a lock on one specific resource and the second SPID tries to lock the same resource. When first SPID leave the resource, the other can easily access it. Blocking is not so troubling to the Server; the system performance is not affected with it. However, with more SPIDs it may cause troubles.
What Does Deadlock Refer?
Consider the scenario;
Process P1 holds the resource R1 and process P2 holds resource R2. Thereafter, sometimes process P1 will be in need of R2, may request for R2 and process P2 requests for R1. But, since R2 is with process P2 and R1 is with P1 the transaction doesn’t proceed. Both the transactions will not be benefited. This results in deadlocks.
Both the process will wait each other to finish the transactions but, won’t be accomplished. Neither P1 nor P2 will be completed. It’s called deadlock since none of the process will attain their transactions or processing.The deadlock results in starvation and contributes to the slowing down of system performance. The processes which are yet to come will be affected and ends up in the deadlock queue; deadlock cycle. Until if the resources are not shared, deadlock state cannot be removed.
From the above para, you would have got an idea about both the characteristic; deadlocks and blocking. Let’s see around the differences between both.
The Differences Between Deadlock And Blocking In SQL Server
Deadlocks are severe problem causing instance since all the processes or the transactions can be troubled, if it occurs. None of the processes will be completed i.e. no benefits will be there for those processes that entered in deadlock condition. But, it’s not the case with the blocking.
Blocking occurs when one two of the processes access the same resource. But, at a time only one can make use of the resource and the other will have to wait until the first one release the resource.
Both these condition can be seen in the SQL Server but, blocking is not noticed much since it release the resource after use and the other can make use of it. However, there are critical situations when all the SPIDs come together. But, deadlock situation is very bad and it should never happen.
An overview of the conditions that may affect the SQL Server is described. Both the (Deadlock And Blocking In SQL Server) conditions in its peak affects the Server but, deadlock slows down the system performance