Блокировка всех строк в таблице PostgreSQL

Оба INSERT и UPDATE получают блокировку ROW EXCLUSIVE , поэтому вы не найдете никакой блокировки на уровне таблицы, которая исключает одну, но не другую.

Вы можете заблокировать все существующих строк на изменения с помощью SELECT FOR UPDATE, но это не повлияет на одновременно INSERT записи, так что они все равно будут выбраны и обработаны, независимо от того, что задачи в данный момент выполняются.

Также могут быть проблемы с синхронизацией таблицы entities_for_tasks с entity_tasks, в зависимости от того, как именно вы ее заполняете и какой уровень изоляции вы используете; этот тип паттерна склонен к гоночным условиям на уровне ниже 1112.

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

Итак, оставьте очередь в покое и подумайте, что еще вам нужно для координации выполнения задачи:

1. Блокировка с надписью «задача выполняется»
2. Набор блокировок, которые говорят, что «задача выполняется с сущностью x»

... где задача из block_everything_tasks нуждается в исключительной блокировке для (1), в то время как задачи из entity_tasks могут поделиться блокировкой на (1) друг с другом, но нужна эксклюзивная блокировка на (2).

Наиболее явный способ реализовать это через консультативные блокировки , которые позволяют вам «блокировать» произвольные целые числа, которые содержат некоторое специфическое для приложения значение.

Предполагая, что ни у одного объекта нет идентификатора 0, давайте использовать его для блокировки верхнего уровня «задача выполняется». Затем, после успешного извлечения задачи из очереди, будет выполняться каждая исключительная задача:

SELECT pg_advisory_xact_lock(0);

... и будет выполняться каждая задача для каждого объекта:

SELECT pg_advisory_xact_lock_shared(0);
SELECT pg_advisory_xact_lock(<entity_id of selected task>);

Основная проблема с консультативной блокировкой состоит в том, что каждый пользователь базы данных должен договориться о том, что означают эти целые числа, или они могут в конечном итоге бороться за одну и ту же блокировку в несвязанных целях. Двухпараметрические перегрузки (int,int) функций блокировки позволяют использовать блокировки для конкретного случая использования, но это не сильно помогает, когда ваши идентификаторы равны bigint с.

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

CREATE_TABLE currently_processing (
  entity_id bigint PRIMARY KEY
);

... затем для исключительных задач:

LOCK currently_processing;

... и для задач для отдельных объектов:

INSERT INTO currently_processing VALUES (<entity_id of selected task>);
<run the task>
DELETE FROM currently_processing WHERE entity_id = <entity_id of selected task>;

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