sql-server - SQL Server 锁的问题

Question

我遇到了一个问题，即 SQL Server 在我们的主表上造成大量锁定(95 到 150 个)。它们通常是持续时间较短的锁，持续时间不到 3 秒，但如果可能的话，我想消除这些锁。我们还注意到，通常没有 block ，但有时我们会遇到 block 似乎“级联”的情况，然后整个系统的速度显着减慢。

背景

我们有多达 600 个虚拟机处理数据，并且我们在 SQL 中加载了一个表，以便我们可以监控任何停滞的记录和标记为完成的记录。在处理过程中，该表中通常有 200,000 到 1,000,000 条记录。

我们正在努力实现的目标

我们正在尝试获取下一条可用记录(状态 = 0)。然而，由于存储过程上可能同时存在多个命中，因此我们试图确保每个虚拟机都获得唯一的记录。这很重要，因为每个记录的处理时间为 1.5 到 2.5 分钟，我们希望使其尽可能干净。

到目前为止我们的思考过程

UPDATE TOP (1) dbo.Test WITH (ROWLOCK)
SET Status = 1, 
    VMID = @VMID, 
    ReadCount = ReadCount + 1,
    ProcessDT = GETUTCDATE()
OUTPUT INSERTED.RowID INTO @retValue
WHERE Status = 0

此更新给我们带来了一些锁问题，因此我们稍微重新处理了该过程，并将 where 更改为子查询以返回表中的前 1 RowID(主键)。这似乎有助于事情运行得更顺利一些，但随后我们偶尔会再次在数据库中过载。

UPDATE TOP (1) dbo.Test WITH (ROWLOCK)
SET Status = 1, 
    VMID = @VMID, 
    ReadCount = ReadCount + 1,
    ProcessDT = GETUTCDATE()
OUTPUT INSERTED.RowID INTO @retValue
-- WHERE Status = 0
WHERE RowID IN (SELECT TOP 1 RowID FROM do.Test WHERE Status = 0 ORDER BY RowID)

我们发现表中存在大量状态 1 和 2 记录会导致速度变慢。我们认为这是来自对“状态”列的表扫描。我们添加了以下索引，但它并没有帮助解决锁定。

CREATE NONCLUSTERED INDEX IX_Test_Status_RowID
ON [dbo].[Test] ([Status])
INCLUDE ([RowID])

更新后的最后一步，我们使用返回的 RowID 来选择详细信息:

SELECT 'Test' as FileName, *, @Nick as [Nickname] 
FROM Test WITH (NOLOCK)
WHERE RowID IN (SELECT id from @retValue)

锁的类型

大多数 block 是 LCK_M_U 和 LCK_M_S，我希望 UPDATE 和 SELECT 查询会出现这种情况。我们偶尔也会有 1 或 2 个 LCK_M_X 锁。这让我觉得我们的“独特”记录代码可能仍然会发生冲突。

问题

1. 这些锁和锁的数量只是这种类型加载的正常 SQL 操作吗？
2. 子查询导致的问题是否比我们开始的 UPDATE 中的 TOP(1) 更多？我正在尝试确认我可以删除 ORDER BY 语句并删除额外的处理步骤。
3. 不同的索引会有帮助吗？我最初想知道索引更新是否是导致锁定的可能原因，但现在我不确定。
4. 是否有更好或更有效的方法来获取唯一的 RowID？
5. WITH (ROWLOCK) 导致的锁定是否比不使用它时导致的锁定更多？这个想法是 ROWLOCK 只会锁定 1 个特定记录并允许另一个过程更新另一个记录并选择而不锁定表或页面。
6. 是否有人推荐任何工具来进行压力测试并同时运行 100 个查询，以测试任何潜在的解决方案？

对于所有问题，我们深表歉意，只是想确保我尽可能清楚地了解我们的流程和我们遇到的问题。

提前感谢您提供任何见解，因为这对我们来说确实是一个令人沮丧的问题。

硬件

我们在具有 24 GB RAM 的双 Xeon CPU 上运行 SQL Server 2008 R2。所以我们应该为这个过程提供足够的动力。

Answer 1

看起来该问题的最佳解决方案是创建一个具有标识的单独表，并使用插入中的 @@IDENTITY 来确定要处理的下一行。这已经解决了我迄今为止在压力测试中的所有锁定问题。感谢所有为我指明正确方向的人!