使用DB表作为消息/作业队列的最佳方法 [英] Best way to use a DB table as a message/job queue

问题描述

我有一个数据库表，其中有〜50K行，每行代表一个需要完成的工作。我有一个程序从数据库中提取作业，执行作业，并将结果放回db。 （此系统现在正在运行）

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 sproce, my current though is to replace said sproce with something that looks something like this

update tbl set owner=connection_id() where avalable and owner is null limit 1;
select stuff from tbl where owner = connection_id();

BTW;工作者的任务可能在获取作业和提交结果之间产生连接。此外，我不期望数据库甚至接近瓶颈，除非我把这部分（约每分钟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反模式只是稍微apropos这里，因为1）我没有做IPC（没有过程生成行，他们都已经存在了）和2）反模式是，它导致不必要的负载在数据库，因为进程等待消息（在我的情况下，如果没有消息，一切都可以关闭，因为一切都完成）

Note: the "Database as an IPC anti-pattern" is only slightly apropos here because 1) I'm not doing IPC (there is no process generating the rows, they all already exist right now) and 2) the primary gripe described for that anti-pattern is that it results in unneeded load on the DB as processes wait for messages (in my case, if there are no messages, everything can shutdown as everything is done)

推荐答案

这里是我过去成功使用的：

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。当它完成后，它将状态更改为Complete。它可以删除消息或不是取决于您是否要保留审计跟踪。状态='N'的邮件以MsgType /时间戳顺序提取，因此在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

这篇关于使用DB表作为消息/作业队列的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！