July 26, 2012 at 4:23 am
Hi all,
I would really appreciate some help with this one. It has got me stumped. Basically Im running a Proc with a series of Delete statements in a transaction. This proc will be called by a multi threaded application so lots of calls. Most of the statements are executing fine, but the final one keeps deadlocking with Key lock problems. It seems to be related to the Primary Key. Ive attached all the relevant info. Thank you for any help provided.
DDL
CREATE TABLE [dbo].[Payloads]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Guid] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
[Name] [nvarchar](max) NOT NULL,
[LastProcessedDate] [datetime] NOT NULL DEFAULT GETDATE(),
[SourceSystem] [nvarchar](32) NOT NULL,
[DestinationSystem] [nvarchar](32) NULL,
[Error] [bit] NOT NULL DEFAULT 0,
[ErrorDetails] [nvarchar](max) NULL,
[CreateDate] [datetime] NOT NULL DEFAULT GETDATE(),
[TypeId] [tinyint] NOT NULL,
[StatusId] [smallint] NOT NULL,
[TagId] [integer] NULL,
[EngineExecutionCrawlLocationId] [bigint] NOT NULL,
[PayloadId] [bigint] NULL,
CONSTRAINT [PK_Payloads] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Payloads_W] FOREIGN KEY([TypeId]) REFERENCES [dbo].[W] ([Id]),
CONSTRAINT [FK_Payloads_X] FOREIGN KEY([StatusId]) REFERENCES [dbo].[X] ([Id]),
CONSTRAINT [FK_Payloads_Y] FOREIGN KEY([TagId]) REFERENCES [dbo].[Y] ([Id]),
CONSTRAINT [FK_Payloads_Z] FOREIGN KEY([EngineExecutionCrawlLocationId]) REFERENCES [dbo].[Z] ([Id]),
CONSTRAINT [FK_Payloads_Payloads] FOREIGN KEY([PayloadId]) REFERENCES [dbo].[Payloads] ([Id])
)
The foreign Keys also have non clustered indexes on them and there are a couple of covering non clustered indexes too.
CREATE NONCLUSTERED INDEX [Payloads_I5] ON [dbo].[Payloads]
(
[Id] ASC
)
INCLUDE ([Name], [StatusId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
Proc
BEGIN TRANSACTION
DELETE FROM [A]
WHERE [PayloadId] = @PayloadId
DELETE FROM
WHERE [PayloadId] = @PayloadId
DELETE FROM [C]
WHERE [PayloadId] = @PayloadId
DELETE FROM [D]
WHERE [PayloadId] = @PayloadId
DELETE FROM [E]
WHERE [PayloadId] = @PayloadId
DELETE FROM [F]
WHERE [PayloadContentId] IN (SELECT [Id]
FROM [G]
WHERE [PayloadId] = @PayloadId)
DELETE FROM [G]
WHERE [PayloadId] = @PayloadId
/* Offending statement Here */
DELETE FROM [Payloads]
WHERE [Id] = @PayloadId
COMMIT TRANSACTION
Deadlock Graph
<deadlock victim="process940c6088">
<process-list>
<process id="process940c6088" taskpriority="0" logused="3080" waitresource="KEY: 31:72057594041139200 (5cd3004a5da8)" waittime="2431" ownerId="85480" transactionname="user_transaction" lasttranstarted="2012-07-26T11:24:03.970" XDES="0x98861950" lockMode="S" schedulerid="4" kpid="5432" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-26T11:24:03.970" lastbatchcompleted="2012-07-26T11:24:03.923" clientapp=".Net SqlClient Data Provider" hostname="CRUSADER" hostpid="2792" loginname="AIL\matt" isolationlevel="read committed (2)" xactid="85480" currentdb="31" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000">
DELETE FROM [Payloads]
WHERE [Id] = @PayloadId </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 31 Object Id = 690101499] </inputbuf>
</process>
<process id="process4dd048" taskpriority="0" logused="3732" waitresource="KEY: 31:72057594041139200 (a903f5656cf9)" waittime="2413" ownerId="85496" transactionname="user_transaction" lasttranstarted="2012-07-26T11:24:03.987" XDES="0x9724e3b0" lockMode="S" schedulerid="4" kpid="2560" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-26T11:24:03.987" lastbatchcompleted="2012-07-26T11:24:03.940" clientapp=".Net SqlClient Data Provider" hostname="CRUSADER" hostpid="2792" loginname="AIL\matt" isolationlevel="read committed (2)" xactid="85496" currentdb="31" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000">
DELETE FROM [Payloads]
WHERE [Id] = @PayloadId </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 31 Object Id = 690101499] </inputbuf>
</process>
<process id="process4c3b88" taskpriority="0" logused="3732" waitresource="KEY: 31:72057594041139200 (b6d1e11077fc)" waittime="2288" ownerId="85471" transactionname="user_transaction" lasttranstarted="2012-07-26T11:24:03.930" XDES="0x83925950" lockMode="S" schedulerid="3" kpid="5608" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-26T11:24:03.927" lastbatchcompleted="2012-07-26T11:24:03.927" clientapp=".Net SqlClient Data Provider" hostname="CRUSADER" hostpid="2792" loginname="AIL\matt" isolationlevel="read committed (2)" xactid="85471" currentdb="31" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000">
DELETE FROM [Payloads]
WHERE [Id] = @PayloadId </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 31 Object Id = 690101499] </inputbuf>
</process>
<process id="process4dd288" taskpriority="0" logused="3732" waitresource="KEY: 31:72057594041139200 (a903f5656cf9)" waittime="2427" ownerId="85487" transactionname="user_transaction" lasttranstarted="2012-07-26T11:24:03.973" XDES="0x800bf950" lockMode="S" schedulerid="4" kpid="2900" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-26T11:24:03.973" lastbatchcompleted="2012-07-26T11:24:03.933" clientapp=".Net SqlClient Data Provider" hostname="CRUSADER" hostpid="2792" loginname="AIL\matt" isolationlevel="read committed (2)" xactid="85487" currentdb="31" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000">
DELETE FROM [Payloads]
WHERE [Id] = @PayloadId </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 31 Object Id = 690101499] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock97039400" mode="X" associatedObjectId="72057594041139200">
<owner-list>
<owner id="process4c3b88" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process940c6088" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589900" mode="X" associatedObjectId="72057594041139200">
<owner-list/>
<waiter-list>
<waiter id="process4dd048" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589000" mode="X" associatedObjectId="72057594041139200">
<owner-list>
<owner id="process4dd048" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process4c3b88" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589900" mode="X" associatedObjectId="72057594041139200">
<owner-list>
<owner id="process940c6088" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process4dd288" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
July 26, 2012 at 8:18 am
To assist anyone else Ive found the cause.
I needed to Add a Non Clustered Index to Payloads(PayloadId) - Kindof a parent child relationship.
Looking at the Execution plan for the DELETE FROM Payloads I can now see it doing an Index Seek to check referential integrity instead of the Index Scan
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply