Can someone please help me to read/understand this deadlock graph?
I don't understand why process 75 is requesting a lock on an object that he has a lock on already?
Asked
Active
Viewed 7,618 times
7
Martin Smith
- 438,706
- 87
- 741
- 845
Jacques
- 747
- 1
- 8
- 19
-
can you send "show innoDB status" response it will be more informative. It should contain last deadlock – varela Sep 01 '11 at 08:50
-
@varela - This is SQL Server. jaques What version? Looks like a paralellism issue. – Martin Smith Sep 01 '11 at 08:51
-
@Martin Smith. SQL Server 2008 – Jacques Sep 01 '11 at 08:56
-
@jaques - If you post your relevant table structures, including indexes, and the XML version of the deadlock graph into your question you may well be able to get a more specific answer. – Martin Smith Sep 01 '11 at 08:59
-
@Martin. This is a commercial product and I don't feel comfortable exposing the internals – Jacques Sep 01 '11 at 09:38
1 Answers
11
According to a blog article that I've found the existance of an "Exchange Event" indicates that the source of your problem may be parallelism in your query.
Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"
The above article goes into much more detail, however the punchline is:
Workaround #1: Add an index or improve the query to eliminate the need for parallelism. In most cases, the use of parallelism in a query indicates that you have a very large scan, sort, or join that isn't supported by proper indexes. If you tune the query, you will often find that you end up with a much quicker and more efficient plan that doesn't use parallelism, and therefore isn't subject to this type of problem. Of course, in some queries (DSS/OLAP-type queries, in particular) it may be difficult to eliminate all large scans.
Workaround #2: Force single-threaded execution with an "OPTION (MAXDOP 1)" query hint at the end of the query. If you can't modify the query, you can apply the hint to any query with a plan guide.
You might want to try this to see if there is any improvement.
-
Thanks, I managed to identify and improve a slow running query and no locks since then. Not sure how to prevent this in the future? And why does SQL server try to be smart if there is a possibility that it might itself into a deadlock situation? (E.G Why use parallelism?) – Jacques Sep 07 '11 at 14:31
-
1The referenced link is focused on "pure" exchange event intra-query deadlock where there is just one SPID (thus "intra-query"). The questions's deadlock graph includes an exchange event _plus_ a more traditional deadlock involving 2 queries (another SPID). I have read that isolated deadlocking exchange events can fix themselves (as they may be due to internal bugs) so the focus should be on the other shared objects--page locks in this case. – crokusek Aug 04 '17 at 17:48