sql-server - 尝试同时运行 Delete 语句时，KEY Lock 上的 SQL Server 死锁

Question

我真的很感激这方面的帮助。这让我难住了。基本上，我在事务中运行带有一系列 Delete 语句的 Proc。此过程将由多线程应用程序调用，因此调用次数很多。大多数语句都执行得很好，但最后一个语句因 Key lock 问题而陷入僵局。好像跟Primary Key和它上面的聚簇索引PK_Payloads有关。我附上了所有相关信息。感谢您提供的任何帮助。

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])
)

外键上也有非聚集索引，也有几个覆盖非聚集索引。

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]

过程

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

死锁图

<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>

Answer 1

您需要一个关于Payloads(PayloadID) 的索引。没有它，FK 验证必须进行表扫描。这也将帮助所有其他 DELETE，但其他 DELETE 也一样，它们非常低效。其他删除不会死锁，因为它们是表扫描，因此都按相同的顺序进行。最后一个至少可以从 [Id] 上的索引中受益，但这样做会导致与所有这些扫描的顺序冲突。