锁升级时的表死锁问题

Question

我有一个表，其中有100到500万条记录作为一个批处理，然后在它们上运行一些存储过程来更新和删除批处理中的记录。所有这些存储过程都使用两个字段来实现选择性，因此它们只在该批处理中的记录上运行。这两个字段都在非聚集索引中。有时，当多个批处理同时运行时，我不断地在批处理之间发生死锁，我假设是由于锁升级。尝试找出是否有一种方法可以在不完全重新设计的情况下解决这个问题，为每个批次使用专用表。禁用页面锁定是否会带来更多麻烦？

其他信息：

表结构和索引的示例(实际的表结构和索引的列比这个多得多)

CREATE TABLE [dbo].[TempImport](
    [UID] [int] IDENTITY(1,1) NOT NULL,
    [EID] [int] NULL,
    [EXTID] [int] NULL,
    [COL1] [varchar](50) NULL,
    [COL2] [varchar](50) NULL,
 CONSTRAINT [PK_TempImport] PRIMARY KEY CLUSTERED 
(
    [UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TempImport_Main] ON [dbo].[TempImport]
(
    [EID] ASC,
    [EXTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

存储过程中的查询类型如下所示：

update TempImport set COL1 = 'foo' where EID = @EID and EXTID = @EXT and COL2='bar'

批处理完成后发生的最后一件事是这样的：

Delete from TempImport where EID = @EID and EXTID = @EXT

死锁通常涉及存储过程中的删除和更新。

如果还有其他有用的信息，请告诉我

Answer 1

只是一些潜在的建议

• 是的，你可以将它们分成(比方说) 2000个批次，以防止行锁升级为表锁，但你会有大量的运行。可能不是一个好的解决方案。
• 您可以修改更新过程(根据Brent Ozar's video re deadlocks I recommended in the comments)，使每个表只更新一次。作为建议，您也可以尝试将它们封装在事务中。这些方法可以消除死锁，但会添加阻塞(其中第二个操作必须等待第一个操作完成)。
• 一个结构化方法是创建一个‘加载’或‘擦除’表，其中包含要操作的ID和相关数据(您可以将其视为队列)。当调用进行更新时，它们只是将请求插入到该队列中。然后，您有一个异步进程(一次只能调用一个)，它从队列中获取所有未完成的数据(并对其进行标记)，执行相关处理，然后从临时表中清除已处理的数据。

请注意，如果您有其他东西访问/使用此表，那么它们也可能在此表上被阻塞或死锁，因此您的方法需要非常小心。