38

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

0

1 Answer 1

33

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:

<waiter-list>
 <waiter event="e_waitPipeGetRow" type="consumer" id="process821d828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8209198" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3827c18" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3809eb8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8226b08" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process9acb6d8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process6188d7828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process381cef8" />
</waiter-list>

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.

3
  • 3
    Since this question is high on the search engine results, it should be noted that there were at least 2 deadlock-related bugs resolved involving columnstore indexes. link, link 2. I had a hard time getting to the issue until I realized it was due to columnstore indexes.
    – Gabe
    Jun 16, 2020 at 17:41
  • Just to add to @Gabe's comment that this issue is still present in some form on the latest CU of SQL Server 2019, even though the links mentioned are for SQL Server 2017 or older.
    – gonsalu
    Feb 21 at 15:41
  • In my case it looks like it is a resource problem caused by another query having too much parallelity not a deadlock within the victim. And I think MS does not do us a favor calling this "deadlock" at all.
    – eckes
    Sep 20 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy