Как отметить некоторые nr строк в таблице при одновременном доступе

Наше приложение имеет таблицу под названием cargo_items. Его можно рассматривать как своего рода очередь для последующего обработки этих элементов. Первоначально была одна работа, которая занимала 3000 записей и обрабатывала их один за другим. Позже кто-то решил запустить еще 3 экземпляра одной и той же работы. То, что произошло, совершенно очевидно, многие предметы были обработаны дважды.

Моя задача – правильно работать с этими процессами, если в то же время выполняется множество экземпляров. Решение, на котором я собираюсь сейчас, состоит в том, чтобы отметить 3000 записей в базе данных с помощью job_id, а затем получить все эти объекты и обработать их изолированными от других процессов.

Мой текущий подход для пометки этих строк следующий:

UPDATE cargo_item item SET job_id = 'SOME_UUID', job_ts = now() FROM ( SELECT id FROM cargo_item WHERE state='NEW' AND job_id is null LIMIT 3000 FOR UPDATE ) sub WHERE item.id = sub.id; 

В основном этот подход блокирует 3000 строк для обновления. Я не уверен, что это хороший подход.

В другом потоке я прочитал об использовании консультативных блокировок для этого сценария.

Что вы, ребята, думаете о текущем подходе и используете вместо этого консультативную блокировку?

ОБНОВИТЬ

Как было предложено, я бы адаптировал инструкцию update следующим образом:

 UPDATE cargo_item item SET job_id = 'SOME_UUID', job_ts = now() FROM ( SELECT id FROM cargo_item WHERE state='NEW' AND job_id is null ORDER BY id LIMIT 3000 FOR UPDATE ) sub WHERE item.id = sub.id; 

Thx Erwin и Tometzky за намек. Тем не менее я задаюсь вопросом, является ли способ, которым я пытаюсь решить проблему, хороший? Есть ли разные подходы, о которых вы могли бы подумать?

В соответствующем ответе вы имеете в виду:

  • Postgres UPDATE … LIMIT 1

Цель состоит в том, чтобы заблокировать одну строку за раз. Это отлично работает с или без консультативных блокировок, потому что нет возможности зайти в тупик – если вы не пытаетесь заблокировать больше строк в одной транзакции.

Ваш пример отличается тем, что вы хотите заблокировать 3000 строк за раз . Существует вероятность блокировки, за исключением случаев, когда все параллельные операции записи блокируют строки в одном и том же согласованном порядке. По документации:

Лучшая защита от блокировок, как правило, позволяет избежать их, будучи уверенным, что все приложения, использующие базу данных, получают блокировки на нескольких объектах в последовательном порядке.

Внесите это с помощью ORDER BY в ваш подзапрос.

 UPDATE cargo_item item SET job_id = 'SOME_UUID', job_ts = now() FROM ( SELECT id FROM cargo_item WHERE state='NEW' AND job_id is null ORDER BY id LIMIT 3000 FOR UPDATE ) sub WHERE item.id = sub.id; 

Это безопасно и надежно, если все транзакции приобретают блокировки в том же порядке, и не ожидается ожидаемых одновременных обновлений столбцов заказа. ( Прочтите желтое поле «ПРЕДОСТЕРЕЖЕНИЕ» в конце этой главы в руководстве .) Таким образом, это должно быть безопасным в вашем случае, так как вы не собираетесь обновлять столбец id .

Эффективно только один клиент одновременно может манипулировать строками таким образом. Параллельные транзакции будут пытаться заблокировать одни и те же (заблокированные) строки и дождаться завершения первой транзакции.

Консультативные блокировки полезны, если у вас много или очень длительные параллельные транзакции (похоже, вы не делаете). Всего лишь немногие из них будут более дешевыми, чтобы просто использовать выше запрос и иметь параллельные транзакции, ожидающие своей очереди.

Все в одном UPDATE

Кажется, что одновременный доступ не является проблемой в вашей настройке. Параллелизм – это проблема, созданная вашим текущим решением.

Вместо этого сделайте все в одном UPDATE . Назначьте партии n чисел (3000 в примере) каждому UUID и обновите все сразу. Быстрее.

 UPDATE cargo_item c SET job_id = u.uuid_col , job_ts = now() FROM ( SELECT row_number() OVER () AS rn, uuid_col FROM uuid_tbl WHERE <some_criteria> -- or see below ) u JOIN ( SELECT (row_number() OVER () / 3000) + 1 AS rn, item.id FROM cargo_item WHERE state = 'NEW' AND job_id IS NULL FOR UPDATE -- just to be sure ) c2 USING (rn) WHERE c2.item_id = c.item_id; 

Основные моменты

  • Целочисленное деление усекает. Вы получаете 1 для первых 3000 строк, 2 для следующих 3000 строк. и т.п.

  • Я выбираю строки произвольно, вы можете применить ORDER BY в окне для row_number() для назначения определенных строк.

  • Если у вас нет таблицы UUID для отправки ( uuid_tbl ), используйте выражение VALUES для их поставки. Пример.

  • Вы получаете партии из 3000 рядов. Последняя партия будет меньше 3000, если вы не найдете несколько 3000 для назначения.

У вас будут тупики с таким подходом. Вы можете избежать их, просто используя order by id в подзапросе.

Но это предотвратит параллельное выполнение этих запросов, так как параллельные запросы всегда будут сначала отмечать самый низкий свободный идентификатор и блокировать до тех пор, пока первый клиент не будет зафиксирован. Я не думаю, что это проблема, если вы обрабатываете менее одной партии в секунду.

Вам не нужны консультативные блокировки. Избегайте их, если сможете.

Вам нужны консультативные блокировки.

  SELECT id FROM cargo_item WHERE pg_try_advisory_lock(id) LIMIT 3000 FOR UPDATE 

разместит контрольную блокировку строк, а другие процессы не будут видеть строки, если в том же месте используется одна и та же функция pg_try_advisory_lock (id). Не забудьте разблокировать строки, используя pg_advisory_unlock