Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Sep 9 '13 at 16:23

This question came from our site for professional and enthusiast programmers.

1  
Have you try MAXDOP 1 in the queries, as suggested in this answer? –  criticalfix Sep 9 '13 at 15:25
    
Is the error message "lock | communication buffer resources" or "lock communication buffer resources"? I was wondering what on earth a lock communication buffer was. –  Martin Smith Sep 9 '13 at 15:38
    
@ criticalfix, I'm using WCF-SQL adapter to send data to SQL Server, so don't have an option to modify SQL Query –  usman shaheen Sep 9 '13 at 15:47

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.