Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Даниил
Member

Откуда: Челябинск
Сообщений: 1024
FB 2.5
Есть таблица, назначение которой - хранить очередь заданий, с такой структурой:
CREATE TABLE TASKS (
ID INTEGER NOT NULL,
DATESTART TIMESTAMP,
ISACTIVE SMALLINT NOT NULL,
ISDONE SMALLINT NOT NULL,
<еще несколько полей>
);
Здесь:
DATESTART - дата-время начала выполнения задачи
ISACTIVE - флаг 0/1 "задача взята в работу"
ISDONE - флаг 0/1 "задача выполнена".


Что делает приложение по обработке таких заданий:
1) Открывается читающая транзакция, запрашивается 1 самая старая не взятая еще в работу задача:
    SELECT 
FIRST 1
ID, <еще несколько полей>
FROM TASKS
WHERE (ISACTIVE = 0) AND (DATESTART <= CURRENT_TIMESTAMP)
ORDER BY DATESTART


2) Закрывается читающая транзакция и тут же открывается пищущая, в которой взятая в работу задача (с полученным на вышеописанном шаге ID) помечается как взятая в работу:
    UPDATE TASKS
SET ISACTIVE = 1
WHERE ID = :ID
после чего пишущая транзакция завершается. Далее задача обрабатывается сколько надо времени и в завершении аналогичным образом помечается как завершенная.

Все элементарно, логично и когда эти задания обрабатывает 1 или 2-3 программы-клиентов - то все работает нормально. Однако 2-3 программы-клиента не успевают обрабатывать весь поток заданий в необходимое время, поэтому экспериментальным путем было получено, что таких программ необходимо иметь около 12-16.
В таком случает все тоже работает бОльшую часть времени нормально, но встречаются случаи, когда из нескольких десятков заданий, какое-то одно могут взять одновременно 2 клиента. Т.е. может так совпасть, что за те несколько миллисекунд между 1 и 2 действием (взяли ID задачи, пометили ее) может отработать шаг "взяли ID задачи" на другом клиенте и тогда одна и та же задача выполнится дважды - а это проблема.

Если эти 2 действия (SELECT+UPDATE одной и той же записи) оформить в виде одной ХП, то возникает lock-конфликт, хотя может быть я не разобрался с параметрами транзакции, в которой надо запускать такую ХП.

Подскажите, как лучше реализовать подобную задачу?
28 мар 19, 12:38    [21845981]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Мимопроходящий
Member

Откуда: бурятский тундрюк, эсквайр
Сообщений: 30037

SELECT FOR UPDATE WITH LOCK

если обломился, знач кто-то уже успел раньше.

Posted via ActualForum NNTP Server 1.5

28 мар 19, 12:44    [21845998]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9634
Даниил,

во-первых дата-время для определения самой старой задачи плохой вариант. У тебя же ID автоинкремент вот и ориентируйся по нём.
во-вторых научись обрабатывать конфликты обновления у себя в приложении
28 мар 19, 12:44    [21845999]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Шавлюк Евгений
Member

Откуда: Одесса
Сообщений: 475
Даниил,

читать про SELECT WITH LOCK
28 мар 19, 12:46    [21846002]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28015
Даниил,

можно по всякому.
Начать с того, что lock conflict возникает при апдейте уже модифицируемой записи, поэтому комбинация select + update в процедуре ну никак не может такое вызывать, если такую процедуру запустить в монопольном режиме.
Масса таких процедур одновременно будет вызывать те же самые lock conflict, что и просто масса update.

Один из вариантов - в транзакции read committed rec_version nowait читать и делать update, если update конфликтует, перечитать еще раз, вероятно запись уже взята в работу и ее апдейтить бессмысленно.
Собственно, у всей задачи приоритет один - успевает только первый апдейт, остальные получая lock conflict НЕ должны пытаться обработать эту запись.

Другой вариант - выстроить транзакции в очередь. Стартовать транзакцию consistency wait с резервированием tasks в protected режиме, в транзакции select, update, commit.

Можно было бы еще замутить read committed no_rec_version, но тут, возможно, придется "долбить" перечитыванием, если первая же запись уже модифицируется.

Если читать поп. литературу, то искать надо "очереди в рсубд".
28 мар 19, 12:53    [21846021]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
hvlad
Member

Откуда:
Сообщений: 10405
UPDATE RETURNING и никаких SELECT'ов
Ну и обрабатывать конфликты обновления, это не сложно
28 мар 19, 12:56    [21846027]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Даниил
Member

Откуда: Челябинск
Сообщений: 1024
Понял, спасибо.
Конфликты обновления будут обрабатываются в дальнейшем в рабочей версии, но в тестовом режиме хотел сначала разобраться с причиной их возникновения. Думал, что можно сделать вообще без конфликтов.

Почитаю и, возможно, попробую оба варианта UPDATE RETURNING и SELECT FOR UPDATE WITH LOCK.
28 мар 19, 13:36    [21846064]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 47677

hvlad
Ну и обрабатывать конфликты обновления, это не сложно

Обрабатывать - не сложно. Сложно придумать как именно обрабатывать. Раз очередь, значит
надо бы пропускать заблокированные записи, но UPDATE RETURNINIG неспособна пропускать их
самостоятельно и неспособна вернуть данные, необходимые для выборки "следующей" записи.

