Подтвердить что ты не робот

Лучший способ использовать таблицу БД в качестве очереди заданий (так называемая пакетная очередь или очередь сообщений)

У меня есть таблица базы данных с ~ 50K строк в ней, каждая строка представляет работу, которая должна быть выполнена. У меня есть программа, которая извлекает работу из БД, выполняет работу и помещает результат обратно в БД. (эта система работает прямо сейчас)

Теперь я хочу разрешить нескольким задачам обработки выполнять задания, но быть уверенным, что ни одна задача не будет выполнена дважды (из-за проблем с производительностью это не вызовет других проблем). Поскольку доступ осуществляется с помощью хранимой процедуры, мой текущий способ - заменить упомянутую хранимую процедуру чем-то вроде этого

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

select stuff 
from tbl 
where owner = connection_id();

КСТАТИ; рабочие задачи могут разорвать связь между получением работы и отправкой результатов. Кроме того, я не ожидаю, что БД приблизится к тому, чтобы стать узким местом, если я не испорчу эту часть (~ 5 заданий в минуту)

Есть ли проблемы с этим? Есть ли лучший способ сделать это?

Примечание. "База данных как антишаблон IPC" здесь только слегка уместна, потому что

  1. Я не делаю IPC (нет процесса генерации строк, все они уже существуют) и
  2. основной недостаток, описанный для этого анти-паттерна, заключается в том, что он приводит к ненужной загрузке БД, поскольку процессы ожидают сообщений (в моем случае, если сообщений нет, все может завершиться, поскольку все сделано)
4b9b3361

ОТВЕТЫ

Ответ 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-to-n, и процессы 0-to-n могут считывать и обрабатывать сообщения. Каждый процесс чтения обычно обрабатывает один тип сообщения. Для балансировки нагрузки может выполняться несколько экземпляров типа процесса.

Читатель извлекает одно сообщение и изменяет состояние на "A" ctive, пока он работает на нем. Когда это будет сделано, он изменит состояние на "C". Он может удалить сообщение или нет в зависимости от того, хотите ли вы сохранить контрольный журнал. Сообщения состояния = 'N' вытягиваются в порядке MsgType/Timestamp, поэтому есть индекс в MsgType + State + CreateTime.

Варианты:
Состояние для "E" rror.
Колонка для кода процесса чтения.
Временные метки для переходов состояний.

Это обеспечило приятный, масштабируемый, видимый и простой механизм для выполнения ряда вещей, подобных описанию. Если у вас есть базовое понимание баз данных, оно довольно надежное и расширяемое.

Код из комментариев:

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

Ответ 2

Лучший способ реализовать очередь заданий в системе реляционных баз данных - это использовать 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 используется как Enum со значениями PENDING (0), APPROVED (1) или SPAM (2).

Если у нас есть несколько одновременно работающих пользователей, пытающихся модерировать записи post, нам нужен способ скоординировать их усилия, чтобы два модератора не просматривали одну и ту же строку post.

Итак, SKIP LOCKED это именно то, что нам нужно. Если два одновременно работающих пользователя, Алиса и Боб, выполняют следующие запросы 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

Мы видим, что Алиса может выбирать первые две записи, а Боб выбирает следующие две записи. Без SKIP LOCKED запрос на блокировку Боба будет блокироваться, пока Алиса не снимет блокировку с первых двух записей.

Подробнее о SKIP LOCKED читайте в этой статье.

Ответ 3

Как возможное изменение технологии, вы можете использовать MSMQ или что-то подобное.

Каждый из ваших заданий/потоков может запрашивать очередь сообщений, чтобы узнать, доступно ли новое задание. Поскольку действие чтения сообщения удаляет его из стека, вы гарантируете, что только одно задание/поток получит сообщение.

Конечно, предполагается, что вы работаете с платформой Microsoft.

Ответ 4

Вместо того, чтобы иметь владельца = null, когда он не принадлежит, вы должны установить его вместо поддельной записи. Поиск нулевого значения не ограничивает индекс, вы можете завершить сканирование таблицы. (это для оракула, SQL-сервер может быть другим)

Ответ 5

Вы пытаетесь реализовать антипаттерн "База данных как IPC". Посмотрите, чтобы понять, почему вы должны правильно пересмотреть свое программное обеспечение.