What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on her opponent, and Batman has his. This would be a superhero (and super) deadlock.
When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. Then, each task requests to lock the data the other task is already holding. Both tasks wait for the other to give up. Neither does. SQL Server could let this showdown continue indefinitely, but it won’t. It picks one task – usually, whichever will be the least expensive to roll back – as the victim, and that task is killed.
How do I know if it’s happening to me?
You might notice slowness in your application. Users might complain about slowness or error messages. And, you’ll see a message in the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.”
Your server has encountered a deadlock.
How can I capture more information about it?
You have several options: you can enable a trace flag to write more information to the log, you can capture deadlocks graphs using Profiler or Extended Events, and you can track the number of deadlocks occurring using Performance Monitor.
Use trace flags to write to the log
Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command
|
1 |
DBCC TRACEON (1204, 1222) |
Here’s a small sample of what would appear in the log:
No one wants to read through that, manually parsing it to figure out what database it occurred in, what tasks were involved, and which was the victim. Wouldn’t it be great if this was represented graphically?
Capture a deadlock graph with Profiler or Extended Events
It is – you just have to know where to look to for a deadlock graph. In SQL Server 2005, 2008, and 2008R2, I prefer to use a Profiler trace; in SQL Server 2012 and 2014 I rely on Extended Events.
Profiler
When you set up a Profiler session to gather deadlock information, select the “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events. Make sure you go to the Events Extraction Settings tab and select the option “Save Deadlock XML events separately”. This ensures that the .xdl files – the graphs – will be saved as separate files. When you start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event.
The task with the blue X on it is the victim. By holding your mouse over the process oval, you can see what statement was being executed by that task. The Key Lock rectangles will help you find the object and index that the locking and blocking occurred on.
Extended Events
In the future, Profiler will be removed from SQL Server. Extended Events (EE) is taking its place. You have two options for getting deadlock information using EE. First, there is the system_health session that, by default, runs continuously. You can mine this for deadlocks that have happened in the past. You can also set up your own session to capture only deadlock information (and any other relevant events).
If using the system_health session, filter on “xml_deadlock_report”. If setting up your own session, capture the same event. If you are using the GUI available in 2012 and 2014, you can open the file and view the deadlock graph on the “Deadlock” tab.
If parsing the results using T-SQL and XQuery, you would use the .query method to extract the deadlock XML, like such:
|
1 |
SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph |
This generates the XML, which you would then save as an .xdl, then close and re-open with SSMS to view the graph.
Track Performance Counters
Performance Monitor (PerfMon) is valuable for tracking many statistics. One of the metrics you can track for SQL Server is SQLServer:Locks – Number of Deadlocks/sec. This is the least informational of the methods mentioned here – it only gives you a count of how many are happening per second in your system. It can be helpful, however, to track this to see what times of the day produce the most deadlocks. You can then use the other methods mentioned here to research what is happening during that time to cause the deadlocks.
Don’t be a victim!
Take charge of your SQL Servers! Become familiar with detecting deadlocks, finding out what caused them, and fixing them! For more tools and advice, visit Locking and Blocking in SQL Server.
33 Comments. Leave new
Hi,
Question on the “SQLServer:Locks – Number of Deadlocks/sec” perfmon counter.
This measures deadlocks that happen to be occurring at the exact time this counter is polled?
If that is the case, it may not be representative of the deadlock occurrence, as it would be very circumstantial to happen to poll it at exactly the right time the deadlocks occurred.
In my case, I poll this counter every 2 minutes, so I wonder how useful my data would be.
Thank you for this link and webcasts, I am a loyal fan!
Marios Philippopoulos
Correct. If you’re looking for an average idea of how many occur in your system on a day-to-day basis, that PerfMon counter is a good start. If you need a more in-depth analysis at any one point in time – say, users complain the system regularly locks up at 9:00 am – then you would want a more in-depth tool.
Hi Jes,
Thanks for sharing such a nice article. I have a question on deadlocks. We have a database where we are experiencing deadlock issues but if we use a few months older copy of the same database with same structure we don’t get deadlock issues. There is more data in latest copy but difference is not huge. We are wandering what might be causing deadlock issues on the latest copy when no structure changes has been done.
Your early response is much appreciated.
Kind Regards
Rauf Gill
Are you running the same queries against both sets of data? Have you looked a the execution plans to see what is different?
Please provide query to get dead lock information- thanks.
Murari – check out the post above. Enjoy!
I think you only posted a piece of it:
SELECT DeadlockEventXML.query(‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph
generates error:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column “DeadlockEventXML” or the user-defined function or aggregate “DeadlockEventXML.query”, or the name is ambiguous.
Nice article. Thanks a lot.
Great article, thanks. Is there a delay in events getting to the Extended Event log? Had a deadlock over half an hour ago but it doesn’t show up.
I’m looking at the screenshot in “Use trace flags to write to the log” it only seems to show one query?
What use is that if we can’t se the other query? (2 queries are needed to deadlock?)
Also are there any drawbacks to enabling those 2 flags?
Peter – yeah, generally speaking anytime you enable trace flags, that gives you non-default routes through the SQL Server engine code, so you’re using less-tested code.
Peter – not necessarily, even 1 query can experience parallelism deadlocks by itself. The screenshot just doesn’t show the whole screen.
I captured deadlock graph with sql profiler in SQL 2008 r2. The deadlock victim request mode is ‘X’ where us owner mode is ‘U’ and the other resource request mode is ‘U’ and owner mode ‘U’ – Both transactions are trying to update one table with same index (in this case clustered index) as shown on the rectangle key info. I can see the queries, server process ids …. but need help how to prevent these kind of deadlock please?
Jared – this is kinda beyond what we can explain in a blog post comment, but check out our indexing training, videos, and blog posts like this:
https://www.brentozar.com/archive/2011/09/indexing-for-deletes/
Great analogy. I love Wonder Woman. I used to have a deadlock example I used in classes involving Luke and Han. And I had action figures to go along with it.
You said: “When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. ”
Actually, deadlocks can occur on other resources besides data. Most common (after data) is deadlocking on thread resources. Less common is deadlocking on memory resources.
I know this post was just talking about deadlocking on data resources, but your sentence makes it sound like all deadlocks are on data.
Cheers
Kalen
One of the prettiest deadlock graphs I’ve ever seen was on parallel threads. It looks like a very ornate Christmas tree ornament.
Is there any chance to activate the “Save Deadlock XML events separately” option in a server side trace without using Profiler? I generally prefer this way of tracing by using a SQL Script but I could not see any difference in the generated Trace Definition file created from two different traces with the above option turned on and off. they were 100% similar, so the option to get the XDL files seems to be ignored…
Hi Brent Ozar,
If we use extended Events/Trace flags as system parameters/Performance counters for long time to retrieving dead lock information from server, Is creating any performance issues. Please help me.
Thanks in advance.
When I save the XML results as a .xdl file and reopen in ssms i’m getting an “invalid” error. Please help.
Additionally, when I click the graph tab, I don’t see anything remotely close to what you show in your screenshot above. Just a bunch of lines:-(
How do I automatically get mail of deadlock graph(Graphically) through t-sql or powershell.
Kamlesh – for Q&A, your best bet is to head over to http://DBA.StackExchange.com.
Hello,
When I run this query against my local 2012 instance:
SELECT DeadlockEventXML.query(‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph
I get this:
Cannot find either column “DeadlockEventXML” or the user-defined function or aggregate “DeadlockEventXML.query”, or the name is ambiguous.
That query looks pretty woefully incomplete. It’s missing a FROM, at minimum.
I copy and paste what was post on this article and get same error.
Paul – she’s saying LIKE that. You’re going to have to write your own query. She only got you started. If you’re looking for a pre-baked solution to deadlocks, go pick up a monitoring tool.
Brent – thanks so much for the pedantic and useless response. Clearly, people reading this article are looking for the steps to capture deadlock information through query. Some steps appear to be missing.
Zeke — you’re commenting on a three year old post by someone who doesn’t work here anymore.
If you’re looking for a query to do it, the internet is full of them.
Don’t be afraid to Google what you’re after, buddy.
On a hiring interview: We hire you if you can explain deadlock to us. The applicant: Hire me and I explain deadlock to you 🙂
Thanks for a good article.
HAHAHA, I see what you did there. Nicely done.
I think the Wonder Woman – Batman – Cheetah analogy needs a diagram!
IS THERE A WAY TO CAPTURE THE COMPLETE XML OF THE DEADLOCK IN THE CATCH BLOCK OF THE STORED PROCEDURE