June 14, 2021 at 12:22 pm
Hello SQL experts,
I am facing an issue where a process gets deadlock and the log suggests "communication buffer resources with another process has been chosen as the deadlock victim". This is totally new to me. I have tried gathering information online but couldn't get much. Could anyone please tell me why it happens, how can we investigate it (as the deadlock graph is a very fancy one) and how to resolve it ?
June 14, 2021 at 7:15 pm
https://stackoverflow.com/questions/18996157/what-does-lock-communication-buffer-resources-mean
These might help. A new one to me as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 14, 2021 at 7:44 pm
Did a quick google on this and found this link:
https://stackoverflow.com/questions/18996157/what-does-lock-communication-buffer-resources-mean
From what I am understanding from the above, the communication buffer in a deadlock means that there were multiple parallel processes that resulted in the deadlock. The specific quote from that link - "Communication Buffer resources" are exchangeEvents used for combining results of parallel queries
To resolve it, my first step would be like with any other deadlock that I have - review deadlock graph, replicate on test, review code for potential deadlock issues, resolve deadlock issues. Review deadlock graph is so I know which queries resulted in the deadlock and will help me in determining how to reproduce this on test. Replicating on test allows me to reproduce the problem and build up a test case for it so I know when the problem is resolved. Reviewing the code is about making a list of what locks are need and when so I can map out which use case would result in a deadlock. Resolving deadlock issues might be a complicated or impossible task depending on the scenario, or it may be as easy as adjusting the cost threshold for parallelism. In your case, since the problem appears to be related to parallelism, I'd be checking to see if the problem persists when you remove parallel processing (MAXDDOP 1 query hint). This would be a short-term solution if it fixes it as this may only hide the deadlock or make it less likely to happen. If your query is deadlocking on itself, then this should fix the problem. Now, if your query is deadlocking with some other queries, then you may need to look at other solutions such as pulling the data you are locking into temporary objects (table variables or temp tables) to work off of instead of holding the locks on them.
We can't see your queries, your data or your deadlock graph, so we don't know your specific use case.
NOTE - adjusting cost threshold for parallelism affects the entire instance, so you may introduce new slowness (or improved performance) by changing that value. I'd proceed with caution when working with that setting. MAXDOP 1 query hint may not solve your problem either if parallelism is the right tool for your workload.
But if you can reproduce it on test, it makes it a lot easier to debug and correct. If you cannot reproduce it on test, then things get interesting as it may end up being trial and error.
Just my 2 cents though. I'm sure there are other experts out there who may have better advice!
EDIT - I just wanted to add that adding a query hint is not the "best" solution, but is more of a workaround solution. I recommend the query hint while doing testing to see if it resolves the issue, but I think bumping up cost threshold for parallelism will be a better long term solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply