Table of Contents
Identifying and dealing with SQL server deadlocks is all part and parcel of what a DBA has to handle as part of their role.
Of course if you are a newcomer to this field or are just interested in finding out why certain conflicts can arise and what you can do to overcome them, you might want to learn more about the tricky issue of deadlocks. To that end, here is a deep dive into the three main types that you are likely to encounter and the steps you can take to prevent them reoccurring in the future.
Initial overview
Getting to grips with the concept of deadlock issues that can arise within SQL Server is straightforward. In short, a deadlock will rear its ugly head in the event that a pair of processes attempt to vie for control of a particular resource at the same time, without either one being able to achieve exclusive access to said resource.
This will not bring the entire database grinding to a halt, but it will monopolize resources unnecessarily and compromise performance as a result. The software will automatically terminate one of the offending processes to keep the ball rolling, so the sooner you can pinpoint the problem and remedy it, the faster you will be able to restore the server to full operational capacity.
Cycle deadlocks explained
The first type of deadlock that you are likely to encounter is known as a cycle deadlock, and as the name suggests it results from a vicious cycle of resource locking being created between two processes.
In this case, the first process and the second process will already have exclusive locks on two different resources, and will be attempting to swap places. However, because each holds an exclusive lock on the resource it is using, this exchange cannot take place because it is prevented in software.
Cycle deadlocks are one of the most common forms you are likely to encounter when managing an SQL Server ecosystem, yet that does not mean they can be overlooked.
Conversion deadlocks scrutinized
Another deadlock type that is simple enough to grasp from a theoretical standpoint, this conundrum will occur when an attempt to convert lock types is made but a separate thread already has a shared lock on the resource that the first thread requires.
It is worth noting that there are actually a trio of sub-categories that exist within the broader umbrella of conversion deadlocks. The first is SIU (share with intent update) which sees a thread with a set of shared locks as well as update locks creating the conflict. The second is SIX (share with intent exclusive) which takes place when shared and exclusive locks are possessed by a single thread. The third is UIX (update with intent exclusive) which arises when update and intent exclusive locks exist as distinct from one another but occur simultaneously.
Key lookup deadlocks examined
The final type of SQL Server deadlock that you should keep an eye out for is a key lookup deadlock, which tends to emerge in the case that a pair of sessions run on a single statement and thus butt heads when attempting to gain an exclusive lock to pinpoint the necessary data.
In some scenarios it is possible for a whole tsunami of key lookup deadlocks to proliferate in unison, which is clearly not ideal, even if it is not a complete disaster.
As you will now see, there is a similar thread connecting all of the major types of deadlocks, which is that they come down to resource management and locking conflicts, while being separate from standard database locking.
Detecting and dealing with deadlocks
The best news in all of this is that SQL Server is capable of helping you identify deadlocks and also allows you to remedy them if necessary.
Through the use of trace flags, you can track the deadlocks that emerge within a specific session, determine how frequently they occur and use this as a starting point to find a solution.
One of the best ways to overcome deadlock problems is to revise any queries which are clearly causing problems and ensure that they are written consistently. This should be done to optimize the order in which data is modified, creating uniformity and lessening the likelihood of conflicts arising.
Another common cause of deadlocks which is an easy fix results from resources being overloaded when they are taxed by the simultaneous running of many different processes. Try to carry out index maintenance outside of peak periods of use for this reason.
Finally, since deadlocks are ultimately inevitable, you should make sure to set the most appropriate priority for processes so that those which are vital to the smooth running of the server do not get terminated.
Conclusion
That's it, at least for now: we hope that this post will be useful for those who are looking to investigate the most common causes of SQL deadlocks. Needless to say, you will need to experiment and investigate the issue of SQL Server deadlocks thoroughly to maximize your chances of success, but all of your effort will be worth it in the long run.
