What does “lock | communication buffer resources” mean?

26
votes

I have an error log which reports a deadlock:

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.

I am trying to reproduce this error, but my standard deadlock SQL code produces a different error:

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

I want to be very clear that I am not asking what a deadlock is. I do understand the basics.

My question is: what is the meaning of lock | communication buffer resources in this context? What are "communication buffer resources"? Does the lock | signify anything?

My best guess is that a communication buffer is used when parallel threads combine their results. Can anyone confirm or deny this?

My ultimate goal is to somehow trigger the first error to occur again.

3
sql-serversql-server-2008deadlockdatabase-deadlocks
Have you looked here? stackoverflow.com/questions/13839088/… – Bill Hurt
AFAIK your "best guess" is accurate and you will see this message from parallel plans. Are you still getting these errors? If so can you retrieve the deadlock graph from the default extended events session? – Martin Smith
@BillHurt Interesting! I had not seen that. – Blorgbeard
@RhianA Thank you, but please read the bold text in the question. Here it is again: I am not asking what a deadlock is. – Blorgbeard
See also this very similar question on DBA Stackexchange: dba.stackexchange.com/questions/49538/… – Jon Schneider

3 Answers

2
votes

I would interpret the message as a deadlock on some combination of Lock resources or Communication Buffer resources. "Lock resources" are ordinary object locks, and "Communication Buffer resources" are exchangeEvents used for combining results of parallel queries. These are described further in https://blogs.msdn.microsoft.com/bartd/2008/09/24/todays-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks/ where the relevant paragraph is:

An "exchangeEvent" resource indicates the presence of parallelism operators in a query plan. The idea is that the work for an operation like a large scan, sort, or join is divided up so that it can be executed on multiple child threads. There are "producer" threads that do the grunt work and feed sets of rows to "consumers". Intra-query parallel requires signaling between these worker threads: the consumers may have to wait on producers to hand them more data, and the producers may have to wait for consumers to finish processing the last batch of data. Parallelism-related waits show up in SQL DMVs as CXPACKET or EXCHANGE wait types (note that the presence of these wait types is normal and simply indicates the presence of parallel query execution -- by themselves, these waits don't indicate that this type or any other type of deadlock is occurring).

The deadlock graph for one of these I've seen included a set of processes with only one SPID and a graph of objectlocks and exchangeEvents. I guess the message "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" appears instead of "Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)" because of the combination of objectlocks and exchangeevents, or else the message has been changed in SQL Server since the article was written.

1
votes

You can use MAXDOP 1 as a query hint - i.e. run that query on one cpu - without affecting the rest of the server.

This will avoid the error for that query - doesn't tell you why it's failing but does provide a work-around if you have to get it working fast :-)

1
votes

Your issue is parallelism related, and the error has "no meaning" as the error message is not reflecting your problem and no do not go and change the maxdope settings. in order to get to the cause of the error you need to use trace flag 1204 , have a look as to how to use the trace flag and what info you get.

When you do this you'd get the answer as to why, where and what line of code caused the lock. I guess you're able to google your self from that point, and if not then post it and you'll get the answer you need.