by Wes Moody | May 13, 2022 | Blog, SQL Server | 3 comments
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 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!
Innocent K Nchamchamon July 10, 2022 at 8:10 pm
Perfect explanation here; wasn’t so clear about locking, blocking and deadlock until after this beautiful illustration.
Thank you so muchReply
venadelleon January 22, 2023 at 5:38 pm
This demonstration is just awesome.
Reply
Jayantaon June 12, 2023 at 10:42 am
Thank You
Reply
Submit a Comment
This site uses Akismet to reduce spam. Learn how your comment data is processed.