June 3, 2015 at 1:22 pm
Hi there - I have extracted the attached deadlock graph but its much larger than the usual deadlock grapghs.
Can someone have a look at this and let me know whats going on here?
I have renamed the .xdl file to .txt so I can upload it to the site. Please rename the extention to .xdl to open.
Thanks!!!
--------------------------------------------
Laughing in the face of contention...
June 3, 2015 at 2:28 pm
In this case, you have two instances of an UPDATE query deadlocking with each other.
Essentially, each process holds U locks (used to search for rows to modify, ironically precisely to avoid a particular sort of conversion deadlock) on a set of pages that includes pages for which threads from the other process are requesting U locks.
Without knowing more about the exact situation, there are a couple things you could do to mitigate this.
The first would be to explore whether it's actually beneficial for the query to run in parallel. If the query can run serially without a large increase in duration (and sometimes SQL Server gets things wrong, and the serial plan is actually faster anyway), then that could help, since each process would be taking out fewer locks at the same time.
Another option to explore is to see if you could add indexing to support the WHERE clause used (if said indexing is not already there). Adding an index that allowed the process to seek for rows to update might go a long way to mitigating this, since it could reduce the number of U locks each UPDATE query has to take out while searching for rows to modify.
Even if it couldn't be used for a singleton seek, it could at least get the U locks to be taken out in the same order by each process, which would help turn any conflicts into hopefully-short-lived blocking, not deadlocking.
A more general note that is relevant to the indexing possibility is that while there is much to be said about things like access order with deadlocks, general performance tuning goes a long way. It's much harder for processes to deadlock if they take a few milliseconds to run than if they take seconds to run 🙂
Of course, as with all such things, the costs have to be carefully weighed, either with adding a supporting index on the EQDETID column, or with changing the parallelism of the query, and those changes should be thoroughly tested.
Would it be possible for you to pull the actual execution plan for that UPDATE query and post it here, perhaps along with the DDL for the table and its current indexes? Seeing those would help make more pointed suggestions.
Assuming the procedure hasn't changed and is still in cache, you could grab the query plan by running this:
SELECT qp.query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE sql_handle=0x03003c00ea5ac967cbe8780092a200000100000000000000
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply