在“选择前n个”查询中返回未锁定的行

Question

在“选择前n个”查询中返回未锁定的行

7

我需要一个MsSql数据库表和另外8个(相同的)进程并行访问同一张表，进行select top n操作，处理这些n行，以及更新这些行中的某一列。问题在于我需要仅选取及处理每一行一次。这意味着如果一个进程已经连接到数据库并选择了前n行，当第二个进程来时，它应该发现这些行被锁定了，然后去选择从n到2*n行，以此类推……

是否有可能在选择行时对某些行加锁，并且当有人请求被锁定的前n行时返回下一行，而不是等待被锁定的行？看起来不太可能，但……

我还在想另一件事-也许不那么优雅，但听起来简单而安全，就是在数据库中为访问该表的实例设置一个计数器。第一个实例到来时会增加计数器并选择前n行，接下来的实例会递增计数器并选择从(n*(i-1))到(n*i)的行，以此类推……

听起来这个想法如何？你有更好的建议吗？非常感谢您的时间。

- Diana

3

听起来你正在使用表格作为队列？你可能会发现这篇文章对你有帮助，作者是Remus Rusanu。 - Martin Smith

感谢提供链接。了解这一点确实很有趣，但由于我需要在选择行之后保留它们（删除它们不是一个选项），所以它并不适用于我的情况。 - Diana

1

你可以使用一个 processed 位列，并可能将 DELETE 替换为 UPDATE。如果你决定使用计数器解决方案，这个答案可能会有所帮助。 - Martin Smith

@Martin - 你关于更新位列的观点是正确的。加上“readpast”锁定将会得到与AdaTheDev相同的解决方案 - 这正是我所需要的。谢谢！ - Diana

4个回答

2

最简单的方法是使用行锁定机制：row locking。

BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

但是如果您访问数据然后关闭连接，您将无法使用此方法。

您需要锁定行的时间有多长？最好的方法可能确实是如您所说，在所选行上放置计数器（最好使用UPDATE中的OUTPUT子句来完成）。

- Codesleuth

我需要在选择后关闭连接，并且处理一批行需要一些时间（每行将确定发送一个带有500kb附件的电子邮件）。 - Diana

我认为你需要实现自己的行锁定方法（例如计数器），因为行锁定可能会阻塞请求，从而导致超时错误出现在其他地方。Martin的评论看起来是一个前进的方式。http://stackoverflow.com/questions/3636950/return-unlocked-rows-in-a-select-top-n-query#comment-3822218 - Codesleuth

0

编辑：啊，没事了，你正在使用离线模式工作。这样怎么样：

UPDATE TOP (@n) QueueTable SET Locked = 1
OUTPUT INSERTED.Col1, INSERTED.Col2 INTO @this
WHERE Locked = 0

<do your stuff>

也许你正在寻找READPAST提示？

<begin or save transaction>

INSERT INTO @this (Col1, Col2) 
SELECT TOP (@n) Col1, Col2 
FROM Table1 WITH (ROWLOCK, HOLDLOCK, READPAST)

<do your stuff>

<commit or rollback>

- Peter Radocchia

0

如果您希望以这种方式选择记录，最好的想法是在单独的表中使用计数器。

您真的不想在生产数据库上独占地锁定行，因此我建议使用计数器。这样，您的进程中只有一个能够同时获取该计数器编号（因为正在更新时将进行锁定），这将为您提供所需的并发性。

如果您需要帮助编写编写这些表和过程（简单且安全），请随时提出。

- JonVD

好的，很高兴看到计数器的想法听起来足够好，而且一开始没有发现任何主要问题。我会再多考虑一天左右。感谢您的及时回复和帮助！ - Diana

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- AdaTheDev · Accepted Answer

以下是我之前博客中提到的一个示例：http://www.adathedev.co.uk/2010/03/queue-table-processing-in-sql-server.html

READPAST提示确保多个进程在轮询记录以进行处理时不会相互阻塞。此外，在此示例中，我使用了一个位字段来实现对记录的物理“锁定” - 如果需要，可以使用日期时间。

DECLARE @NextId INTEGER
BEGIN TRANSACTION

-- Find next available item available
SELECT TOP 1 @NextId = ID
FROM QueueTable WITH (UPDLOCK, READPAST)
WHERE IsBeingProcessed = 0
ORDER BY ID ASC

-- If found, flag it to prevent being picked up again
IF (@NextId IS NOT NULL)
    BEGIN
        UPDATE QueueTable
        SET IsBeingProcessed = 1
        WHERE ID = @NextId
    END

COMMIT TRANSACTION

-- Now return the queue item, if we have one
IF (@NextId IS NOT NULL)
    SELECT * FROM QueueTable WHERE ID = @NextId