Deadlock anatomy – transactionname

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s