如何使用锁定提示，以使两个并行查询返回不相交的结果？ [英] How do I use locking hints so that two parallel queries return non-intersecting results?

问题描述

我有一个包含列 Id 和状态的SQL表任务 $ c>。我需要做以下事情：找到任何一个任务与状态 ReadyForProcessing ，检索所有的列，并将其状态设置为处理。类似（伪代码）：

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.

看起来我需要锁定提示。我已阅读此MSDN文章，但不了解任何内容那里。如何使用锁定提示来解决上述问题？

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

这篇关于如何使用锁定提示，以使两个并行查询返回不相交的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！