January 5, 2010 at 3:01 pm
I have some deadlock issues. This particular one has been occuring frequently on the same two Procedures. Any ideas?
2010-01-05 10:12:58.41 spid2 Wait-for graph
2010-01-05 10:12:58.41 spid2
2010-01-05 10:12:58.41 spid2 Node:1
2010-01-05 10:12:58.41 spid2 KEY: 9:50099219:1 (6900bc599226) CleanCnt:2 Mode: U Flags: 0x0
2010-01-05 10:12:58.41 spid2 Grant List 6::
2010-01-05 10:12:58.41 spid2 Owner:0x61043c8 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:64 ECID:0
2010-01-05 10:12:58.41 spid2 SPID: 64 ECID: 0 Statement Type: SELECT Line #: 77
2010-01-05 10:12:58.41 spid2 Input Buf: RPC Event: LoadTargetObject;1
2010-01-05 10:12:58.41 spid2 Grant List 13::
2010-01-05 10:12:58.41 spid2 Requested By:
2010-01-05 10:12:58.41 spid2 ResType:LockOwner Stype:'OR' Mode: X SPID:140 ECID:0 Ec:(0x000000112618B658) Va
2010-01-05 10:12:58.41 spid2
2010-01-05 10:12:58.41 spid2 Node:2
2010-01-05 10:12:58.41 spid2 KEY: 9:1659205011:1 (d0003dabd1f3) CleanCnt:2 Mode: X Flags: 0x0
2010-01-05 10:12:58.41 spid2 Grant List 13::
2010-01-05 10:12:58.41 spid2 Owner:0x59e74a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:140 ECID:0
2010-01-05 10:12:58.41 spid2 SPID: 140 ECID: 0 Statement Type: UPDATE Line #: 52
2010-01-05 10:12:58.41 spid2 Input Buf: RPC Event: CustomerStatusIndicator;1
2010-01-05 10:12:58.41 spid2 Requested By:
2010-01-05 10:12:58.41 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:64 ECID:0 Ec:(0x00000014C0BDA458) Val
2010-01-05 10:12:58.41 spid2 Victim Resource Owner:
2010-01-05 10:12:58.41 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:64 ECID:0 Ec:(0x00000014C0BDA458) Val
I tracked down the code responsible for the deadlock. Statistics Profile shows that their execution plans are both
normal/fast: No index/table scans; only bookmark lookups, nested joins, index seeks and clustered index seeks.
The resources held are: SPID 64 holds A.ID and SPID 140 holds C.ID
--SPID: 64
select {bunch of columns about 50} from [A]
inner join on B.ID = A.BID
inner join [C] on C.ID = A.CID
Left outer join [D] on D.AID = A.ID
where A.ID = 123
--SPID:140
Update A
Set A.STID = PR.STID
from PR
INNER join A on PR.CID = A.CID
where A.ID = 234
A.ID, B.ID, C.ID are are clustered indexes. All remaining mentioned columns have non-clustered indexes on them.
A column A.CID means CID is a foreign key in table A and it parent key is c.ID
January 5, 2010 at 3:15 pm
Can you post the table definitions and the actual definitions of all of the indexes?
Both that select and the update are part of larger procedures. Can you post the definitions of LoadTargetObject and CustomerStatusIndicator? Are there explicit transactions in either?
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
January 5, 2010 at 3:47 pm
They are all tables with around 30-50 columns. I just tried the create to copy the script but its big.
ALTER TABLE [dbo].[A] ADD CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
The indexes are pretty normal. Each table has a primary clustered key and around 10 non-clustered indexes.
LoadTargetObject was the parent stored procedure. I found the deadlocking code about 3 nested SP's inside. Considering each stored procedure is about 500-700 lines, I did not post it. CustomerStatusIndicator is consists only of the code that I used above but it also nested inside a much larger stored procedure.
There are no explicit transactions. I checked.
[EDIT]
really appreciate the help!
January 6, 2010 at 1:31 am
badkow (1/5/2010)
The indexes are pretty normal.
I'm sure they are, but saying that the indexes are normal does not in any way help me to tell is they are useful indexes, if they are covering, if they need adjusting, etc. Sorry, but I can't read your mind.
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
January 6, 2010 at 9:34 am
Gila, I attached the table and index definitions. Do you need anything else?
The deadlocking statements are:
==[shared Application.PK]==[needs Shared Resource.PK]==
-- SPID:64; OriginalSP: ApplicationApplicationGetObject CurrentSP: ProcApplicationApplicationGet Line# 77 SELECT
SELECT
@ResourceTypeID = Resource.ResourceTypeID,
@ApplicationTypeID = ApplicationTypeID,
@SubmissionTypeID = SubmissionTypeID
FROM
Application
INNER JOIN PreApplication ON PreApplication.[ID] = Application.PreApplicationID
INNER JOIN Resource ON Resource.[ID] = Application.ResourceID
LEFT OUTER JOIN Product ON Product.ApplicationID = Application.[ID]
WHERE
Application.[ID] = @ApplicationID
==[Xclusive Resource.PK]==[needs Xclusive Application.PK]==
-- SPID:140; OriginalSP: LoanStatusSaveWholeSale CurrentSP: ProcApplicationResourceStatusUpdate Line# 52 UPDATE
UPDATE
Application
SET
Application.SubmissionTypeID = PipelineRecord.SubmissionTypeID
FROM
PipelineRecord
INNER JOIN Application ON PipelineRecord.ResourceID = Application.ResourceID
WHERE
Application.[ID] = @ApplicationID
Thanks for Looking!
January 6, 2010 at 9:53 pm
I think I found the solution. Its because of an Update lock that allowed a shared lock to lock a resource that it needed to upgrade to an Exclusive.
I will post in more detail once I run some tests and confirm this.
January 7, 2010 at 1:15 am
Why did you edit and remove all the useful information. I was going to set aside an hour or so tomorrow to look at this. Do you want another opinion or not?
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
January 8, 2010 at 2:19 pm
Just thought no one was interested. :ermm:
Put them back on. 🙂
And ofcourse, I would definitely like an opinion from the Gilamonster
January 9, 2010 at 3:07 am
badkow (1/8/2010)
Just thought no one was interested. :ermm:
If I wasn't interested, I wouldn't have asked for them in the first place.
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
January 16, 2010 at 3:45 am
I'm trying to have a look at this, but without the definitions of the two procs (which I can no longer access from the PM that you sent, I assume you've deleted the attachments), I can't work out what happened leading up to the deadlock, I can't even tell what the two statements that directly cause the deadlock were and I can't give you any useful advice on resolving this permanently.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply