Разберите ситуацию в PostgreSQL: почему оба INSERT временно нарушают внешний ключ, но транзакция может успешно завершиться только после проверки ограничений в COMMIT?
CREATE TABLE a (
id integer PRIMARY KEY,
b_id integer
);
CREATE TABLE b (
id integer PRIMARY KEY,
a_id integer REFERENCES a(id)
DEFERRABLE INITIALLY DEFERRED
);
ALTER TABLE a
ADD CONSTRAINT a_b_fk
FOREIGN KEY (b_id) REFERENCES b(id)
DEFERRABLE INITIALLY DEFERRED;
BEGIN;
INSERT INTO a VALUES (1, 1);
INSERT INTO b VALUES (1, 1);
COMMIT;
Оба внешних ключа объявлены как DEFERRABLE INITIALLY DEFERRED, поэтому PostgreSQL не обязан проверять их после каждого INSERT. Проверка переносится к границе транзакции — обычно к COMMIT; к этому моменту обе строки уже существуют, и ограничения выполняются. Если проверка при фиксации не пройдёт, COMMIT завершится ошибкой, а изменения транзакции не станут видимыми как зафиксированные.
Обычная немедленная проверка ограничений хорошо защищает данные после каждого оператора, но мешает многошаговым изменениям, в которых промежуточное состояние временно неполно. Типичный пример — создание взаимно связанных записей или перестановка значений между строками.
Отложенная проверка разделяет момент выполнения отдельных операторов и момент проверки итогового состояния транзакции. Это позволяет сохранять целостность результата, не требуя, чтобы каждое промежуточное состояние уже удовлетворяло всем ограничениям.
В примере строка a ссылается на строку b, а строка b — на строку a. Первый INSERT создаёт a(1, 1), когда строки b(1) ещё нет. При немедленной проверке внешний ключ нарушился бы сразу, и второй INSERT не выполнился бы.
Если вместо отложенной проверки отключить ограничения или не проверять их вовсе, можно получить зафиксированные «висячие» ссылки. Поэтому важно не просто разрешить временное нарушение, а гарантировать проверку итогового состояния перед фиксацией.
У ограничения есть два независимых свойства. DEFERRABLE разрешает переносить его проверку, а INITIALLY DEFERRED задаёт отложенный режим по умолчанию для каждой новой транзакции. Если ограничение было бы INITIALLY IMMEDIATE, его можно было бы явно отложить командой SET CONSTRAINTS.
Выполнение происходит так:
BEGIN начинает транзакцию.INSERT создаёт временно неполное состояние.INSERT добавляет недостающую строку.COMMIT PostgreSQL проверяет отложенные внешние ключи.Отложенность не означает отключение ограничения. Если удалить одну из строк или изменить ссылку так, чтобы итоговое состояние стало некорректным, ошибка возникнет при COMMIT. Клиент должен обрабатывать ошибку фиксации так же серьёзно, как ошибку обычного оператора.
Перевод ограничения в режим IMMEDIATE обычно вызывает его проверку сразу. Если проверка не проходит, транзакция может перейти в состояние ошибки; дальнейшая работа зависит от СУБД и должна учитывать необходимость отката.
Проверка при фиксации не устраняет конкурентный доступ. СУБД всё равно должна корректно учитывать изменения других транзакций, блокировки и видимость версий. Конкретная схема блокировок и детали проверки внешних ключей зависят от СУБД, поэтому нельзя делать универсальный вывод, что отложенные ограничения полностью не блокируют другие операции.
Сервис импортирует пакет объектов, между которыми есть взаимные ссылки. При немедленных внешних ключах импорт приходится выполнять в специальном порядке либо разбивать на предварительные и последующие обновления.
Вариант с отключением проверок быстрее реализуется, но опасен: ошибка в импорте может оставить некорректные ссылки, а восстановление целостности придётся выполнять отдельно. Вариант с изменением схемы и разрешением NULL уменьшает связанность, но усложняет модель данных и добавляет промежуточные состояния.
Выбранный вариант — отложенные внешние ключи для конкретного набора связанных операций. Импорт выполняется в одной транзакции, ограничения проверяются при COMMIT, а при ошибке вся пачка откатывается. Это сохраняет атомарность и целостность, хотя может увеличить время фиксации и объём работы в конце транзакции.
Вопрос: Чем DEFERRABLE INITIALLY DEFERRED отличается от DEFERRABLE INITIALLY IMMEDIATE?
Ответ: Оба варианта разрешают перевод ограничения между режимами проверки. Разница в начальном режиме: INITIALLY DEFERRED откладывает проверку до конца транзакции автоматически, а INITIALLY IMMEDIATE проверяет ограничение после операторов, пока приложение явно не выполнит SET CONSTRAINTS ... DEFERRED.
Поэтому DEFERRABLE само по себе не означает, что проверка всегда выполняется при COMMIT. Нужно учитывать как возможность отложить ограничение, так и его текущий режим в данной транзакции.
Вопрос: Что произойдёт, если данные так и останутся некорректными к моменту COMMIT?
Ответ: Фиксация завершится ошибкой проверки ограничения. Транзакция не будет успешно зафиксирована, поэтому приложение не должно считать операцию выполненной только потому, что все INSERT прошли без ошибок.
На практике код должен обработать ошибку COMMIT, выполнить откат или завершить ошибочную транзакцию согласно правилам конкретного драйвера, а затем решить, можно ли безопасно повторить всю операцию. Повторять только последний INSERT обычно неправильно: результат зависит от всей последовательности изменений.
Вопрос: Защищает ли отложенная проверка внешнего ключа от конфликтов между конкурентными транзакциями?
Ответ: Нет, она решает другую задачу: определяет момент проверки ограничения внутри транзакции. Конкурентные изменения по-прежнему могут ожидать блокировки, конфликтовать или привести к ошибке фиксации в зависимости от СУБД, уровня изоляции и конкретной схемы доступа.
Кроме того, успешное выполнение промежуточных операторов не гарантирует успешный COMMIT. При проектировании нужно учитывать длительность транзакции, порядок изменения связанных строк и обработку ошибок фиксации, а не воспринимать отложенное ограничение как способ отключить конкурентный контроль.