ORDER BY和WITH(ROWLOCK，UPDLOCK，READPAST) [英] ORDER BY and WITH(ROWLOCK, UPDLOCK, READPAST)

问题描述

我需要使用一些SQL表来设置队列系统，例如

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

我在第一个表中使用ROWLOCK，在第二个表中使用UPDLOCK，因为在此选择之后，我将仅更新TableB，尽管我需要确保这些行不会被更新在TableA中通过任何其他并发查询.一切顺利，直到需要在上述任何SELECT中插入ORDER BY子句的位置，这样才只能选择非常特定的ID(我必须这样做).会发生什么:

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)如果没有ORDER BY，则两个并发执行将按需要执行，并返回不同且不重叠的结果；但是，它们不会返回我想要的结果，因为这些精确结果超出了每个SELECT语句的范围.

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)使用ORDER BY和两个并发执行，只有第一个返回结果.第二个不返回任何内容.

2) Using ORDER BY and two concurrent executions, only the first one returns results. The second one does not return anything.

我记得在博客上看到，对于使WITH (ROWLOCK, READPAST)和ORDER BY起作用的这类查询，需要在排序中使用的字段上创建索引.我尝试过，但是得到了相同的结果.我该如何解决这个问题?

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?

例如，如果我有一个表TestTable，其中的字段(TestID INT，Value INT)和值(1,1)，(2,2)，...并同时"执行

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

第一个执行返回预期的行(1,2)，第二个执行返回(3,4).但是，如果我执行

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

第一个返回(1，2)，第二个不返回.为什么会这样?！

the first one returns (1, 2) and the second returns nothing. Why is this?!

推荐答案

符合预期

• 带有ORDER BY，不带ROWLOCK，不带索引的SELECT将具有表锁定，这是因为要进行TOP 2的扫描/中间排序.因此，由于READPAST，第二个会话会跳过整个表

• 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

不带ORDER BY的SELECT只是选择任意2行，它们恰好是插入顺序(纯巧合，没有隐含顺序).这两个行被锁定的事实导致第二个会话跳到下一个未锁定的行.

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尝试使锁尽可能细化，但是扫描意味着表锁.现在，这通常没有什么区别(它将是共享的读取锁)，但是您也具有UPDLOCK，这意味着排他锁定的表

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

所以，您需要这两个

SELECT查询(ROWLOCK，UPDLOCK，READPAST)中的
• 3条提示可控制粒度，隔离和并发.
  仅使用ROWLOCK仍会导致对每行进行扫描/排序的排他锁.
• Value上的索引包括TestID，以提高SELECT的效率.索引可能只会解决并发问题，但无法保证.

• 3 hints in the SELECT queries (ROWLOCK, UPDLOCK, READPAST) to control granularity, isolation and concurrency.
  Using ROWLOCK only will still cause an exclusive lock on every row to scan/sort.
• an index on Value INCLUDE TestID to 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

这篇关于ORDER BY和WITH(ROWLOCK，UPDLOCK，READPAST)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！