I want to have a series of blog posts where I talk about different parts in the deadlock XML report and when they might matter. There is not a lot of documentation about how to deal with deadlock XML. There definitely are a few great tools out there for visualizing and diagnosing deadlocks. Even so, I don’t think there’s any one tool out there right now that handles every single case. I very frequently find myself looking at the XML when I need to diagnose and fix a deadlock.
Today I’ll talk briefly about the transactionname attribute. This is an attribute of the “process” element. Normally this doesn’t matter too much. Some typical descriptors you see here (that actually describe what’s happening):
DROPOBJ
SELECT
UPDATE
COND WITH QUERY
INSERT
Usually the query that follows uses that construct. This is why it doesn’t usually matter very much. You will understand the deadlock better by looking at the execution stack anyway. The execution stack may reveal that you are dealing with a It might be a DROP TABLE statement (transactionname=”DROPOBJ”). It may be a IF EXISTS ( SELECT […] ) construct (transactionname=”COND WITH QUERY”). The important thing is that it is something that accurately describes what the process is actually doing.
I have seen some interesting ones:
SQL Diagnostic Manager Collection Service
MdView
In both cases, these were the transactionname of another vendor’s product. Once I realized this, the rest of the deadlock report was easy to understand. Be aware of the applications that are running on your system. If you are encountering some interesting deadlocks that might not make sense or are hard to diagnose, take a look at the transactionname and see if it makes sense in the context of the deadlock report.
Concurrency Exchange 