sql-server-2005 - ORDER BY 和 WITH(ROWLOCK、UPDLOCK、READPAST)

Question

我需要使用一些 SQL 表来设置队列系统，就像 here 中描述的那样.那就是，因为我需要按不同的标准过滤排队的项目，所以在我使用的存储过程中

BEGIN TRANSACTION

CREATE TABLE #Temp (ID INT, SOMEFIELD INT)

INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition1

INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition2

(...)

UPDATE TableB SET SomeField = 1 FROM TableB WITH (ROWLOCK, READPAST) WHERE ID IN (SELECT ID FROM #Temp)

COMMIT TRANSACTION

我正在使用 ROWLOCK在第一个表中和 UPDLOCK在第二个因为，​​在这个选择之后，我要更新 TableB只是，尽管我需要确保这些行不会在 TableA 中更新通过任何其他并发查询。一切顺利，直到我需要插入 ORDER BY任何 SELECT 中的条款s 以上，以便只选择非常具体的 ID(我必须真的这样做)。发生的事情是:

1) 无 ORDER BY , 根据需要执行两个并发执行，返回不同且不重叠的结果；但是，它们不会返回我想要的结果，因为这些精确结果超出了每个 SELECT 的范围。陈述。

2) 使用 ORDER BY和两个并发执行，只有第一个返回结果。第二个不返回任何东西。

我记得在博客上看到，对于这些类型的查询 WITH (ROWLOCK, READPAST)和 ORDER BY工作需要在排序中使用的字段上创建索引。我试过了，但我得到了相同的结果。我怎样才能解决这个问题？

编辑:例如，如果我有一张 table TestTable带有字段(TestID INT，Value INT)和值“(1,1), (2,2), ...”并“同时”执行

BEGIN TRANSACTION

SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST)

WAITFOR DELAY '00:00:05'

COMMIT TRANSACTION

第一次执行返回行 (1,2)，第二次执行返回 (3,4)。但是，如果我执行

BEGIN TRANSACTION

SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST) ORDER BY VALUE ASC

WAITFOR DELAY '00:00:05'

COMMIT TRANSACTION

第一个返回 (1, 2) ，第二个不返回任何内容。为什么是这样？!

Answer 1

正如预期的那样

带有 ORDER BY、没有 ROWLOCK、没有索引的 SELECT 将有一个表锁，因为扫描/中间排序来计算 TOP 2。因此，由于 READPAST

，第二个 session 跳过了整个表。

没有 ORDER BY 的 SELECT 只是选择任意 2 行，这恰好是按插入顺序排列的(纯巧合，没有隐含的顺序)。这 2 行被锁定的事实导致第二个 session 跳到下一个未锁定的行。

SQL Server 尝试尽可能细化锁定，但扫描意味着表锁定。现在，这通常不会有什么不同(它是一个共享读锁)但是你也有 UPDLOCK，这意味着一个独占锁定的表

所以，你需要这两个

SELECT 查询中的 3 个提示(ROWLOCK、UPDLOCK、READPAST)来控制粒度、隔离和并发。仅使用 ROWLOCK 仍会导致扫描/排序每一行的排他锁。

Value 上的索引包括 TestID使 SELECT 高效。索引可能只会修复并发性，但不能保证。

在您之前的一个问题中，我将我的回答(在评论中)链接到 SQL Server Process Queue Race Condition我有所有 3 个锁定提示