将DB表用作作业队列（即批处理队列或消息队列）的最佳方法

一尘不染

将DB表用作作业队列（即批处理队列或消息队列）的最佳方法

sql

我有一个约有5万行的数据库表，每一行代表一项需要完成的工作。我有一个程序可以从数据库中提取作业，执行该作业并将结果放回数据库中。（此系统正在运行）

现在，我要允许一个以上的处理任务来执行工作，但是请确保没有一个任务执行两次（出于性能方面的考虑，这不会导致其他问题）。因为访问是通过存储过程进行的，所以我目前的工作是用看起来像这样的东西替换所述存储过程

update tbl 
set owner = connection_id() 
where available and owner is null limit 1;

select stuff 
from tbl 
where owner = connection_id();

顺便提一句; 工人的任务可能会导致找工作和提交结果之间的联系中断。另外，除非我弄乱了这个部分（每分钟约5个工作），否则我不希望数据库接近瓶颈。

这有什么问题吗？有一个更好的方法吗？

注意：“将数据库作为IPC反模式”在这里仅略为合适，因为

我没有在做IPC（没有进程生成行，它们现在已经全部存在）并且
针对该反模式所描述的主要问题是，当进程等待消息时，它会导致数据库上不必要的负载（在我的情况下，如果没有消息，则一切都可以在完成后关闭）

阅读 170

2021-03-17

共1个答案

一尘不染

这是我过去成功使用的方法：

MsgQueue表架构

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对于某些情况是很方便的，对于实例）。

然后可以插入0到n的进程，并且0到n的进程可以读取和处理消息。每个读取过程通常处理单个消息类型。可以运行一个进程类型的多个实例来进行负载平衡。

读者提取一条消息，然后对其进行处理，将状态更改为“ A”活动。完成后，它将状态更改为“ C”全。它是否可以删除邮件取决于您是否要保留审核跟踪。State
=’N’的消息按MsgType / Timestamp顺序拉出，因此MsgType + State + CreateTime上有一个索引。

变体：
“ E”错误的状态。
读取器过程代码列。
状态转换的时间戳。

这提供了一种不错的，可伸缩的，可见的，简单的机制来执行您正在描述的许多事情。如果您对数据库有基本的了解，那么它就是万无一失且可扩展的。

注释中的代码：

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

2021-03-17