"Исполнителей много, данных мало" , теоретическая задачка

Здравствуйте!

Вопрос по сути - сферический конь в вакууме, но проблему "конкуренции" худо-бедно надеюсь отразит.

Исходные данные:

1) БД - PostgreSQL, MySQL, SQLite (для многопоточного приложения), опционально MS SQL, SyBASE
2) Есть таблица "A" формата (Id:SERIAL, Data: VARCHAR)
3) Есть "поставщик" данных в таблицу "А", он только добавляет записи
4) Есть "исполнители" - это группа программ на разных компах, либо многопоточное(ые) приложение(я), их задача обработать первую доступную запись

Обработка "первой доступной записи":

* постараться захватить запись - иными словами не дать ее обработать другим "исполнителям"
* тут без разницы, пусть - перевести все символы поля Data в строчные
* удалить запись из таблицы "A"
* вставить обработанное в таблицу "Б"

Как это возможно решить в знакомых вам движках БД?

ЗЫ: Формулировка темы слегка некорректна. Более точно - количество "исполнителей" и их производительность - в состоянии обеспечить обработку поступающей очереди с гарантией, что очередь не будет монотонно, а, тем паче, строго монотонно расти.

Цитата JoeUser @ 15.06.16, 18:49

Как это возможно решить в знакомых вам движках БД?

Сперва требуется уточнить:
1) Структура таблицы - догма? или допускается одноразовая модификация?
2) Удаление из А только по завершении обработки - догма? или можно удалять сразу после удачного считывания?
3) Если клиент после удаления из А, но до записи в Б, сдохнет, и запись прогадится в никуда - это фиг бы с ею?

PS. Любой из озвученных движков справится с такой задачей "одной левой".

Цитата Akina @ 15.06.16, 19:41

Сперва требуется уточнить:
1) Структура таблицы - догма? или допускается одноразовая модификация?

Ну давай от простого к сложному. Структура таблицы - догма. Модификация структуры таблицы в рантайме не допускается.
"Поставщик" пишет во всю дурь (только и исключительно инсертами). "Исполнители" опустошают таблицу. В данном контексте не важно как, 1-ю или n-ю запись пытаются "забрать", да хоть рэндомом, не важно, важно как быстрее обработать.

Цитата Akina @ 15.06.16, 19:41

2) Удаление из А только по завершении обработки - догма? или можно удалять сразу после удачного считывания?

Считывание-обработка-вставка-удаление - это типа транзакции. Или все или ничего.

Цитата Akina @ 15.06.16, 19:41

3) Если клиент после удаления из А, но до записи в Б, сдохнет, и запись прогадится в никуда - это фиг бы с ею?

Считывание-обработка-вставка-удаление - это типа транзакции. Или все или ничего.

Цитата Akina @ 15.06.16, 19:41

PS. Любой из озвученных движков справится с такой задачей "одной левой"

Ну ... ээээ ... покажи плс свой алгоритм и коды запросов на любимом движке плс.

Цитата JoeUser @ 15.06.16, 18:49

задача обработать первую доступную запись

Одно приложение тупо инсёртит, ещё 17 законнекчены и тупо пытаются понять - что такое первая доступная запись?

Цитата JoeUser @ 15.06.16, 18:49

постараться захватить запись

Не знаю как в SQLite, но в остальных на уровне строки есть write lock.

Цитата JoeUser @ 15.06.16, 20:02

Структура таблицы - догма. Модификация структуры таблицы в рантайме не допускается.

Ты невнимателен. Я спросил об ОДНОРАЗОВОЙ модификации. То есть о добавлении в структуру одного поля.
Кстати, если разработчик ПО не клинический идиот, и не лепил везде, где можно, в запросах звёздочки и не хардкодил статически обращение к полям по номеру, это не должно никак влиять на работоспособность приложений, использующих эту таблицу.
Ладно, допустим, что он всё же идиот, и модификация невозможна в принципе.

Цитата JoeUser @ 15.06.16, 20:02