PS: В конечном итоге афтар, надеюсь, таки придёт к единственному диспетчеру заданий вместо
10-12, ибо "другой альтернативы - нет".

Posted via ActualForum NNTP Server 1.5

28 мар 19, 13:55    [21846082]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Даниил
Member

Откуда: Челябинск
Сообщений: 1024
Dimitry Sibiryakov
hvlad
Ну и обрабатывать конфликты обновления, это не сложно

Обрабатывать - не сложно. Сложно придумать как именно обрабатывать. Раз очередь, значит
надо бы пропускать заблокированные записи, но UPDATE RETURNINIG неспособна пропускать их
самостоятельно и неспособна вернуть данные, необходимые для выборки "следующей" записи.

UPDATE RETURNINIG будет заниматься обработкой только 1 записи.
"Следующая" запись уже будет обрабатываться в другой транзакции. Все возникающие lock conflict при этом - просто игнорируем (понимая, что какой-то другой обработчик уже работает с этой записью). Я пока так логику понял.

Dimitry Sibiryakov
PS: В конечном итоге афтар, надеюсь, таки придёт к единственному диспетчеру заданий вместо
10-12, ибо "другой альтернативы - нет".
Пока придумывается для единственного диспетчера - это раздача заданий обработчикам на этапе их создания (триггер на INSERT, либо каким-то приложением-клиентом). После чего обработчики берут из очереди только "свои" задания и не конфликтуют друг с другом.
28 мар 19, 14:08    [21846099]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 47677

Даниил
"Следующая" запись уже будет обрабатываться в другой транзакции. Все возникающие lock
conflict при этом - просто игнорируем (понимая, что какой-то другой обработчик уже
работает с этой записью). Я пока так логику понял.

Не будет оно так работать.

Лучшее, на что ты можешь рассчитывать: читать обычным select, каждую прочитанную запись
пробовать update и так до тех пор пока записи не кончатся или update не будет успешным.
Достаточно одной транзакции. Работать будет если последовательность обработки заданий не
важна.

Posted via ActualForum NNTP Server 1.5

28 мар 19, 14:15    [21846107]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28015
Даниил,

select for update with lock работать не будет, потому что он будет блокировать сразу несколько выбираемых записей, и вся система встанет крабом.
28 мар 19, 14:40    [21846144]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Мимопроходящий
Member

Откуда: бурятский тундрюк, эсквайр
Сообщений: 30037

28.03.2019 14:40, kdv пишет:
> select for update with lock работать не будет, потому что он будет блокировать сразу несколько выбираемых записей, и вся система встанет крабом.

select for update with lock всегда выбирает по одной.

Posted via ActualForum NNTP Server 1.5

28 мар 19, 15:48    [21846254]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
hvlad
Member

Откуда:
Сообщений: 10405
Dimitry Sibiryakov
hvlad
Ну и обрабатывать конфликты обновления, это не сложно

Обрабатывать - не сложно. Сложно придумать как именно обрабатывать
Откат + повтор, классика.
Какие проблемы ?
Если нагрузка по самое нехочу (100500 рабочих одновремено выгребают эту очередь), то можно добавить рандомный таймаут между попытками.
Но для такой нагрузки нужны другие методы, а не общая очередь.

Dimitry Sibiryakov
пропускать заблокированные записи
С этим к другим, не к нам
28 мар 19, 16:02    [21846272]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
vvvait
Member

Откуда:
Сообщений: 80
можно использовать insert первичные ключи внетранзакционны. можно завести таблицу ACTIVE_TASK(ID), второй при попытке вставки обломается
28 мар 19, 17:48    [21846441]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28015
Мимопроходящий
select for update with lock всегда выбирает по одной.

у него запрос select first 1, тогда вроде ок. Хотя, если order by будет PLAN SORT, то что-то я не уверен, что не заблокируются все записи, слитые в sort. При sort записи-то выбираются не из базы.
28 мар 19, 18:54    [21846528]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
Мимопроходящий
Member

Откуда: бурятский тундрюк, эсквайр
Сообщений: 30037

не.
он блокирует только то, что выдал.

Posted via ActualForum NNTP Server 1.5

29 мар 19, 12:00    [21846935]     Ответить | Цитировать Сообщить модератору
 Re: SELECT и UPDATE одной записи таблицы в одной транзакции  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28015
Мимопроходящий,

уговорил. Вообще я напомню, что select first 1 ... order by non_indexed_field приводит к PLAN SORT, и сортирует ВЕСЬ набор записей, и только потом выдает одну запись (в отличие от plan order).
Поскольку все столбцы у запроса с plan sort уже в файле сортировки, сервер в базу за записями не лезет.
Отсюда я сделал предположение, что у такого запроса с with lock будет блокировка по всем записям, независимо от того, сколько записей выдано по FIRST.

Оказалось, что я неправ. Проверил так:

1. выдаем с одного клиента запрос
select first 1 * from employee
order by first_name
for update with lock


2. со второго клиента выдаем то же самое, получаем lock conflict on no wait transaction.
Тут понятно, борьба за первую одинаковую запись.

3. Но, если со второго клиента выдаем
select first 1 * from employee
where emp_no = 4
order by first_name
for update with lock


то есть, НЕ ТУ запись, которую выдал первый запрос.
Ошибки нет, всё норм.
29 мар 19, 16:55    [21847362]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить