ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / SELECT и UPDATE одной записи таблицы в одной транзакции
17 сообщений из 17, страница 1 из 1
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792871
Даниил
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Даниил
Участник
FB 2.5
Есть таблица, назначение которой - хранить очередь заданий, с такой структурой:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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 самая старая не взятая еще в работу задача:
Код: plaintext
1.
2.
3.
4.
5.
    SELECT 
    FIRST 1 
        ID,  <еще несколько полей>
    FROM TASKS
    WHERE (ISACTIVE = 0) AND (DATESTART <= CURRENT_TIMESTAMP)
    ORDER BY DATESTART


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

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

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

Подскажите, как лучше реализовать подобную задачу?
...
Рейтинг: 0 / 0
28.03.2019, 12:38
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792884
Мимопроходящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мимопроходящий
Участник
SELECT FOR UPDATE WITH LOCK

если обломился, знач кто-то уже успел раньше.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
28.03.2019, 12:44
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792885
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Даниил,

во-первых дата-время для определения самой старой задачи плохой вариант. У тебя же ID автоинкремент вот и ориентируйся по нём.
во-вторых научись обрабатывать конфликты обновления у себя в приложении
...
Рейтинг: 0 / 0
28.03.2019, 12:44
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792888
Шавлюк Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шавлюк Евгений
Участник
Даниил,

читать про SELECT WITH LOCK
...
Рейтинг: 0 / 0
28.03.2019, 12:46
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792899
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Даниил,

можно по всякому.
Начать с того, что 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, но тут, возможно, придется "долбить" перечитыванием, если первая же запись уже модифицируется.

Если читать поп. литературу, то искать надо "очереди в рсубд".
...
Рейтинг: 0 / 0
28.03.2019, 12:53
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792905
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
UPDATE RETURNING и никаких SELECT'ов
Ну и обрабатывать конфликты обновления, это не сложно
...
Рейтинг: 0 / 0
28.03.2019, 12:56
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792923
Даниил
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Даниил
Участник
Понял, спасибо.
Конфликты обновления будут обрабатываются в дальнейшем в рабочей версии, но в тестовом режиме хотел сначала разобраться с причиной их возникновения. Думал, что можно сделать вообще без конфликтов.

Почитаю и, возможно, попробую оба варианта UPDATE RETURNING и SELECT FOR UPDATE WITH LOCK.
...
Рейтинг: 0 / 0
28.03.2019, 13:36
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792930
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
hvladНу и обрабатывать конфликты обновления, это не сложно

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

PS: В конечном итоге афтар, надеюсь, таки придёт к единственному диспетчеру заданий вместо
10-12, ибо "другой альтернативы - нет".
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
28.03.2019, 13:55
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792939
Даниил
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Даниил
Участник
Dimitry SibiryakovhvladНу и обрабатывать конфликты обновления, это не сложно

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

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

Dimitry SibiryakovPS: В конечном итоге афтар, надеюсь, таки придёт к единственному диспетчеру заданий вместо
10-12, ибо "другой альтернативы - нет".
Пока придумывается для единственного диспетчера - это раздача заданий обработчикам на этапе их создания (триггер на INSERT, либо каким-то приложением-клиентом). После чего обработчики берут из очереди только "свои" задания и не конфликтуют друг с другом.
...
Рейтинг: 0 / 0
28.03.2019, 14:08
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792943
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Даниил"Следующая" запись уже будет обрабатываться в другой транзакции. Все возникающие lock
conflict при этом - просто игнорируем (понимая, что какой-то другой обработчик уже
работает с этой записью). Я пока так логику понял.

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

Лучшее, на что ты можешь рассчитывать: читать обычным select, каждую прочитанную запись
пробовать update и так до тех пор пока записи не кончатся или update не будет успешным.
Достаточно одной транзакции. Работать будет если последовательность обработки заданий не
важна.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
28.03.2019, 14:15
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39792974
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Даниил,

select for update with lock работать не будет, потому что он будет блокировать сразу несколько выбираемых записей, и вся система встанет крабом.
...
Рейтинг: 0 / 0
28.03.2019, 14:40
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793043
Мимопроходящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мимопроходящий
Участник
28.03.2019 14:40, kdv пишет:
> select for update with lock работать не будет, потому что он будет блокировать сразу несколько выбираемых записей, и вся система встанет крабом.

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

Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
28.03.2019, 15:48
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793060
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
Dimitry SibiryakovhvladНу и обрабатывать конфликты обновления, это не сложно

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

Dimitry Sibiryakovпропускать заблокированные записиС этим к другим, не к нам
...
Рейтинг: 0 / 0
28.03.2019, 16:02
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793200
vvvait
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vvvait
Гость
можно использовать insert первичные ключи внетранзакционны. можно завести таблицу ACTIVE_TASK(ID), второй при попытке вставки обломается
...
Рейтинг: 0 / 0
28.03.2019, 17:48
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793268
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Мимопроходящийselect for update with lock всегда выбирает по одной.
у него запрос select first 1, тогда вроде ок. Хотя, если order by будет PLAN SORT, то что-то я не уверен, что не заблокируются все записи, слитые в sort. При sort записи-то выбираются не из базы.
...
Рейтинг: 0 / 0
28.03.2019, 18:54
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793507
Мимопроходящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мимопроходящий
Участник
не.
он блокирует только то, что выдал.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
29.03.2019, 12:00
| Ответить | Цитировать | Написать  
SELECT и UPDATE одной записи таблицы в одной транзакции
    #39793767
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Мимопроходящий,

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

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

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



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

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



то есть, НЕ ТУ запись, которую выдал первый запрос.
Ошибки нет, всё норм.
...
Рейтинг: 0 / 0
29.03.2019, 16:55
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / SELECT и UPDATE одной записи таблицы в одной транзакции
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=39792888&tid=1560767]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 96ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 15ms
total:  208ms
созд. / загр.: 208ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]