从存储过程返回SQL结果集之前修改它。

Question

我有一个简单的表在我的SQL Server 2008数据库中：

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

该表存储了需要服务执行的未完成任务的指令。

我希望在未来能够扩展我的系统。到目前为止，只有一台计算机上的一个服务从该表中读取数据。我有一个存储过程，它选择所有未完成和未激活的任务。当服务开始处理任务时，它会更新返回行中所有任务的 task_active 标志。

为了使系统能够进行水平扩展，我希望可以在更多的计算机上部署服务。由于我想防止任务被返回给多个服务，因此必须更新返回未完成和未激活任务的存储过程。

我认为我必须锁定表格（同时只有一个读者 - 我知道我必须使用适当的隔离级别）并在返回结果集之前更新结果集中每一行的 task_active 标志。

所以我的问题是如何修改存储过程中 SELECT 语句返回的结果集？

- m0sa

Answer 1

这是典型的出队模式，使用OUTPUT子句实现，并在MSDN中进行了描述，详情请参见输出（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;

使用ROWLOCK, READPAST提示可以实现高吞吐量和高并发性：多个线程/进程可以在多个线程/进程出队任务时入队新任务。没有顺序保证。

更新

如果您想要对结果进行排序，可以使用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;

我在文章“使用表格作为队列”中讨论了这个和其他enqueue/dequeue技术。

- Remus Rusanu

---

有没有一种方法在调用TOP(1)之前对Task_Table进行排序，而不会失去ROWLOCK和READPAST？ - m0sa