Parallel query worker thread was involved in a deadlock

Question

I run an SQL Server Trace to track some deadlocks issues and I was hit on the head with this comment Parallel query worker thread was involved in a deadlock as a reason for the deadlock.

Q1: Does this means that the same query is deadlocking it self? The query execution plan shows some parallelism cases.

Q2: What are the possible ways to "force" SQL Server not to use parallelism or at least to avoid using it as much as possible?

Solution 1

Q1: No. This just means that the deadlock involves an Exchange operator. On the client side you'll get the error "Transaction (Process ID n) was deadlocked on {thread | communication buffer} resources with another process and has been chosen as the deadlock victim."

These kind of deadlock will always include two or more processes and will always include a lock resource.

Here is a repro for this scenario. In most cases, having the correct index will resolve this issue.

When a process deadlock with itself (very rare with latest builds) it's called Intra-Query Parallelism deadlock and you will get an error like "Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused your server command (process ID n) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)." See this link for details.

Q2: Refer to the links Denis provided.

Solution 2

Take a look at Understanding and Using Parallelism in SQL Server

You also want to take a look at using MAXDOP as a query hint

Sometimes all you need is an index, see Fix Execution Plan showing Parallelism