Every time we talk about deadlock, you will see DBA frowning on this topic. DBA believes that Developers are creating the deadlocks and developers believe that DBA’s should be taking care of the deadlocks. Essentially, it is such a subject which requires DBA and Developers to work together to resolve the issue. In this blog post, we will see a brief history of the deadlock and modern approach of resolution.
A Brief History
Deadlocks have been constant trouble makers for database professionals for years. In all the perfect world deadlocks shows up from nowhere and keep on happening again and again.
Locking in SQL Server is a normal occurrence. The matter of fact when any transactions attempt to access a data or resource, it usually blocks other transactions to access the same resources. Once the first transactions complete its work, it releases the locking from the resource and the resources are available to another transaction.
However, deadlocking is very different from the blocking. It happens when two or more transactions block each other from consuming the resources they need to complete the transactions. This is classic catches 22 situations. In this case both the transactions forever wait on each other complete, resulting neither of them moving forward at all. In this scenario, SQL Server automatically resolves the deadlock by terminating one of the transactions.
DBAs all these years have been constantly struggling to identify the root cause of the deadlocking and also resolving them. Let us see a few of the classic tricks and modern take on them.
Lock the DeadLock
How do we know there is a deadlock and not a normal blocking? Well, if it is just blocking the waiting transactions will automatically execute when the first transactions will complete. However, if it is a deadlock, SQL Server knows that there is no real success there and will terminate one of the transactions. The transactions which will be terminated will be in the most cases lease expensive transaction.
SQL Server reports the error 1205 when a deadlock happens and you can see that in the error log as well as in message area if you are running your query in SQL Server Management Studio.
What should a DBA/developer do when they see error 1205 – honestly they should jump quickly to their system and try to identify where exactly the deadlocks are happening and try to prevent its recurrence.
How to Analysis Deadlocks?
First of all, whenever there is a deadlock, we need to find a deadlock graph which contains information about the resources and transactions involved in the deadlock. In SQL Server 2000, you can use the traceflag 1204 and SQL Server 2005 onwards you can use traceflag 1222 to identify XML Deadlock Graph. You can also use SQL Profiler to capture the deadlock as well but I personally do not like this method as I believe SQL Profiler also adds lots of overhead on the SQL Server. In the past, I have seen people using Service Broker Events along with the WMI (Windows Management Instrumentation) as well. Again, I am not personally a big fan of that method. Remember that our ultimate goal is to get the Deadlock Graph from all the tools which are discussing so far.
I really started to enjoy working with the deadlocks since SQL Server 2008. From this version, Microsoft introduced system_health Extended Events Session. Once you get the deadlock graph from the extended events, you can easily understand which resources need to be looked at.
How to Resolve Deadlocks?
I wish the answer to this question was that simple and I can explain in just one blog post. However, let us discuss three different ways you can resolve the deadlocks.
Method 1: Bookmark Lookup Deadlocks
My experience says 90% of the deadlocks are of this kind of deadlocks or at least my clients are facing this kind of deadlocks. We can easily solve this kind of deadlock with the help of indexes. However, to create such index, you need to properly study deadlock graph and afterward carefully create an index. Please do not use missing indexes script available online free to resolve this problem as they do not really help in this particular situation.
Method 2: Change the Order of Resource Access
Once in a while, I also come across this kind of deadlock as well, where I see transactions are updating and selecting the data in different order. I often see this happening when multiple processes and queries are running in parallel. There are multiple ways to resolve it and the easiest is to solve it by changing the order of accessed resources. If that does not help you can also depend on the different isolation level to solve the issues of the deadlocks.
Method 3: Third Party Tools
My personal favorite third-party tool for analyzing deadlocks is Spotlight Cloud and Spotlight Tuning Pack. It is Powerful, Cost Effective, & Easy and helps us to react to issues quickly. I take help of this product when I have to go beyond the symptom to find the real cure of performance issues.
Action Point
If you want to learn more about deadlocks you can register here to hear me talking about SQL Server Deadlock Analysis and Resolutions – Unconventional Approach.
Reference: Pinal Dave (https://blog.sqlauthority.com)
