行锁-手动使用它们

Question

我基本上有一个具有5个线程的应用程序，每个线程都从一个表中读取。该查询是来自表的简单SELECT TOP 1
*，但是我想强制执行锁定，以便下一个线程将从表中选择下一个记录，而不是锁定的记录。当应用程序完成其任务后，它将更新锁定的记录并释放锁定，然后再次重复该过程。这可能吗？

Answer 1

我建议的一种方法是在记录中沿记录的字段显示指示是否正在处理记录的字段。然后实现一个“从队列中读取下一个”存储过程，该存储过程执行以下操作，以确保没有2个进程选择相同的记录：

BEGIN TRANSACTION

-- Find the next available record that's not already being processed.
-- The combination of UPDLOCK and READPAST hints makes sure 2 processes don't 
-- grab the same record, and that processes don't block each other.
SELECT TOP 1 @ID = ID
FROM YourTable WITH (UPDLOCK, READPAST)
WHERE BeingProcessed = 0

-- If we've found a record, set it's status to "being processed"
IF (@ID IS NOT NULL)
    UPDATE YourTable SET BeingProcessed = 1 WHERE ID = @ID

COMMIT TRANSACTION

-- Finally return the record we've picked up
IF (@ID IS NOT NULL)
    SELECT * FROM YourTable WHERE ID = @ID

有关这些表提示的更多信息，请参见MSDN。