使用数据库表作为队列 [英] Using a database table as a queue

问题描述

我想使用数据库表作为队列。我想插入它，并从中以插入顺序（FIFO）中的元素。我主要考虑的是性能，因为我每秒有成千上万的这些交易。所以我想使用一个SQL查询，给我第一个元素，而不搜索整个表。我读取它时不会删除一行。
SELECT TOP 1 ..... help here？
我应该使用任何特殊的索引吗？

I want to use a database table as a queue. I want to insert in it and take elements from it in the inserted order (FIFO). My main consideration is performance because I have thousands of these transactions each second. So I want to use a SQL query that gives me the first element without searching the whole table. I do not remove a row when I read it. Does SELECT TOP 1 ..... help here? Should I use any special indexes?

推荐答案

我将使用IDENTITY字段作为主键，为每个排队项目唯一增加ID，并在其上粘贴聚集索引。这将代表项目排队的顺序。

I'd use an IDENTITY field as the primary key to provide the uniquely incrementing ID for each queued item, and stick a clustered index on it. This would represent the order in which the items were queued.

要在处理队列表时保持这些项目，您需要一个状态字段指示特定项目的当前状态（例如，0 =等待，1 =正在处理，2 =处理）。这是为了防止一个项目被处理两次。

To keep the items in the queue table while you process them, you'd need a "status" field to indicate the current status of a particular item (e.g. 0=waiting, 1=being processed, 2=processed). This is needed to prevent an item be processed twice.

当处理队列中的项目时，您需要找到表中当前未处理的下一个项目。这将需要以这样的方式，以防止多个进程拾取相同的项目同时处理，如下所示。请注意实施时您应该注意的表提示 UPDLOCK和READPAST队列。

When processing items in the queue, you'd need to find the next item in the table NOT currently being processed. This would need to be in such a way so as to prevent multiple processes picking up the same item to process at the same time as demonstrated below. Note the table hints UPDLOCK and READPAST which you should be aware of when implementing queues.

例如在sproc中，像这样：

e.g. within a sproc, something like this:

DECLARE @NextID INTEGER

BEGIN TRANSACTION

-- Find the next queued item that is waiting to be processed
SELECT TOP 1 @NextID = ID
FROM MyQueueTable WITH (UPDLOCK, READPAST)
WHERE StateField = 0
ORDER BY ID ASC

-- if we've found one, mark it as being processed
IF @NextId IS NOT NULL
    UPDATE MyQueueTable SET Status = 1 WHERE ID = @NextId

COMMIT TRANSACTION

-- If we've got an item from the queue, return to whatever is going to process it
IF @NextId IS NOT NULL
    SELECT * FROM MyQueueTable WHERE ID = @NextID

如果处理项目失败，你想以后能再次尝试吗？如果是这样，您需要将状态重置为0或某事。这将需要更多的思考。

If processing an item fails, do you want to be able to try it again later? If so, you'll need to either reset the status back to 0 or something. That will require more thought.

或者，不要使用数据库表作为队列，但像MSMQ - 只是想我会把混合！

Alternatively, don't use a database table as a queue, but something like MSMQ - just thought I'd throw that in the mix!

这篇关于使用数据库表作为队列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！