Deadlock - How to fix offending code?

Question

Deadlock - How to fix offending code?

SQLdba-473999

SSCrazy

Points: 2761
More actions
January 21, 2010 at 1:27 pm

#140728

All,
I have a deadlock situation and I need some assistance on what changes would fix the problem. The deadlock trace information is:
This SQL is being executed by 2 spids that end up blocking eachother
DELETE FROM L
WHERE [LID] IN
(SELECT TOP 1000 LID
FROM L JOIN #LPC c ON L.RID=c.RID AND
L.LTypeID=c.LTypeID
WHERE L.[Time] BETWEEN @Start AND @End
AND L.[Time] < DateAdd(day, -c.DTK, @U))
------------------------------------------------------------------------------
Spid 2 is waiting for an eXclusive key lock on index L.IX_Time
(Spid 1 holds a conflicting S lock.)
Spid 1 is waiting for an Update key lock on index L.PK_L
(Spid 2 holds a conflicting Update lock.)
------------------------------------------------------------------------------
Indexes:
[PK_L] PRIMARY KEY NONCLUSTERED
(
[LID] ASC
)
CLUSTERED INDEX [IX_Time] ON [dbo].[L]
(
[Time]
)
My only thought is maybe add a TRANSACTION ISOLATION LEVEL clause??? I don't see any indexes that could be added. I also put the query in DTA and no suggestions were offered.
Anyone have any thoughts here?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do you have blocking or deadlocks? Are you seeing error 1205 for either process?

If deadlocks, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 2

Yes it is a deadlock situation, one of the processes gets a 1205 error and an email is generated.

I set the switch last night. The error log showed a long deadlock-list which is decoded in my post above. I referenced the following article to do so: http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx.

You mentioned a graph, I don't see an actual graph or is the deadlock-list what you are referring to?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ang-473999 (1/21/2010)
I set the switch last night. The error log showed a long deadlock-list which is decoded in my post above.

Post it please (as an attachment if it's big). There's a lot of info in there that's not present in your first post.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 4

In my original post I paraphrased the table/index names, I did not do that here, so it names may look slightly different. See attachment.