title: SQL Server Deadlock on KEY Lock when attempting to run Delete statements concurrently
body: <p>I would really appreciate some help with this one. It has 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 and the clustered index on it PK_Payloads. Ive attached all the relevant info. Thank you for any help provided.</p>
  
  <p><strong>DDL</strong></p>
  
  <pre><code>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])
  )
  </code></pre>
  
  <p>The foreign Keys also have non clustered indexes on them and there are a couple of covering non clustered indexes too.</p>
  
  <pre><code>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]
  </code></pre>
  
  <p><strong>Proc</strong></p>
  
  <pre><code>DECLARE @Details NVARCHAR(MAX)
  
  SELECT @Details = 'Name: ' + p.[Name] + ',  Source Ref: ' + pc.[SourceContentReference] 
     + ',  Quarantine Ref: ' + ISNULL(pc.[DestinationContentReference], '')
     + ',  Archive Ref: ' + ISNULL(pc.[ArchiveContentReference], '')
  FROM Payloads p
  INNER JOIN PayloadContent pc ON p.[Id] = pc.[PayloadId]
  WHERE p.[Id] = @PayloadId
  
  BEGIN TRANSACTION
  
  -- Audit Payload Deleted
  INSERT INTO [Audit] ([AuditTypeId], [ObjectId], [Details])
  VALUES (1, @PayloadId, ISNULL(@Details, ''))
  
  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
  </code></pre>
  
  <p><strong>Deadlock Graph</strong></p>
  
  <pre><code>&lt;deadlock victim="process940c6088"&gt;
  &lt;process-list&gt;
  &lt;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"&gt;
  &lt;executionStack&gt;
  &lt;frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000"&gt;
  DELETE FROM [Payloads]
  WHERE [Id] = @PayloadId &lt;/frame&gt;
  &lt;/executionStack&gt;
  &lt;inputbuf&gt;
  Proc [Database Id = 31 Object Id = 690101499] &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;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"&gt;
  &lt;executionStack&gt;
  &lt;frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000"&gt;
  DELETE FROM [Payloads]
  WHERE [Id] = @PayloadId &lt;/frame&gt;
  &lt;/executionStack&gt;
  &lt;inputbuf&gt;
  Proc [Database Id = 31 Object Id = 690101499] &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;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"&gt;
  &lt;executionStack&gt;
  &lt;frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000"&gt;
  DELETE FROM [Payloads]
  WHERE [Id] = @PayloadId &lt;/frame&gt;
  &lt;/executionStack&gt;
  &lt;inputbuf&gt;
  Proc [Database Id = 31 Object Id = 690101499] &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;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"&gt;
  &lt;executionStack&gt;
  &lt;frame procname="AI.DataPoint.Database.dbo.DeletePayload" line="47" stmtstart="2474" stmtend="2580" sqlhandle="0x03001f00fb1c2229b2c7b7009aa000000100000000000000"&gt;
  DELETE FROM [Payloads]
  WHERE [Id] = @PayloadId &lt;/frame&gt;
  &lt;/executionStack&gt;
  &lt;inputbuf&gt;
  Proc [Database Id = 31 Object Id = 690101499] &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;/process-list&gt;
  &lt;resource-list&gt;
  &lt;keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock97039400" mode="X" associatedObjectId="72057594041139200"&gt;
  &lt;owner-list&gt;
  &lt;owner id="process4c3b88" mode="X"/&gt;
  &lt;/owner-list&gt;
  &lt;waiter-list&gt;
  &lt;waiter id="process940c6088" mode="S" requestType="wait"/&gt;
  &lt;/waiter-list&gt;
  &lt;/keylock&gt;
  &lt;keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589900" mode="X" associatedObjectId="72057594041139200"&gt;
  &lt;owner-list/&gt;
  &lt;waiter-list&gt;
  &lt;waiter id="process4dd048" mode="S" requestType="wait"/&gt;
  &lt;/waiter-list&gt;
  &lt;/keylock&gt;
  &lt;keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589000" mode="X" associatedObjectId="72057594041139200"&gt;
  &lt;owner-list&gt;
  &lt;owner id="process4dd048" mode="X"/&gt;
  &lt;/owner-list&gt;
  &lt;waiter-list&gt;
  &lt;waiter id="process4c3b88" mode="S" requestType="wait"/&gt;
  &lt;/waiter-list&gt;
  &lt;/keylock&gt;
  &lt;keylock hobtid="72057594041139200" dbid="31" objectname="AI.DataPoint.Database.dbo.Payloads" indexname="PK_Payloads" id="lock8a589900" mode="X" associatedObjectId="72057594041139200"&gt;
  &lt;owner-list&gt;
  &lt;owner id="process940c6088" mode="X"/&gt;
  &lt;/owner-list&gt;
  &lt;waiter-list&gt;
  &lt;waiter id="process4dd288" mode="S" requestType="wait"/&gt;
  &lt;/waiter-list&gt;
  &lt;/keylock&gt;
  &lt;/resource-list&gt;
  &lt;/deadlock&gt;
  </code></pre>