37

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • Have you looked here? http://stackoverflow.com/questions/13839088/parallel-query-worker-thread-was-involved-in-a-deadlock – Bill Hurt Dec 07 '13 at 19:40
  • 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 Dec 10 '13 at 02:45
  • @BillHurt Interesting! I had not seen that. – Blorgbeard Dec 10 '13 at 03:30
  • @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. – Blorgbeard Dec 10 '13 at 03:32
  • in which sql server version is this issue.can you paste your sql code here – vimal vasudevan Dec 10 '13 at 11:10
  • @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. – Blorgbeard Dec 10 '13 at 17:40
  • @blorgbeard then copy paste the error code – vimal vasudevan Dec 12 '13 at 04:17
  • @vimalvasudevan, the first quote block in my question is the error code. – Blorgbeard Dec 12 '13 at 04:47
  • 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 – RoMEoMusTDiE Jan 27 '16 at 02:03
  • 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 – RoMEoMusTDiE Jan 27 '16 at 02:14
  • 3
    @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 Jan 27 '16 at 03:20
  • Hi @Blorgbeard , 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. – RoMEoMusTDiE Jan 27 '16 at 03:55
  • 1
    See also this very similar question on DBA Stackexchange: https://dba.stackexchange.com/questions/49538/sql-server-deadlocked-on-lock-communication-buffer-resources/72170 – Jon Schneider Apr 24 '18 at 15:17

4 Answers4

5

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.

Rattle
  • 2,393
  • 2
  • 20
  • 25
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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
Walter Verhoeven
  • 213
  • 1
  • 6
  • 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. – eckes Sep 20 '22 at 18:29
1

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

user240084
  • 59
  • 2
  • 2
    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? – Martin Smith Apr 08 '15 at 20:24
  • 2
    Simple means you cannot perform update on the particular row because a session is using it. – RoMEoMusTDiE Jan 27 '16 at 06:44
  • 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. – eckes Sep 20 '22 at 18:30
0

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.

user10669460
  • 21
  • 1