Deadlock - What to do next?
-
Hi,
I have a job that keeps getting a deadlock victim error and fails.
I created a trace in profiler that captured the deadlock. I'll attach a pic of the graph.
It looks to me that it is having an issue with the clustered index on the claim table (which is huge). How do I best proceed? Should I try removing the clustered on the pkey? I have a dev server I can use.
Thanks in advance - I'm stumped.
-
To me, I would start by looking at the process that didn't get killed. whatever process 143 was doing, it needed an exclusive lock on pec_prod.dbo.claim and an intent exclusive lock on an object (not sure what based on the image).
Since it needed an exclusive lock, my GUESS is that it is doing some data manipulation of some sort (INSERT, UPDATE, DELETE). Your query was looking for a SHARED lock which is not compatible with the exclusive lock at your current isolation level. I expect your query was doing a SELECT, which would be MUCH easier to roll back than an INSERT, UPDATE, or DELETE operation, so your query was the victim.
Now to correct and prevent the deadlock, the best option (my opinion) is to reduce how long you need your shared lock for. What I mean is if you are taking out a shared lock on the table for 1 minute, other queries that need to change data will need to wait for your query to finish before they can run. If they are waiting for a whole minute, you are going to have unhappy end users and increase the chance of a deadlock. But if you can get your query to complete in under a second (for example), it is a MUCH smaller waiting window for end users and a much lower chance of a deadlock happening.
Now how you improve the performance of the query depends GREATLY on how the table is currently built and defined. Dropping the clustered index LIKELY won't help prevent the deadlock and may actually make it more common as your queries will likely hold longer shared locks. A better approach would be to see what you can do about filtering the data or tuning the query. This MAY mean tuning your query or it MAY mean tuning the other query. Or it may mean tuning both.
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. -
Thank you SO much Brian. Really appreciate your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply