Какой инструмент применить чтобы избавиться от блокировки таблиц в БД, если доступ нужен по очереди?
Сейчас есть CRM система, написанная на flask. База данных postgresql с ORM peewee.
В CRM системе есть таблица заявок, которые обзванивают операторы. По нажатию на кнопку мы обращаемся к базе данных и через db.atomic в peewee берем заявки, сортируем их по дате и используем for_update().get() для получения только одной заявки, назначаем ответственного и транзакции заканчивается. Во время транзакции происходит блокировка таблицы и другие пользователи не могут взять себе заявку пока транзакция не завершена, с увеличением количества операторов задержка стала больше. Какими инструментами можно избавиться от данной проблемы? Либо как-то изменить запрос, чтобы не блокировать всю таблицу, но не выдавались одинаковые заявки?
Оптимизируйте запрос, чтобы он выполнялся быстрее: добавьте соответствующие индексы, проверьте что транзакция открывается при самом запросе, а не где-то загодя. Вообще проверьте, что именно тормозит в выполнении транзакции. Почему транзакция блокирует таблицу? Почему всю таблицу? И почему вообще блокирует, какой у вас уровень изолированности транзакций?
С учетом "есть таблица заявок, которые обзванивают операторы", что-либо сложнее делать нет смысла, вряд ли у вас там высокая нагрузка.
Либо как-то изменить запрос, чтобы не блокировать всю таблицу, но не выдавались одинаковые заявки?
Так самого запроса-то и не видать... поди пойми что там происходит под капотом...
Но если по сути... как я понимаю, в таблице есть некое поле "ответственный", в которое надо вместо NULL (или там нуля) положить ID текущего оператора. Но коли так, то никаких предварительных телодвижений и не нужно. Просто
UPDATE заявки
SET оператор = @current_operator
WHERE оператор IS NULL -- только незарезервированные заявки
ORDER BY дата_заявки ASC LIMIT 1
после чего проверить, что ни с кем не произошло пересечения, а заодно и получить идентификатор заявки
SELECT id_заявки
FROM заявки
WHERE оператор = @current_operator
Возможны варианты:
- вернулся один номер - всё нормально, заявка распределена оператору
- вернулось ноль номеров - кто-то перехватил эту заявку, повторяем резервирование
- вернулось более одного номера или сделано более какого-то числа неуспешных попыток резервирования - что-то не в порядке, зовём администратора задачи
Akina, надо еще не забыть: WHERE оператор is null. И разумеется не в рамках транзакции делать.
Блокировки никуда не денутся в этой схеме, но из-за простоты 1 запроса, все должно работать быстро и особых проблем не возникнет.
Вообще, даже если у автора сотни операторов на проекте, это не будет представляться проблемы во время работы, т.к. обращаться за заявками они будут в разное время. Но есть пиковый момент, когда операторы в ожидании и заявки поступают в обзвон. В этот момент все операторы ломятся на сервер за заявками, ктото успевает заблокировать 1 запись под update, остальные блокируются и ждут, update завершается, остальные получают ответ "приходите позже". Разумеется, эти запросы размазаны по времени, но чем больше операторов и чем медленнее СУБД, тем будет больше очередь. Кроме того, когда "вернулось ноль номеров", это может значит, что заявки закончились, тогда врубается пауза чтобы не кошмарить СУБД запросами, по истечении этой паузы все опять ломанутся за заявками, а из-за пауза ожидание увеличивается.
Повторюсь, из-за скорости работы одиночного update, все должно быть норм, но если нет, то можно паузу делать немного рандомной, либо проверять попал оператор на блокировку или действительно закончились заявки и в первом случае не ждать, а снова мучить сервер.
Теоретически, здесь хорошо подходит select for update skip locked - когда мы не ждем освобождения блокировки и уходим ни с чем, а просто берем следующую запись. Но нужно попробовать и убедится, что в конкретной СУБД все работает корректно.
В этот момент все операторы ломятся на сервер за заявками, ктото успевает заблокировать 1 запись под update, остальные блокируются и ждут, update завершается, остальные получают ответ "приходите позже".
На показанной схеме это всё фигня. При правильном индексировании показанный запрос на резервирование должен выполняться менее миллисекунды. Гораздо быстрее, чем сетевой обмен с клиентом при выполнении этого запроса.
Вообще чтобы почувствовать замедление от конкурентных операций на интерактивной операции - это надо ну очень всё сделать через задницу.
Дмитрий, разумнее выгрести одну запись в CTE - там-то будет SELECT, который прекрасно допускает LIMIT. Ну и у меня больше речь идёт о принципе, чем о конкретной реализации.
Средний
