SQL Server Deadlocks - Easy Quick Start Guide

If you find the topic of SQL Server deadlocks a little intimidating and mysterious, you are not alone. But fear not, this Quick Start lays out the practical shortcut I bet is sufficient for most scenarios.

Definition: What is a Deadlock

A deadlock occurs when 2 threads are competing for similar resources, and Thread 1 HAS A and WANTS B, and Thread 2 HAS B and WANTS A. They’re stuck. SQL Server picks a loser and throws error 1205 "Transaction... has been chosen as the deadlock victim."

The BIG PROBLEM if your application has a lot of deadlocks is unpredictable application behavior. This can especially show up in apps that call large stored procs where the failing statement gets rolled back, but the proc itself may just keep on trucking with the next statement. This is all based on what kind of error handling you're doing, whether you have XACT_ABORT on, whether you're doing explicit transactions with BEGIN TRAN or not, etc. Very Probably your application is not coded to deal with this, and just ASSUMES that the proc either always works completely or does nothing. ALAS, the story of errors in SQL Server is more nuanced than that.

Suffice it say, you want to avoid this problem by finding your deadlock sources and fixing them.

This is Easier than You Think

When I first started troubleshooting deadlocks many moons ago, I quickly ran into lots of very technical reference material that was educational, but didn't really help me quickly solve my problem. Instead it felt like this was "deep stuff" requiring super SQL Server mastery. In ignorance and fear (being honest), I shied away from articles that mentioned "trace flags" (my mistake) and in the end adopted a technique that was laborious and time consuming, and assumed that's just the way it had to be. Like the query processor facing too many indexes, I ran out of analysis time and chose a bad plan.

Happily when I finally decided to do another deep dive on this topic, I found out that general deadlock troubleshooting is actually really easy.

Step 0 - Skip Profiler

If you're like me, then maybe your first instinct was to use the deadlock graph features in Profiler traces. Yay, visuals! As it turns out, that is a painful and less useful path for basic troubleshooting. Instead, try the following method first.

How to Troubleshoot SQL Server Deadlocks - Summary

1. DBCC TRACEON(1222,-1)


2. Check your SQL Server logs for Deadlock details on exactly which queries are causing the deadlocks.


3. Optimize the appropriate Update process.

Easy! Okay, let's unpack that.

How to Troubleshoot SQL Server Deadlocks - Details

1. DBCC TRACEON(1222,-1)
   
   
   
   1. You just turned on a trace flag.
      
      
      
      1. What, trace flag, ack, what the heck? Relax, no, you don't have to do this from the command line, and you don't have to restart SQL Server, not for this one. It's an easy flag you can just flip on and off.
   
   
   2. Turning this on immediately starts dumping Deadlock information into the SQL Server log as deadlocks are encountered.


2. Check your SQL Server logs for Deadlock details on exactly which queries are causing the deadlocks.
   
   
   
   1. The Logs will contain a list of entries that start with "deadlock list", followed by a lot of details about the different processes involved, each with their own "process" entry. Within each process entry, you're looking for the "execution stack" which will show you EXACTLY what command and what line of what stored procedure or ad hoc query statement was involved in the deadlock.
   
   
   2. DID YOU HEAR THAT? It shows you EXACTLY what statements are conflicting to cause the deadlock. From there you'll be able to look at the processes involved and probably quickly spot one that is using a non-optimal approach to updating some data.


3. Optimize the appropriate Update process.
   
   
   
   1. 80% of the time I bet you'll have an UPDATE process that is doing a big table-locking SELECT inside of a transaction that could be moved outside of the transaction, or the UPDATE is searching on a field in the where clause that doesn't have an Index and just needs one added.
   
   
   2. If it's more complicated than that, see below for references.

References and Hairy Details

So that's the easy version of what can be a tough topic. For more details I recommend these resources:

• Bart Duncan's (MSFT) classic post on deadlock troubleshooting using trace flag 1222, with great shots of what you'll see in the Log:
  
  
  
  • http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx


• Jonathan Kehayias' EXHAUSTIVE guide, with all the ways you can capture traces and explicit advice on how to resolve different types of locks:
  
  
  
  • https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/


• MSDN's "Detecting and Ending Deadlocks":
  
  
  
  • http://technet.microsoft.com/en-us/library/ms178104%28v=sql.90%29.aspx


• MSDN's "Trace Flags":
  
  
  
  • http://technet.microsoft.com/en-us/library/ms188396%28v=sql.90%29.aspx

Feedback

Helpful? Off-base? Sound off in the comments, feedback welcome!