When I was learning about Microsoft SQL Server issues a DBA needs to understand, I was reading about deadlocks. At first, I was confused because it sounded like the definition of blocking, which I was already more familiar with. I re-read it a couple of times and still wasn’t getting it. However, after compiling some information from a few different sources, I put this together to help others who might be in the same boat. I hope this helps!
Blocking vs. Deadlocks in Microsoft SQL Server
Blocking occurs as a result of two (or more) processes needing the same resource. The problem is that in Microsoft SQL Server, only one process can have access to a resource at a time. So, if the first process is already utilizing (or locking) the desired resource, then the second process is blocked from accessing it. Microsoft SQL Server will force the second process to wait until the first process is finished. The diagram below illustrates what happens:
Blocking
Deadlocks are different, but they can be easily confused at first glance. Like blocking, a deadlock involves two processes that need specific resources to complete. However, unlike blocking, the two processes are not trying to get the same resource. A deadlock occurs when Process 1 is locking Resource A and Process 2 is locking Resource B. In order for Process 1 to complete and free up Resource A, it needs to put a lock on Resource B. However,in order for Process 2 to complete and free up Resource B, it needs to put a lock on Resource A. So we get a deadlock (think of it like a stalemate in chess). The diagram below illustrates the deadlock.
Deadlock
In short, blocking occurs when two processes need the same resource. The second process will typically wait for the first to release the lock, then it will finish its job. On the other hand, a deadlock occurs when two processes are at a “stalemate” nobody’s going anywhere, and neither side can get what it needs because they need a lock on each other’s processes in order to finish their jobs. Typically, Microsoft SQL Server will resolve the deadlock on its own by rolling back the process that has done the least work, allowing the other process to move forward and hopefully finish unimpeded. Eventually, the process that was rolled back will restart and finish unimpeded as well.
If you have any questions, feel free to reach out to us!