November 24, 2011 at 2:49 pm
Hello,
I am looking at some incredibly straight forward code which is causing some deadlocks and I am a little unsure as to the best way to manage the blocking on it.
DELETE aa
FROM ra
INNER JOIN aa ON aa.ID = ra.ID
WHERE ra.ID = @ID
AND ra.Date = CONVERT(DATE, @Date)
DELETE
FROM ra
WHERE ra.ID = @ID
AND Date = CONVERT(DATE, @Date)
The above code is the part of the procedure that is causing the locks - where the following type of contention is encountered:
key lock on table aa ncidx_1, owned by spid b (lock type = X), waiting by spid a (lock type = U)
key lock on table ra cidx_1, owned by spid a (lock type = S), waiting by spid b (lock type = X)
The above code has a transaction begin just above what you see now; and then finishes later on after some other actions are completed.
I was thinking of chopping up the transactions so that they are smaller - but logically thinking about what is happening the 2 delete statements need to both go together - so that if one fails they are both rolled back. So I wasnt sure that this would actually achieve anything.
Indexing wise - there are index seeks happening for both chunks - but wonder if there is anything else I should do.
What are peoples thoughts on how to get around this? I would have thought this would be super basic but I keep thinking about it and start doing one thing but then questioning whether it would actually work - thus going round in circles and never commiting to anything....which is where you wonderful people come in.
Any ideas/comments/hints/thoughts - much appreciated.
Thanks in advance.
Troy
November 24, 2011 at 4:56 pm
Lock type U? That means there's an update involved in this (or a select with UPDLOCK hint)
Can you post the deadlock graph?
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2011 at 6:57 pm
Hello - thanks for the response. Yes it certainly is a U lock type - I will see if I can capture it...the deadlock isn't happening all the time, so it might be a hit/miss opportunity 🙂
November 25, 2011 at 12:08 am
That's fine. Turn the tracelfag on (either with TRACEON or a startup option) and leave it. When the deadlock reoccurs, the deadlock graph will be automatically written into the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 6:31 pm
Oh well - this one has not raised it's ugly head since....and the other deadlocks I have managed to work out the resolution....so all's well that ends well I suppose...fingers crossed.
Thanks anyhow, but figured I had better just update this thread to effectivly close it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply