oracle 12.1
есть таблица-очередь заявок на обслуживание вида





в эту таблицу одни процессы добавляют строки со статусом Новая,
другие процессы (демоны разбора очереди, больше одного, способ реализации - Informatica или Java) должны:
- захватывать одну заявку в статусе Новая по сортировке FIFO
- менять ее статус на Обработка
- производить некую работу
- менять статус на Завершена

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

думаем в сторону select for update (nowait?) - есть ли опыт/пример/подводные камни?

спасибо всем ответившим!

дополнения:

авторС обязательным построчным фетчем курсора унутре "демона" и фиксацией транзакции по факту обновления статуса (row-by-row).


предполагается, что каждый демон разбора очереди а) получает (через select for update) всего одну строку по порядку сортировки FIFO, меняет ей статус на Обработка и комитит, после чего запускает некий внешний обработчик с ИД этой заявки

Затем происходит обработка, и обработчик последним своим писком делает апдейт статуса заявки на Завершена.

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


вижу на других форумах важное замечание:
автор важное отличие для работы с курсорами - курсор с for update залочит все строки попадающие под условие курсора сразу при open, а если добавить skip locked, то только те строки будут залочены, которые были действительно считаны в fetch, что позволяет: ограничить коллличество залоченых строк, или возвращать открытый курсор как результат функции с ещё не залочеными строками.

вопрос: как правильно в нашем случае отобрать и залочить всего одну строку, чтобы такого не происходило?

andrey_anonymous,


как сделать, чтобы первый схватил 1 строку - и остальным тоже досталось по строке?

демон в общем случае - процедура на pl/sql, т.е. никаких лишних прослоек между ним и базой нет

Уважаемые, всем спасибо за глубокую дискуссию!

Задача в итоге шире, чем в первом посте, поясню:

I планируется 2 очереди:
1) очередь заявок на обработку. Заявка - это крупный объект для общения между АС. Эта очередь должна получать новую заявку из внешнего по отношению к данной АС мира, никак не завися от ограниченности ресурсов АС (демон занят и тп).
2) полученная заявка должна захватываться демоном заявок, после чего по метаданным внутри АС производится ее декомпозиция на конкретные работы (работа - многошаговый процесс, в общем случае описанный как набор атомарных SQL в таблицах этой АС, соотношение Заявка - Работы = 1:М)
3) результат декомпозиции - работы - следует сложить во вторую очередь Работ, которую будут разбирать и исполнять демоны второго рода (исполнители работ).

Почему предполагается так:

1) ресурсы (кол-во демонов) ограничено.
2) Кол-во работ (SQL) в каждой заявке разное, время на их исполнение достаточно велико (от минут до часов)
3) !!! в очередь могут добавляться заявки с повышенным приоритетом, и их нужно будет обработать раньше тех, что попали в очередь до нее, но с низким

Т.о. целевой процесс предполагается таким:
Демон заявок:
1) схватил первую заявку из очереди, выяснил, что у нее 5 работ внутри - добавил эти 5 работ в очередь работ
2) схватил следующую заявку ...- и т.д. пока заявки не кончились - встал на ожидание новой строки (кстати посоветуйте как эффективнее. SLEEP?)

здесь вроде как не проблема с балк-операциями (сразу хватать все заявки)

Демон работ (запускается одновременно указанное лимитированное кол-во демонов параллельно):
1) схватил работу, выполнил, отчитался о результате статусом
2) схватил следующую работу .. и т.д.

здесь нельзя балк - работы долгие, выполняются пошагово.

А далее следует большое НО ;)
в угоду скорости внедрения очередь работ отложена на второй этап, Демоны работ эмулируются шелл-файлами линукса и прочей требухой.
Из ограничения "не более Н работ одновременно" получаем более жесткое ограничение "нельзя исполнять более одной заявки вида Х одновременно" и необходимость запускать в параллель демонов очереди по числу видов заявок, каждый из которых не имеет права запустить на исполнение более одной заявки своего вида.
это не дает сделать массовый обработчик, как предлагает DBA.

andrey_anonymous,

я только за встроенную функциональность ;)
но опыта по AQ нет, у вас есть?

кто может сказать, есть ли проблемы с:
1) приоритетами (нам сообщения нужно выбирать по сортировке "сначала по приоритетам asc, потом по дате появления в очереди asc - т.е. если появится сообщение с более высоким приоритетом, оно должно схватиться следующим - минуя более старые неразобранные с низким)?

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

3) с расширением метаданных (как выглядит процесс "добавить в очередь новое поле" - нужно остановить всю очередь (включая тех, кто туда пишет - а это много АС и они не в нашей власти!!!) или можно онлайн - типа redefinition)?

4) как именно лучше выгребать очередь и ПОЧЕМУ:
а) по job по таймеру пинать читателя, внутри которого стоит атомарный dequeuе (проблема сна так решается легко)
б) подписать его (но тут проблема сна + исполнителей будет ограниченное кол-во и нужно этим управлять - как?)

5) можно ли организовать логику dequeuе "ждать Х секунд, если ничего не пришло - выйти из ожидания?"

вопросов много, вот и отложили AQ на вторую итерацию...