How do I use locking hints so that two parallel queries return non-intersecting results?

Question

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?

Answer 1

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

Answer 2

what about something like this:

UPDATE TOP (1) Tasks 
    SET State = Processing 
    OUTPUT INSERTED.RetrievedTaskId 
    WHERE State = ReadyForProcessing 

test it out:

DECLARE @Tasks table (RetrievedTaskId  int, State char(1))
INSERT @Tasks VALUES (1,'P')
INSERT @Tasks VALUES (2,'P')
INSERT @Tasks VALUES (3,'R')
INSERT @Tasks VALUES (4,'R')

UPDATE TOP (1) @Tasks
  SET State = 'P'
  OUTPUT INSERTED.RetrievedTaskId
  WHERE State = 'R'

SELECT * FROM @Tasks

--OUTPUT:

RetrievedTaskId
---------------
3

(1 row(s) affected)

RetrievedTaskId State
--------------- -----
1               P
2               P
3               P
4               R

(4 row(s) affected)