Считывание-обработка-вставка-удаление - это типа транзакции. Или все или ничего.

Таки вставка ДО удаления, а не после...

Цитата JoeUser @ 15.06.16, 20:02

покажи плс свой алгоритм и коды запросов на любимом движке плс.

Например, на MySQL:

-- SET AUTOCOMMIT = 0;
START TRANSACTION;
SELECT @ID := `ID`, @Data := `Data` FROM `A` LIMIT 1 FOR UPDATE;
-- perform calculations
-- insert data into table `B`
DELETE FROM `A` WHERE `ID` := @ID;
COMMIT;
-- SET AUTOCOMMIT = 1;

Добавлено 16.06.16, 06:06

Цитата Bas @ 16.06.16, 05:36

в остальных на уровне строки есть write lock.

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

Цитата Akina @ 16.06.16, 05:53

Например, на MySQL:

Что сделает твой код, если в момент обращения в таблице А - количество записей нуль?

Добавлено 16.06.16, 06:18

Цитата #SI# @ 16.06.16, 05:16

и тупо пытаются понять - что такое первая доступная запись?

Кстати, да. Если все ж работаем с блокировками записей - как найти первую "незаблокированную" запись запросом?

Цитата JoeUser @ 16.06.16, 06:09

Что сделает твой код, если в момент обращения в таблице А - количество записей нуль?

Ну так в процедуре должен быть определён хэндлер для этого события:

DECLARE EXIT HANDLER FOR NOT FOUND

Ну или просто анализировать значение переменных в блоке обработки.

Сообщение отредактировано: Akina - 16.06.16, 06:32

Akina, а код

SELECT @ID := `ID`, @Data := `Data` FROM `A` LIMIT 1 FOR UPDATE;

какую именно запись захватит? первую попавшуюся незаблокированную? или просто первую?

Цитата JoeUser @ 17.06.16, 09:15

какую именно запись захватит? первую попавшуюся незаблокированную? или просто первую?

Хм. Хороший вопрос. Счас попробую потестировать. Придётся генерить большой объём данных - InnoDB блокирует-то страницу...

Добавлено 17.06.16, 09:34
Да, экспериментально ничего хорошего не получилось. Пробует захватить первую попавшуюся, и если она заблокирована, то ждёт завершения транзакции с ней, после чего выбирает первую из оставшихся после удаления.
Счас пороюсь по документации - есть ли возможность выбора именно незаблокированной записи... хотя сомневаюсь что-то.

Цитата Akina @ 17.06.16, 09:26

Счас пороюсь по документации - есть ли возможность выбора именно незаблокированной записи... хотя сомневаюсь что-то.

Да, это главный вопрос этой темы.

Добавлено 17.06.16, 09:55
Для PostgreSQL >=9.5 и Oracle >=??? - SKIP LOCKED

Добавлено 17.06.16, 10:03
SQLite 3 - не умеет блокировать записи, только таблицу. Может в единицу времени иметь множество "читателей" и только одного "писателя".

Добавлено 17.06.16, 10:11
SQL Server и Sybase - NOWAIT SKIP LOCKED

Цитата JoeUser @ 17.06.16, 09:49

это главный вопрос этой темы

Нет, похоже, в MySQL такого инструмента не завезли. Так что подобный механизм придётся реализовывать "на ручной тяге" - лучше всего через создание таблицы блокирования записей. Во всяком случае я такое уже делал, причём достаточно тривиальной структурой (RecordID, HandlerID, ExpireTimestamp). Предусматривались и захват записи для обработки, и возможное не-завершение процесса обработки.

Ясненько. Сенкс за исследование по MySQL

Цитата JoeUser @ 17.06.16, 09:49

Oracle >=???

>=7

Цитата Akina @ 17.06.16, 11:03

Нет, похоже, в MySQL такого инструмента не завезли.

А в мускуле можно не ждать освобождения блокировки, а отвалиться по ошибке сразу, или по тайм ауту в 0.1сек?