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 Proc

Solution 1:

This should do the trick.

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

Solution 2:

what about something like this:

UPDATE TOP(1) Tasks 
    SETState= Processing 
    OUTPUT INSERTED.RetrievedTaskId 
    WHEREState= ReadyForProcessing 

test it out:

DECLARE@Taskstable (RetrievedTaskId  int, State char(1))
INSERT@TasksVALUES (1,'P')
INSERT@TasksVALUES (2,'P')
INSERT@TasksVALUES (3,'R')
INSERT@TasksVALUES (4,'R')

UPDATE TOP (1) @TasksSET 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)

Solution 3:

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 = (selectmin(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.