Recreating
If you have a copy of the StackOverflow2013 database, this query should produce a parallel deadlock.
SELECT c.Id, c.UserId, c.CreationDate, c.PostId, c.Score INTO #ohno FROM dbo.Comments AS c WITH (TABLOCKX);
If you want an easy way to track down deadlocks, I can’t recommend sp_BlitzLock enough.
It doesn’t render the deadlock graph for you, but it does get you the deadlock XML, which you can save as an XDL file.
For viewing them, Sentry One’s Plan Explorer tool is way better than SSMS. It doesn’t just explore plans — it also explores deadlocks.
Graphed Out
The way it’ll look is something like this:
You’ll see the exchange event, and you’ll also see the same query deadlocking itself.
This is an admittedly odd situation, but one I’ve had to troubleshoot a bunch of times.
You might see query error messages something like this:
Msg 1205, Level 13, State 18, Line 3 Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Options For Fixing It
If you start running into these, it can be for a number of reasons, but the root cause is a parallel query. That doesn’t mean you should change MAXDOP to 1, though you should check your parallelism settings to make sure they’re not at the defaults.
You may want to try setting the query you’re having a problem with to DOP 1. Sure, performance might suffer, but at least it won’t error out.
If that’s not possible, you might need to look at other things in the query. For example, you might be missing a helpful index that would make the query fast without needing to go parallel.
Another issue you might spot in query plans is around order preserving operators. I wrote a whole bunch about that with an example here. You might see it around operators like Sorts, Merges, and Stream Aggregates when they’re surrounding parallel exchange operators. In those cases, you might need to hint HASH joins or aggregations.
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.
is is easy to fix like this:
SELECT c.Id, c.UserId, c.CreationDate, c.PostId, c.Score
INTO #ohno
FROM dbo.Comments AS c WITH (tablockx)
option (maxDOP 1)
and
SELECT c.Id, c.UserId, c.CreationDate, c.PostId, c.Score
INTO #ohno
FROM (select top (10000000000) * from dbo.Comments AS d WITH (tablockx)) as c
But I would like to see other ways.
I’ve tried so many other hints, but no joy.
Do you know of any?
if yes, do I need to buy your videos to learn them?