January 13, 2010 at 7:48 pm
Deadlock encountered .... Printing deadlock information
2010-01-08 15:21:14.26 spid1
2010-01-08 15:21:14.26 spid1 Wait-for graph
2010-01-08 15:21:14.26 spid1
2010-01-08 15:21:14.26 spid1 Node:1
2010-01-08 15:21:14.26 spid1 KEY: 7:1316251794:2 (ae00082d0590) CleanCnt:2 Mode: X Flags: 0x0
2010-01-08 15:21:14.26 spid1 Grant List 0::
2010-01-08 15:21:14.26 spid1 Owner:0x7f66a880 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:146 ECID:0
2010-01-08 15:21:14.26 spid1 SPID: 146 ECID: 0 Statement Type: INSERT Line #: 65
2010-01-08 15:21:14.26 spid1 Input Buf: RPC Event: SubmissionServiceRequestStatusSave;1
2010-01-08 15:21:14.26 spid1 Requested By:
2010-01-08 15:21:14.26 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:98 ECID:0 Ec:(0x6651B5C8) Value:0x57c29ea0 Cost:(0/B30)
2010-01-08 15:21:14.26 spid1
2010-01-08 15:21:14.26 spid1 Node:2
2010-01-08 15:21:14.26 spid1 KEY: 7:50099219:1 (8f0094a3f489) CleanCnt:2 Mode: X Flags: 0x0 2010-01-08 15:21:14.26 spid1 Grant List 0::
2010-01-08 15:21:14.26 spid1 Owner:0x6c1c4fc0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98 ECID:0
2010-01-08 15:21:14.26 spid1 SPID: 98 ECID: 0 Statement Type: CONDITIONAL Line #: 466
2010-01-08 15:21:14.26 spid1 Input Buf: RPC Event: LoanStatusSaveWholesale;1
2010-01-08 15:21:14.26 spid1 Requested By:
2010-01-08 15:21:14.26 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:146 ECID:0 Ec:(0x63B8D5C8) Value:0x2a182d20 Cost:(0/11C)
2010-01-08 15:21:14.26 spid1 Victim Resource Owner:
2010-01-08 15:21:14.26 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:146 ECID:0 Ec:(0x63B8D5C8) Value:0x2a182d20 Cost:(0/11C)
I got the statements that were deadlocking. We use no explicit transactions. Both the statements below were inside stored procedures but were not part of any other statements that were referencing them. I tried to do some analysis:
1. conditional(Select)#466 owns PK_application in X mode
2. insert #65 owns NCindex AppID in X mode
3. conditional(Select)#466 wants NCindex AppID in S mode
4. insert #65 wants PK_Appln in S mode
What I am puzzled by is why the Conditional #466 needs to take an X lock on PK_application?
________________
Conditional #466
________________
IF EXISTS ( SELECT ServiceRequestStatusLog.[ID] FROM ServiceRequestStatusLog
WHERE ApplicationID = @ApplicationID
AND ServiceOrderType='Flood Request')
SET @intFloodCertificateIndicator = 1
__________
Insert #65
__________
INSERT INTO ServiceRequestStatusLog
(
ApplicationID,
RequestQueueGUID
)
VALUES
(
@LoanApplicationID,
@RequestGUID
)
Application Table DDL
_____________________
CREATE TABLE [dbo].[Application](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ResourceID] [int] NOT NULL CONSTRAINT [DF_Application_ResourceID] DEFAULT (0),
[LeadID] [int] NULL CONSTRAINT [DF_Application_LeadID] DEFAULT (0),
[LoanPurposeTypeID] [int] NOT NULL CONSTRAINT [DF_Application_LoanPurposeTypeID] DEFAULT (0),
[PropertyUsageTypeID] [int] NOT NULL CONSTRAINT [DF_Application_PropertyUsageTypeID] DEFAULT (0),
CONSTRAINT [PK_Application] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ServiceRequestStatusLog DDL
___________________________
CREATE TABLE [dbo].[ServiceRequestStatusLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationID] [int] NOT NULL,
[RequestQueueGUID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ServiceOrderType] [varchar](50) NOT NULL,
CONSTRAINT [PK_ServiceRequestStatusLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ServiceRequestStatusLog] WITH CHECK ADD CONSTRAINT [FK_ServiceRequestStatusLog_Application] FOREIGN KEY([ApplicationID])
REFERENCES [dbo].[Application] ([ID])
ON DELETE CASCADE
GO
CREATE NONCLUSTERED INDEX [IX_ServiceRequestStatusLog_ApplicationID] ON [dbo].[ServiceRequestStatusLog]
(
[ApplicationID] ASC
) ON [PRIMARY]
January 14, 2010 at 3:46 am
Since you're using SQL 2005, can you rather enable traceflag 1222 instead of 1204
DBCC TRACEON(1222,-1)
Is the deadlock reproducible?
Is the conditional part of a transaction that earlier did something on the Application table? Can you post the full code of the two procs SubmissionServiceRequestStatusSave and LoanStatusSaveWholesale?
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply