ORDER BY 和WITH（行锁、UPDLOCK、READPAST）
我需要使用一些 SQL 表设置一个队列系统，例如 此处描述的表。也就是说，由于我需要按不同的条件过滤排队项目，因此在我使用的存储过程中，
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 中更新。一切都很顺利，直到我需要在上面的任何 SELECT 中插入一个 ORDER BY 子句，以便只选择非常特定的 ID（我真的必须这样做）这）。发生的情况是：
1) 如果没有 ORDER BY，两个并发执行将按需要执行，返回不同且不重叠的结果；但是，它们不会返回我想要的结果，因为这些精确结果超出了每个 SELECT 语句的范围。
2) 使用ORDER BY 和两个并发执行，只有第一个返回结果。第二个不返回任何内容。
我记得在一篇博客上看到，对于使用 WITH (ROWLOCK, READPAST) 和 ORDER BY 进行此类查询，需要在正在使用的字段上创建索引订购。我尝试过，但得到了相同的结果。我怎样才能克服这个问题？
编辑：例如，如果我有一个表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)，而第二个则不返回任何内容。这是为什么？！
I need to set up a queue system using some SQL tables, like the one described here. That being, and since I need to filter queued items by different critera, inside a stored procedure I am using
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
I am using ROWLOCK in the first table and UPDLOCK in the second because, after this select, I am going to update TableB only, though I need to make sure that these lines don't get updated inTableA by any other concurrent query. Everything goes well until the point where I need to insert an ORDER BY clause in any of the SELECTs above, so that only very specific IDs get selected (I must really do this). What happens is:
1) Without ORDER BY, two concurrent executions execute as desired, returning different and non-overlapping results; however, they don't return the results I want because those precise results were outside the scope of every SELECT statement.
2) Using ORDER BY and two concurrent executions, only the first one returns results. The second one does not return anything.
I recall seeing on a blog that for these kind of queries with WITH (ROWLOCK, READPAST) and ORDER BY to work one needs to create indexes on the fields one is using in the ordering. I tried it, but I got the same results. How can I get past this problem?
Edit: For example, if I have a table TestTable with fields (TestID INT, Value INT) and values "(1,1), (2,2), ..." and execute "simultaneously"
BEGIN TRANSACTION
SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST)
WAITFOR DELAY '00:00:05'
COMMIT TRANSACTION
the first execution returns lines (1,2) and the second one returns (3,4) as espected. However, if I execute
BEGIN TRANSACTION
SELECT TOP 2 TestID FROM TestTable WITH (UPDLOCK, READPAST) ORDER BY VALUE ASC
WAITFOR DELAY '00:00:05'
COMMIT TRANSACTION
the first one returns (1, 2) and the second returns nothing. Why is this?!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如预期的那样
带有 ORDER BY、没有 ROWLOCK、没有索引的 SELECT 将具有表锁，因为扫描/中间排序要计算出 TOP 2。因此，第二个会话会因为 READPAST 而跳过整个表
不带 ORDER BY 的 SELECT 只是选取任意 2 行，这恰好是按插入顺序排列的（纯属巧合，没有隐含的顺序）。这两行被锁定的事实导致第二个会话跳到下一个未锁定的行。
SQL Server 尝试使锁尽可能细化，但扫描意味着表锁。现在，这通常不会产生任何影响（它是一个共享读锁），但是您也有 UPDLOCK，这意味着一个独占锁定的表
需要这
仅使用 ROWLOCK 仍会导致对要扫描/排序的每一行使用独占锁。
Value上的索引 INCLUDETestID可以提高 SELECT 的效率。索引可能只能解决并发问题，但不能保证它。在您之前的一个问题中，我将我的答案（在评论中）链接到 SQL Server 进程队列竞争条件，其中我有全部 3 个锁定提示
As expected
The SELECT with the ORDER BY, without ROWLOCK, without index will have a table lock because of a scan/intermediate sort to work out TOP 2. So the 2nd session skips the whole table because of READPAST
The SELECT without the ORDER BY is just picking any 2 rows, which happen to be in order of insert (pure coincidence, there is no implied order). The fact that these 2 rows are locked causes the 2nd session to skip to the next non-locked rows.
SQL Server attempts to keep locks as granular as possible but the scan means a table lock. Now, this wouldn't normally make a difference (it'd be a shared read lock) but you have UPDLOCK too which means an exclusively locked table
So, you need both of these
Using ROWLOCK only will still cause an exclusive lock on every row to scan/sort.
ValueINCLUDETestIDto make the SELECT efficient. An index only will probably fix the concurrency but it won't be guaranteed.In one of your previous questions I linked to my answer (in a comment) to SQL Server Process Queue Race Condition where I have all 3 lock hints