Modify SQL result set before returning from stored procedure

Question

I have a simple table in my SQL Server 2008 DB:

Tasks_Table
-id
-task_complete
-task_active
-column_1
-..
-column_N

The table stores instructions for uncompleted tasks that have to be executed by a service.

I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored procedure, that selects all uncompleted and inactive tasks. As the service begins to process tasks it updates the task_active flag in all the returned rows.

To enable scaleing of the system I want to enable deployment of the service on more machines. Because I want to prevent a task being returned to more than 1 service I have to update the stored procedure that returns uncompleted and inactive tasks.

I figured that i have to lock the table (only 1 reader at a time - I know I have to use an apropriate ISOLATION LEVEL), and updates the task_active flag in each row of the result set before returning the result set.

So my question is how to modify the SELECT result set iin the stored procedure before returning it?

Answer 1

This is the typical dequeue pattern, is implemented using the OUTPUT clause and and is described in the MSDN, see the Queues paragraph in OUTPUT Clause (Transact-SQL):

UPDATE TOP(1) Tasks_Table WITH (ROWLOCK, READPAST)
 SET task_active = 1
 OUTPUT INSERTED.id,INSERTED.column_1, ...,INSERTED.column_N 
 WHERE task_active = 0;

The ROWLOCK,READPAST hint allows for high throughput and high concurency: multiple threads/processed can enqueue new tasks while mutliple threads/process dequeue tasks. There is no order guarantee.

Updated

If you want to order the result you can use a CTE:

WITH cte AS (
  SELECT TOP(1) id, task_active, column_1, ..., column_N
  FROM Task_Table WITH (ROWLOCK, READPAST)
  WHERE task_active = 0
  ORDER BY <order by criteria>)
UPDATE cte
  SET task_active = 1
OUTPUT INSERTED.id, INSERTED.column_1, ..., INSERTED.column_N;

I discussed this and other enqueue/dequeue techniques on the article Using Tables as Queues.