Ребята, подскажите пожалуйста где изучить вопрос или может кто то объяснит саму механику СУБД в момент создания FOREIGN KEY.


То есть у меня ситуация такая, Есть три сервера, один боевой, к которому ходят запросы, и два тестовых.

Одна и та же таблица1, 15тыс записей на всех серверах. Добавил новую таблица2 на тестовых серверах, добавил в таблица1 новую колонку. Запустил создание FOREIGN KEY с новой колонки на Таблица2. На тестовых серверах операция выполнялась 6секунд, на боевом операция встала на 20минут. Новая колонка на момент создания внешнего ключа содержит только null значения.


Что поэтапно происходит при создании FOREIGN KEY и что поэтапно происходит при создании FOREIGN KEYна таблицу, к которой при этом периодически выполняются селекты.

Заранее спасибо.

Ну а по-этапно что тут. База блокирует таблицу, чтобы проверить, что накладываемое ограничение при текущих данных исполняется, поэтому и блокирует и исходную таблицу и "поочередно" строки в той таблице, на которую ссылаемся. Можно указать NOT VALID, но потом все равно требуется INVALIDATE.
Другие детали и подробности читайте в ALTER TABLE или CREATE TABLE - в документации все расписано.

На примере не совсем понятно
в
Предположим миллион записей с null в колонке, на которую вешается fk. Получается СУБД будет фулсканом перебирать все строки таблицы поочередно и все, без сопоставления? Вряд ли он будет по null пытаться искать ключевое значение. И вся работа по идее сводится к селекту всей таблицы?

Спасибо за ответ.

Можете еще пояснить на пальцах?
К СУБД идут в основном не сильно грузные быстрые запросы. Получается они тоже вешают лок на время своей работы. Тут я навешиваю fk, он ждет пока таблицы разлочатся, вешает свой лок и начинает выполнение. Не очень понимаю, тогда логически разница между сервером без запросов и сервером, куда идут запросы, должна быть только во времени ожидания выполнения этих мелких запросов, они явно не 20-30минут отрабатывают.
А по факту вроде как получается, что fk ждет разлочивания, запускается и вешает лок на 30минут под свои нужды. Хотя на свободном сервере ему хватало нескольких минут. Или fk не лочит полностью таблицу, а позволяет выполняться параллельным процессам?

Спасибо за ответ. А с чем может потенциально быть связано большое время ожидания закрытия транзакции? Запрос выбрал свои данные, почему он дальше долго ожидает завершения транзакции?