Menu
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.
974
“Transaction was deadlocked” error occurs when two or more sessions are waiting to get a lock on a resource which has already locked by another session in the same blocking chain. As a result, none of the sessions can be completed and SQL Server has to intervene to solve this problem.
Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.
SQL Server deadlock is essentially a standoff between two processes that are competing for exclusive access to the same resource. Because only one process can use a resource at a time, performance slows until the deadlock is resolved.
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.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
