将数据库表用作作业队列(即批处理队列或消息队列)的最佳方法 [英] The best way to use a DB table as a job queue (a.k.a batch queue or message queue)
问题描述
我有一个数据库表，其中有约5万行，每行代表一个需要完成的工作.我有一个程序可以从数据库中提取作业，执行该作业并将结果放回数据库中. (此系统现在正在运行)
I have a databases table with ~50K rows in it, each row represents a job that need to be done. I have a program that extracts a job from the DB, does the job and puts the result back in the db. (this system is running right now)
现在，我想允许一个以上的处理任务来执行工作，但是请确保没有一个任务执行两次(出于性能方面的考虑，不要这样做会导致其他问题).由于访问是通过存储过程进行的，因此，我目前的做法是用看起来像这样的东西替换所述存储过程
Now I want to allow more than one processing task to do jobs but be sure that no task is done twice (as a performance concern not that this will cause other problems). Because the access is by way of a stored procedure, my current though is to replace said stored procedure with something that looks something like this
update tbl
set owner = connection_id()
where available and owner is null limit 1;
select stuff
from tbl
where owner = connection_id();
顺便说一句；工人的任务可能会导致找工作和提交结果之间的联系中断.另外，除非我弄乱了这个部分(每分钟约5个工作)，否则我不希望数据库接近瓶颈.
BTW; worker's tasks might drop there connection between getting a job and submitting the results. Also, I don't expect the DB to even come close to being the bottle neck unless I mess that part up (~5 jobs per minute)
这有什么问题吗?有更好的方法吗?
Are there any issues with this? Is there a better way to do this?
注意:作为IPC反模式的数据库" 在这里仅是适当的，因为
Note: the "Database as an IPC anti-pattern" is only slightly apropos here because
- 我不是在进行IPC(没有进程生成行，它们现在已经全部存在)和
- 针对该反模式的主要描述是，当进程等待消息时，它会导致数据库上不必要的负载(在我的情况下，如果没有消息，则一切都可以在完成后关闭)
推荐答案
这是我过去成功使用的内容:
Here's what I've used successfully in the past:
MsgQueue表架构
MsgQueue table schema
MsgId identity -- NOT NULL
MsgTypeCode varchar(20) -- NOT NULL
SourceCode varchar(20) -- process inserting the message -- NULLable
State char(1) -- 'N'ew if queued, 'A'(ctive) if processing, 'C'ompleted, default 'N' -- NOT NULL
CreateTime datetime -- default GETDATE() -- NOT NULL
Msg varchar(255) -- NULLable
您所期望的消息类型-符合插入过程和读取过程之间的约定的消息，消息以XML或其他表示形式进行构造(在某些情况下，JSON很方便例如).
Your message types are what you'd expect - messages that conform to a contract between the process(es) inserting and the process(es) reading, structured with XML or your other choice of representation (JSON would be handy in some cases, for instance).
然后可以插入0到n进程，并且0到n进程可以读取和处理消息.每个读取过程通常处理一种消息类型.可以运行一个进程类型的多个实例来进行负载平衡.
Then 0-to-n processes can be inserting, and 0-to-n processes can be reading and processing the messages, Each reading process typically handles a single message type. Multiple instances of a process type can be running for load-balancing.
阅读器提取一条消息，并在其起作用时将其状态更改为"A"活动.完成后，它将状态更改为"C"全.它是否可以删除邮件取决于您是否要保留审核跟踪.状态消息'N'按MsgType/Timestamp顺序拉出，因此MsgType + State + CreateTime上有一个索引.
The reader pulls one message and changes the state to "A"ctive while it works on it. When it's done it changes the state to "C"omplete. It can delete the message or not depending on whether you want to keep the audit trail. Messages of State = 'N' are pulled in MsgType/Timestamp order, so there's an index on MsgType + State + CreateTime.
变化:
状态为"E"错误.
阅读器过程代码列.
状态转换的时间戳.
Variations:
State for "E"rror.
Column for Reader process code.
Timestamps for state transitions.
这提供了一种不错的，可伸缩的，可见的，简单的机制来执行您正在描述的许多事情.如果您对数据库有基本的了解，那么它就是万无一失且可扩展的.
This has provided a nice, scalable, visible, simple mechanism for doing a number of things like you are describing. If you have a basic understanding of databases, it's pretty foolproof and extensible.
注释代码:
CREATE PROCEDURE GetMessage @MsgType VARCHAR(8) )
AS
DECLARE @MsgId INT
BEGIN TRAN
SELECT TOP 1 @MsgId = MsgId
FROM MsgQueue
WHERE MessageType = @pMessageType AND State = 'N'
ORDER BY CreateTime
IF @MsgId IS NOT NULL
BEGIN
UPDATE MsgQueue
SET State = 'A'
WHERE MsgId = @MsgId
SELECT MsgId, Msg
FROM MsgQueue
WHERE MsgId = @MsgId
END
ELSE
BEGIN
SELECT MsgId = NULL, Msg = NULL
END
COMMIT TRAN
这篇关于将数据库表用作作业队列(即批处理队列或消息队列)的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
