Money In The Bank
Capturing deadlock graphs has come a long way since… 2008? You used to need clunky trace flags, that for some reason would dump information line by line into the error log.
Nowadays, you can get a lot of great information from the system health extended event session, or use Extended Events (to wit, probably the most value I’ve gotten from Extended Events).
But no matter what you use to collect or display them, deadlock graphs can return some confusing information.
SELECTS!
Sure, you can have selects use lock hints that’ll make them more prone to deadlocks: serializable, repeatable read, XLOCK hints and all that. But that’s not really the point. The point is that it’s usually not just a select happening in a transaction that’s taking locks.
With a stored procedure like this, the deadlock graph can be weird looking.
Even though it’s the updates holding locks, the select queries show up as owners.
And that brings me to the next oddity!
Cut Off Text!
This is also an “issue” with the plan cache. I get it — logging the gazillion lines of text you monsters cram into a single statement would be difficult — but most of the time i’d rather have everything from the FROM on than just a bunch of selected columns.
Sometimes, though, even short text gets cut off.
Where id = what?
Why is the o in “cornholio” gone?
I DON’T GET IT. WHERE DID IT GO.
Unresolved Procedures
You know, with this information, you can, like… Nevermind.
As if figuring out deadlocks isn’t hard enough, we now have the additional pleasure of digging this out ourselves.
Why? I don’t know.
Monkey In The Tank
None of this stuff is fun to deal with, even for people experienced with troubleshooting deadlocks.
If you’re just starting out, it can make figuring the situation out seem impossible.
Don’t worry though, I’m sure there are robots coming.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.