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

Вот что я успешно использовал в прошлом:

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

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

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

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

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

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.

Столбец 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 запрос на получение блокировки Боба будет блокироваться до тех пор, пока Алиса не снимет блокировку с первых двух записей.

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

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

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

Конечно, это при условии, что вы работаете с платформой Microsoft.

Смотрите ответ Влада для контекста, я просто добавляю эквивалент в Oracle, потому что есть несколько ошибок, о которых нужно знать.

То

SELECT * FROM t order by x limit 2 FOR UPDATE OF t SKIP LOCKED

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

SQL> create table t as
  2   select rownum x
  3   from dual
  4   connect by level <= 100;

Table created.

SQL> declare
  2    rc sys_refcursor;
  3  begin
  4    open rc for select * from t order by x for update skip locked fetch first 2 rows only;
  5  end;
  6  /
  open rc for select * from t order by x for update skip locked fetch first 2 rows only;
                                                                *
ERROR at line 4:
ORA-06550: line 4, column 65:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 15:
PL/SQL: SQL Statement ignored

SQL> declare
  2    rc sys_refcursor;
  3  begin
  4    open rc for select * from t order by x fetch first 2 rows only for update skip locked ;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
ORA-06512: at line 4

или, возможно, попробуйте вернуться к опции ROWNUM

SQL> declare
  2    rc sys_refcursor;
  3  begin
  4    open rc for select * from ( select * from t order by x ) where rownum <= 10 for update skip locked;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
ORA-06512: at line 4

И не получишь никакой радости. Таким образом, вам нужно самостоятельно контролировать выборку n строк. Таким образом, вы можете написать что-то вроде:

SQL> declare
  2    rc sys_refcursor;
  3    res1 sys.odcinumberlist := sys.odcinumberlist();
  4  begin
  5    open rc for select * from t order by x for update skip locked;
  6    fetch rc bulk collect into res1 limit 10;
  7  end;
  8  /

PL/SQL procedure successfully completed.

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