SQL Server: deadlocked on lock communication buffer resources What could be possible reason for this deadlock type? (not deadlock in general)


  Lock communication buffer resources


Is this indicated system is low in memory and buffers count ran out of limit?

Detailed Error: 


  Transaction (Process ID 59) was deadlocked on lock communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction
The full message that is commonly seen:


  Transaction (Process ID 53) was deadlocked on lock | communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction.


This lock type is commonly seen with deadlock queries that SQL Server has executed as parallel, sometimes referred to as "intra-query parallel deadlocks". I have seen a few statements that this also points out system resources are low, which I guess could be involved to a small degree. 

A general guideline that I have noticed to determine if it is parallel deadlock is when you pull the XML deadlock graph (which can be done with the system_health session in 2008 and higher) you will notice different process IDs showing the same bit of code within the execution stack.

As well, looking at the resource list of the deadlock graph and noting the type of waiter event. They will most commonly show "e_xxxxxx", or something like this maybe:

&lt;waiter-list&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process821d828" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process8209198" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process3827c18" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process3809eb8" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process8226b08" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process9acb6d8" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process6188d7828" /&gt;
 &lt;waiter event="e_waitPipeGetRow" type="consumer" id="process381cef8" /&gt;
&lt;/waiter-list&gt;


To try and resolve the issue various paths to take are offered online and in books. I generally start by looking at the execution plan of the query/procedure and focus on the areas that are showing parallel execution. Then from there go through trying to tune the query first and then as last resort may start using query hints. 

The most common query hint you will see mentioned to resolve these deadlocks is implementing MAXDOP 1. However, before doing that you might check to see what the server level MAXDOP and Cost Threshold are set to. Cost Threshold is generally set to 5 by default and I like to raise that to 35 or 40 to start out with, if the query in question has a low cost for that section of code it may not need to run in parallel at all. I'm not all that fond of using MAXDOP query hints but that does not mean they do not have their place and purpose. just my opinion.