SQL Server，使用表作为队列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4686726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server, using a table as a queue
提问by Barguast
I'm using an SQL Server 2008 R2 as a queuing mechanism. I add items to the table, and an external service reads and processes these items. This works great, but is missing one thing - I need mechanism whereby I can attempt to select a single row from the table and, if there isn't one, block until there is (preferably for a specific period of time).
我使用 SQL Server 2008 R2 作为排队机制。我将项目添加到表中，外部服务读取并处理这些项目。这很好用，但缺少一件事 - 我需要一种机制，我可以尝试从表中选择一行，如果没有，则阻塞直到有（最好在特定时间段内）。
Can anyone advise on how I might achieve this?
任何人都可以就我如何实现这一目标提出建议吗？
采纳答案by Andomar
I need mechanism whereby I can attempt to select a single row from the table and, if there isn't one, block until there is (preferably for a specific period of time).
我需要一种机制，我可以尝试从表中选择一行，如果没有，则阻塞直到有（最好在特定时间段内）。
You can loop and check for new rows every second:
您可以每秒循环并检查新行：
while not exists (select * from QueueTable)
begin
wait for delay '00:01'
end
Disclaimer: this is not code I would use for a production system, but it does what you ask.
免责声明：这不是我用于生产系统的代码，但它可以满足您的要求。
回答by Remus Rusanu
The only way to achieve a non-pooling blocking dequeue is WAITFOR (RECEIVE). Which implies Service Broker queues, with all the added overhead.
实现非池化阻塞出队的唯一方法是WAITFOR (RECEIVE). 这意味着 Service Broker 队列，以及所有额外的开销。
If you're using ordinary tables as queuesyou will not be able to achieve non-polling blocking. You must poll the queue by asking for a dequeue operation, and if it returns nothing, sleep and try again later.
如果您使用普通表作为队列，您将无法实现非轮询阻塞。您必须通过请求出队操作来轮询队列，如果它没有返回任何内容，请休眠并稍后再试。
I'm afraid I'm going to disagree with Andomar here: while his answer works as a generic question 'are there any rows in the table?' when it comes to queueing, due to the busy nature of overlapping enqueue/dequeue, checking for rows like this is a (almost) guaranteed deadlock under load. When it comes to using tables as queue, one must always stick to the basic enqueue/dequeue operations and don't try fancy stuff.
恐怕我会在这里不同意 Andomar 的观点：虽然他的回答是一个通用问题，“表中是否有任何行？” 在排队时，由于重叠入队/出队的繁忙性质，检查这样的行是（几乎）保证在负载下死锁。当谈到使用表作为队列时，必须始终坚持基本的入队/出队操作，不要尝试花哨的东西。
回答by mcintyre321
"since SQL Server 2005 introduced the OUTPUT clause, using tables as queues is no longer a hard problem". A great post on how to do this.
“自从 SQL Server 2005 引入了 OUTPUT 子句，使用表作为队列不再是一个难题”。关于如何做到这一点的好帖子。
回答by RMD
The previous commenter that suggested using Service Broker likely had the best answer. Service Broker allows you to essentially block while waiting for more input.
之前建议使用 Service Broker 的评论者可能给出了最佳答案。Service Broker 允许您在等待更多输入的同时进行阻塞。
If Service Broker is overkill, you should consider a different approach to your problem. Can you provide more details of what you're trying to do?
如果 Service Broker 太过分了，您应该考虑采用不同的方法来解决您的问题。你能提供更多关于你正在尝试做什么的细节吗？
回答by Didaxis
Let me share my experiences with you in this area, you may find it helpful.
让我与您分享我在这方面的经验，您可能会发现它有所帮助。
My team first used MSMQ transactional queues that would feed our asynchronous services (be they IIS hosted or WAS). The biggest problem we encountered was MS DTC issues under heavy load, like 100+ messages/second load; all it took was one slow database operation somewhere to start causing timeout exceptions and MS DTC would bring the house down so to speak (transactions would actually become lost if things got bad enough), and although we're not 100% certain of the root cause to this day, we do suspect MS DTC in a clustered environment has some serious issues.
我的团队首先使用 MSMQ 事务队列来提供我们的异步服务（无论是 IIS 托管还是 WAS）。我们遇到的最大问题是重负载下的 MS DTC 问题，例如 100+ 条消息/秒负载；所需要的只是某个地方的一个缓慢的数据库操作开始导致超时异常，并且 MS DTC 可以这么说（如果事情变得足够糟糕，事务实际上会丢失），尽管我们不是 100% 确定根直到今天，我们确实怀疑集群环境中的 MS DTC 存在一些严重问题。
Because of this, we started looking into different solutions. Service Bus for Windows Server (the on-premise version of Azure Service Bus) looked promising, but it was non-transactional so didn't suit our requirements.
因此，我们开始研究不同的解决方案。Windows Server 的服务总线（Azure 服务总线的内部部署版本）看起来很有前景，但它是非事务性的，因此不符合我们的要求。
We finally decided on the roll-your-own approach, an approach suggested to us by the guys who built the Azure Service Bus, because of our transactional requirements. Essentially, we followed the Azure Worker Role model for a worker role that would be fed via some queue; a polling-blocking model.
我们最终决定采用自己动手的方法，这是由构建 Azure 服务总线的人向我们建议的方法，因为我们的事务要求。本质上，我们遵循 Azure Worker Role 模型来获取通过某个队列提供的辅助角色；轮询阻塞模型。
Honestly, this has been far better for us than anything else we've used. The pseudocode for such a service is:
老实说，这对我们来说比我们使用过的任何其他东西都要好得多。这种服务的伪代码是：
hasMsg = true
while(true)
if(!hasMsg)
sleep
msg = GetNextMessage
if(msg == null)
hasMsg = false
else
hasMsg = true
Process(msg);
We've found that CPU usage is significantly lower this way (lower than traditional WCF services).
我们发现 CPU 使用率以这种方式显着降低（低于传统的 WCF 服务）。
The tricky part of course is handling transactions. If you'd like to have multiple instances of your service read from the queue, you'll need to employ read-past/updlock in your sql, and also have your .net service enlist in the transactions in a way that will roll-back should the service fail. in this case, you'll want to go with retry/poison queues as tables in addition to your regular queues.
棘手的部分当然是处理交易。如果您希望从队列中读取您的服务的多个实例，您需要在您的 sql 中使用 read-past/updlock，并且还让您的 .net 服务以滚动的方式登记在事务中 -如果服务失败，则返回。在这种情况下，除了常规队列之外，您还需要使用重试/毒药队列作为表。
