如何使用锁定提示，以便两个并行查询返回非交叉结果？(How do I use locking hints so that two parallel queries return non

如何使用锁定提示，以便两个并行查询返回非交叉结果？(How do I use locking hints so that two parallel queries return non-intersecting results?)

我有一个带有Id和State列的SQL表Tasks 。 我需要执行以下操作：使用状态ReadyForProcessing查找任何一个任务，检索其所有列并将其状态设置为Processing 。 像（伪代码）的东西：

BEGIN TRANSACTION; SELECT TOP 1 * FROM Tasks WHERE State = ReadyForProcessing // here check if the result set is not empty and get the id, then UPDATE Tasks SET State = Processing WHERE TaskId = RetrievedTaskId END TRANSACTION

此查询将从多个数据库客户端并行运行，其想法是，如果两个客户端并行运行查询，则它们将获取不同的任务，从不执行相同的任务。

看起来我需要锁定提示。 我已经阅读了这篇MSDN文章，但对此并不了解。 如何使用锁定提示来解决上述问题？

I have an SQL table Tasks with columns Id and State. I need to do the following: find any one task with state ReadyForProcessing, retrieve all its columns and set its state to Processing. Something like (pseudocode):

BEGIN TRANSACTION; SELECT TOP 1 * FROM Tasks WHERE State = ReadyForProcessing // here check if the result set is not empty and get the id, then UPDATE Tasks SET State = Processing WHERE TaskId = RetrievedTaskId END TRANSACTION

This query will be run in parallel from several database clients and the idea is that if two clients run the query in parallel they acquire different tasks and never the same task.

Looks like I need locking hints. I've read this MSDN article but don't understand anything there. How do I use locking hints for solving the above problem?

最满意答案

这应该可以解决问题。

BEGIN TRANSACTION DECLARE @taskId SELECT TOP (1) @taskid = TaskId FROM Tasks WITH (UPDLOCK, READPAST) WHERE State = 'ReadyForProcessing' UPDATE Tasks SET State = 'Processing' WHERE TaskId = @taskid COMMIT TRAN

This should do the trick.

BEGIN TRANSACTION DECLARE @taskId SELECT TOP (1) @taskid = TaskId FROM Tasks WITH (UPDLOCK, READPAST) WHERE State = 'ReadyForProcessing' UPDATE Tasks SET State = 'Processing' WHERE TaskId = @taskid COMMIT TRAN