Краткий ответ

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

Исторический контекст

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

Между проверкой и фиксацией результата другая транзакция могла бы удалить родителя. Поэтому СУБД должна не только проверить условие, но и согласовать конфликтующие операции с помощью блокировок, версий данных или сериализации.

Постановка проблемы

Рассмотрим заказ и его позиции. Транзакция добавляет позицию и убеждается, что заказ существует, одновременно другая транзакция удаляет этот заказ.

Если разрешить обе операции без координации, после фиксации появится позиция, ссылающаяся на отсутствующий заказ, либо удаление родителя будет выполнено несмотря на существующую дочернюю запись. Это нарушит ссылочную целостность.

Подробное решение

При вставке дочерней строки СУБД проверяет наличие родительского ключа. Чтобы результат проверки оставался действительным до подходящего момента, реализация обычно устанавливает на родительскую строку блокировку, совместимую с другими проверками внешнего ключа, но несовместимую с её удалением или изменением ключа.

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

Минимальная схема конфликта выглядит так:

CREATE TABLE customers (
    id integer PRIMARY KEY
);
CREATE TABLE orders (
    id integer PRIMARY KEY,
    customer_id integer REFERENCES customers(id)
);

-- Транзакция 1: добавляет заказ для клиента
BEGIN;
INSERT INTO orders VALUES (10, 1);

-- Транзакция 2: пытается удалить того же клиента
BEGIN;
DELETE FROM customers WHERE id = 1;

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

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

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

Ситуация из практики

В интернет-магазине удаление клиента периодически зависало на несколько секунд во время оформления заказа. Оказалось, что оформление создавало заказ, а фоновая задача одновременно пыталась удалить клиента.

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

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

Что кандидаты часто упускают

Всегда ли вставка дочерней строки блокирует родителя до COMMIT?

Нет, конкретная блокировка зависит от СУБД и типа операции. Некоторые реализации используют кратковременную блокировку для проверки существования родителя, другие удерживают защиту дольше; MVCC также влияет на то, какую версию строки видит транзакция. Надёжный ответ должен отделять общий принцип защиты ссылочной целостности от конкретных режимов блокировок.

Почему индекс внешнего ключа влияет не только на скорость, но и на конкуренцию?

При удалении родителя СУБД должна найти все дочерние строки с соответствующим ключом. Индекс позволяет проверить узкий диапазон, а без индекса возможны полное сканирование таблицы и более длительное удержание блокировок. Поэтому отсутствие индекса может превращать локальный конфликт в заметное влияние на другие операции.

Может ли внешняя транзакция завершиться успешно, если родитель удаляется одновременно с добавлением дочерней строки?

Да, но результат определяется порядком сериализации и правилами конкретной СУБД. Обычно одна операция дождётся другой, после чего вставка может получить ошибку отсутствующего родителя, удаление — ошибку из-за существующей дочерней ссылки, либо одна из транзакций будет отклонена при конфликте. Нельзя рассчитывать на заранее выбранный результат без знания уровня изоляции и реализации ограничений.