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

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 = Processin开发者_如何学编程g 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?

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

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)

I really, really don't like explicit locking in databases, it's a source of all sorts of crazy bugs - and the performance of the database can drop through the floor.

I'd suggest re-writing the SQL along the following lines:

begin transaction;

update tasks
set state = processing
where state = readyForProcessing
and ID = (select min(ID) from tasks where state = readyForProcessing);

commit; 

This way, you don't need to lock anything - and because the update is atomic, there's no risk of two processes updating the same record.