3

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?

RacerX
  • 2,566
  • 3
  • 23
  • 21
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • See [this question](http://stackoverflow.com/questions/2295385/row-locks-manually-using-them) and [a related blog post](http://www.adathedev.co.uk/2010/03/queue-table-processing-in-sql-server.html). – ig0774 Mar 25 '11 at 14:32

3 Answers3

2

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
Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
1

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)
RacerX
  • 2,566
  • 3
  • 23
  • 21
0

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.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • So how do you deal with the requirement to retrieve the columns for the task as an atomic operation? This is precisely the sort of use case the explicit locking is made for. – ig0774 Mar 25 '11 at 14:37
  • 1
    Make sure you don't run it under SERIALIZABLE - you can get a conversion deadlock. – A-K Mar 25 '11 at 16:12
  • It's open to a race as the SELECT is executed before the UPDATE, so it's entirely possible that two threads update the same record twice (which may not be a problem as they'll update the record to the same value, but I have seen it happen). The only way to deal with that is with explicit locking hints. – Chris J Jun 21 '11 at 12:39