Запретить вставку строки, если уже есть 3 строки с таким значением

Question

Имеется такая таблица:

CREATE TABLE sessions (
    id SERIAL,
    user_id INT NOT NULL,
    active bool
);

Мне нужно вставить в таблицу новую строку, но при вставке убедиться, что вместе с моей строкой в таблице не будет больше 3 строк с active=true для одного user_id. Я не единственный кто может в этот момент выполнить запрос, т.е. параллельная запись возможна. Создать индекс не получится, потому что у других пользователей должна быть возможность вставить больше 3 строк. Пока на ум приходит только блокировка таблицы на запись (EXCLUSIVE). Я сторонник того, что блокировку нужно применять только в том случае, когда нет никакого другого выхода, почти всегда находится такой выход. Есть способ лучше блокировки?

Answer 1

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

Начать транзакцию begin isolation level serializable;
Получить текущее количество записей для данного пользователя обычным select ... where user_id=X and active=true
В зависимости от количества принять решение о вставке или возможно о модификации записей
commit
В случае если при commit произошла ошибка серализации выполнить действия заново, с п.1

Внимание: выполнение п 5 обязательно, возможны ложные ошибки сериализации произошедшие не по причине наличия 3х подходящих записей. Например могут возникать ошибки при работе в 2х транзакций с близкими id пользователей, так как будут использованы предикатные блокировки страниц индекса, которые затрагиваются чтением и записью в транзакции. А на одной странице индекса могут располагаться данные по нескольким пользователям.

Второй способ - использовать рекомендательные блокировки с помощью одной из специализированных функций. Порядок действий:

Начать транзакцию
Выполнить например pg_advisory_xact_lock(UserID) для взятия исключительной блокировки по данному пользователю в пределах транзакции.
Получить количество подходящих записей
При необходимости выполнить insert
commit

БЛОГ НА HUSL

Запретить вставку строки, если уже есть 3 строки с таким значением

Ответы (1 шт):