返回"select top n"中未锁定的行.询问 [英] Return unlocked rows in a "select top n" query

问题描述

我需要有一个MsSql数据库表和另外8个(相同的)进程并行访问同一表-选择前n个，处理这n行，并更新这些行的一列.问题是我只需要选择并处理每行一次.这意味着，如果一个进程进入数据库并选择了前n行，则在第二个进程到来时，它应该找到那些被锁定的行并从n到2 * n行中选择行，依此类推...

I need to have a MsSql database table and another 8 (identical) processes accessing the same table in parallel - making a select top n, processing those n rows, and updating a column of those rows. The problem is that I need to select and process each row just once. This means that if one process got to the database and selected the top n rows, when the second process comes it should find those rows locked and select the rows from n to 2*n rows, and so on...

当您选择某些行时，有人请求被锁定的前n行返回下一行，而不是等待已锁定的行时，是否可以对某些行进行锁定?似乎很远，但是...

Is it possible to put a lock on some rows when you select them, and when someone requests top n rows which are locked to return the next rows, and not to wait for the locked ones? Seems like a long shot, but...

我在想的另一件事-也许不是那么优雅，但听起来简单又安全，是在数据库中为在该表上进行选择的实例提供一个计数器.出现的第一个实例将使计数器递增并选择前n个，下一个实例将使计数器递增并从n *(i-1)到n * i选择行，依此类推...

Another thing I was thinking - maybe not so elegant but sounds simple and safe, is to have in the database a counter for the instances which made selects on that table. The first instance that comes will increment the counter and select top n, the next one will increment the counter and select rows from n*(i-1) to n*i, and so on...

听起来像是一个好主意吗?您有更好的建议吗?任何想法都将受到高度赞赏！

Does this sound like a good ideea? Do you have any better suggestions? Any thought is highly appreciated!

感谢您的时间.

推荐答案

以下是示例 READPAST提示可确保在轮询要处理的记录时多个进程不会相互阻塞.另外，在此示例中，我有一个位字段可以物理锁定"记录-如果需要，可以是日期时间.

The READPAST hint is what ensures multiple processes don't block each other when polling for records to process. Plus, in this example I have a bit field to physically "lock" a record - could be a datetime if needed.

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

这篇关于返回"select top n"中未锁定的行.询问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！