我应该如何从数据库表实现多线程队列？
我的过程如下：
- 用户登录到 Web 应用程序，这会将一个条目放入 UserQueue 表中
- Windows 服务每 x 秒轮询该表并处理每个项目
- 一旦处理完毕，该项目将从 UserQueue 表中删除
这一切都适用于顺序处理但我担心长时间运行的任务可能会阻塞所有其他用户的队列（这对于网络应用程序来说是一个问题）。
我认为 .NET 中的 BlockingCollection 将项目保存在内存中，然后处理它们，但我不能保证 UserQueue 表中的行不会多次放入该集合中（由于 BlockingCollection 的非唯一性）除非我使用数据库标志（例如BeingProcessed = true）。我不热衷于数据库标志，因为如果我的服务因任何原因停止，它可能会在表中留下未处理的项目，并且 BeingProcessed = true。
我是否缺少更标准的方法，或者我应该考虑 Quartz.net 或类似的方法？
My process is as follows:
- User logs into web app and this drops an entry into the UserQueue table
- A Windows Service polls this table every x seconds and processes each item
- Once processed the item is deleted from the UserQueue table
This all works well with sequential processing but I'm concerned that a long-running task could block the queue for all other users (and this would be an issue for the web app).
I considered the BlockingCollection in .NET to hold the items in memory and then process them but I cannot guarantee that a row from the UserQueue table won't get put into that collection more than once (due to the non-unique nature of BlockingCollection) unless I use a database flag (BeingProcessed = true for example). I'm not keen on a database flag because if my service was stopped for any reason it could leave unprocessed items in the table with the BeingProcessed = true.
Is there a more standard approach to this that I am missing or should I consider Quartz.net or similar?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
基本技巧是使用带有日期的测试和设置，而不仅仅是简单的布尔值。下面是你如何做到这一点。
假设您的 UserQueue 表非常简单。目前是这样的：
到目前为止，一切都很好。但我们希望安全地抓住一项任务并用它做一些事情。
首先，让我们稍微改变一下架构：
现在，我们只需遵循一个简单的过程：
select * from UserQueue limit 1声明的作业update UserQueue set dtLocked = NOW() where id = @id and dtLocked is null到NOW()当前为 null因为我们现在对锁使用
日期时间，所以我们可以通过简单的更新语句定期清除死任务，该语句删除早于某个时间（例如五分钟）的锁。作为奖励，这种设计可以让您一次安全地处理多个任务，因此您可以通过简单地启动更多线程来消除用户任务阻塞的任何机会。
The basic trick is to use a test-and-set with a date, rather than just a simple boolean. Here's how you do that.
Let's say that your UserQueue table is super simple. Something like this, at the moment:
So far, so good. But we want to safely grab a task and do something with it.
To start, let's alter the schema slightly:
Now, we simply follow a straight-forward procedure:
select * from UserQueue limit 1NOW()where it is currently null via e.g.update UserQueue set dtLocked = NOW() where id = @id and dtLocked is nullBecause we're now using a
datetimefor the lock, we can clean out dead tasks on a regular basis via simple update statement that deletes locks older than some amount of time—say, five minutes.As a bonus, this design lets you safely process multiple tasks at once, so you can eliminate any chance of a user task blocking by simply firing up more threads.
虽然您的问题可能会受益于数据库事务，但我不确定您是否会从具有相同项目排队的长时间运行进程中获得很多好处。对于这种情况，我建议找到一个对提交的数据有意义的更好的主键，以便队列框架将按顺序应用影响该行的数据。我还建议研究现有的排队框架，例如 Microsoft Queuing 或 IBM 的 MQ（诚然，我不太熟悉排队框架）。
While your problem may benefit from a database transaction, I'm not sure that you would gain much for the long running processes with the same items queuing up. For that scenario I would recommend finding a better primary key that is meaningful to the data being submitted so that the data that would impact that row would be applied sequentially by your queuing framework. I would also recommend looking into existing queuing frameworks like Microsoft Queuing or IBM's MQ (admittedly I'm not very well versed in queuing frameworks).