将数据库表用作消息/作业队列的最佳方法

我有一个包含~50K行的数据库表,每行代表一个需要完成的工作.我有一个程序从数据库中提取作业,完成工作并将结果放回到数据库中.(这个系统现在正在运行)

现在我想允许多个处理任务来完成工作,但要确保没有任务完成两次(作为性能问题而不是这会导致其他问题).因为访问是通过sproce进行的,我现在的目的是用看起来像这样的东西替换所说的sproce

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

select stuff 
from tbl 
where owner = connection_id();

BTW; 工作人员的任务可能会在获得工作和提交结果之间失去联系.此外,我不认为DB甚至会接近瓶颈,除非我把那部分搞砸了(每分钟约5个工作)

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

注意:"数据库作为IPC反模式"在这里只是略微适用,因为1)我没有做IPC(没有生成行的过程,它们现在都已存在)和2)描述的主要抱怨反模式是因为进程等待消息导致数据库上出现不必要的负载(在我的情况下,如果没有消息,一切都可以在一切都完成时关闭)

这是我过去成功使用的内容:

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"恐怖的状态.
Reader进程代码列.
状态转换的时间戳.

这提供了一个很好的,可扩展的,可见的,简单的机制,用于执行您正在描述的许多事情.如果您对数据库有基本的了解,那么它非常简单且可扩展.

评论代码:

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

在关系数据库系统中实现作业队列的最佳方法是使用SKIP LOCKED。

SKIP LOCKED是一种锁获取选项，适用于读/共享（FOR SHARE）或写/独占（FOR UPDATE）锁，并且目前得到广泛支持：

Oracle 10g及更高版本

PostgreSQL 9.5及更高版本

SQL Server 2005及更高版本

MySQL 8.0及更高版本

现在，考虑一下我们有post用作工作队列的下表：

CREATE TABLE post (
    id int8 NOT NULL,
    body varchar(255),
    status int4,
    title varchar(255),
    PRIMARY KEY (id)
)

该status列用作枚举，其值为PENDING（0），APPROVED（1）或SPAM（2）。

如果我们有多个并发用户试图审核post记录，则需要一种方法来协调他们的工作，避免让两个主持人审阅同一post行。

因此，SKIP LOCKED正是我们所需要的。如果有两个并发用户Alice和Bob，则执行以下SELECT查询，这些查询专门锁定帖子记录，同时还添加SKIP LOCKED选项：

[Alice]:
SELECT
    p.id AS id1_0_,1
    p.body AS body2_0_,
    p.status AS status3_0_,
    p.title AS title4_0_
FROM
    post p
WHERE
    p.status = 0
ORDER BY
    p.id
LIMIT 2
FOR UPDATE OF p SKIP LOCKED

[Bob]:                                                                                                                                                                                                              
SELECT
    p.id AS id1_0_,
    p.body AS body2_0_,
    p.status AS status3_0_,
    p.title AS title4_0_
FROM
    post p
WHERE
    p.status = 0
ORDER BY
    p.id
LIMIT 2
FOR UPDATE OF p SKIP LOCKED

我们可以看到Alice可以选择前两个记录，而Bob可以选择后两个记录。如果没有SKIP LOCKED，则Bob锁定获取请求将阻塞，直到Alice释放前2个记录上的锁定为止。

有关更多详细信息SKIP LOCKED，请查看本文。