What does "lock | communication buffer resources" mean?

Question

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.

Semasiology

Have you looked here? #13839588 – Swint

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? – Nanette

@BillHurt Interesting! I had not seen that. – Semasiology

@MartinSmith OK, yeah it looks like it's definitely to do with parallel plans. I have actually moved on now so I can't check the deadlock graph. I guess I'll never know for sure. – Semasiology

in which sql server version is this issue.can you paste your sql code here – Dugong

@vimalvasudevan SQL Server 2008, per the tag. I don't have the SQL code that caused the issue, I was trying to reproduce it from an error log. – Semasiology

@blorgbeard then copy paste the error code – Dugong

@vimalvasudevan, the first quote block in my question is the error code. – Semasiology

locks happen when there is concurrent event.. which is a normal behavior, just then have to wait until the other session releases it.. deadlock happens when no session releases the lock and gives way... normally your update script should just be in a particular order to avoid this and just encounter locks – Jeane

DEADLOCK: typical example would be .. user1 to update data in table 1 and update data in table 2 then user2 to update data in table2, after 10 seconds update data in table1.. deadlock victim will be user1...... solution is to have the same update sequence from your application... window1 update should be the same as window update script – Jeane

@RhianA Thank you, but please read the bold text in the question. Here it is again: I am not asking what a deadlock is. – Semasiology

Hi @Semasiology , buffer resource is the same as the buffer cache meaning all the rows you access and prior to committing update are held locked in the buffer cache. When the t-log commits your session will then release the lock. And this is not a bad feature. – Jeane

See also this very similar question on DBA Stackexchange: dba.stackexchange.com/questions/49538/… – Twister

Answer 1

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.

Popup

Answer 2

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.

Backlash

I disagree, this is a general good advise for deadlocks, but not for this particular error which is more a misnomer than a deadlock. In this specific case it is better to look at other queries with prohibitively high parallelism resulting in memory consumption. And maxdop is a quick and ugly (not permanent) fix. – Dunkin

Answer 3

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 :-)

Lupita

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? – Nanette

Simple means you cannot perform update on the particular row because a session is using it. – Jeane

It might not help to set maxdop on the particular query, in my experience this happens even for single-executor queries if they happen to conflict with other queries with high parallelity. So you would need to set the hint on the other query or the whole database. – Dunkin

Answer 4

Just needs to update here for others benefit, for me it was a weird error message, spent lot of time working around the error message. Finally got a clue on the net that I should use query option MAXDOP 1 to avoid parallelism. And when I opted for it, error message was totally different. Actually it was string truncation being caused by the insert statement in a varchar column, adjusted the length of the string to be ingested and everything was back to normal.

Hope it helps someone.

Verge