行锁-手动使用它们

我建议采用的方法是在记录中沿其行设置一个字段,以指示是否正在处理记录。然后,实现执行以下操作的“从队列中读取下一个”存储过程,以确保没有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

Service Broker Queues 在SQL Server中专门设计用于解决此方案。

必须锁定表和行似乎是实现此功能的向后方法。

有关此技术的详细文章“使用readpass和updlock在SQL Server中处理数据队列”

https://www.mssqltips.com/sqlservertip/1257/processing-data-queues-in-sql-server-with-readpast-and-updlock/