Избегайте эксклюзивных блокировок доступа к ссылочным таблицам при DROPping в PostgreSQL.

Для тех, кто ищет в Google и пытается понять, почему их таблица удаления (или удаление внешнего ключа или добавление внешнего ключа) застряла на долгое время:

PostgreSQL (я смотрел версии с 9.4 по 13) ограничения внешнего ключа на самом деле реализуются с помощью триггеров на обоих концах внешнего ключа.

Если у вас есть таблица компании (идентификатор в качестве первичного ключа) и таблица bank_account (идентификатор в качестве первичного ключа, company_id в качестве внешнего ключа, указывающего на company.id), то на самом деле в таблице bank_account есть 2 триггера, а также 2 триггера в компании. стол.

Первоначальное создание этих триггеров (при создании внешнего ключа) требует блокировки SHARE ROW EXCLUSIVE для этих таблиц (раньше это была блокировка ACCESS EXCLUSIVE в версии 9.4 и ранее). Эта блокировка не конфликтует с блокировками чтения данных, но будет конфликтовать со всеми другими блокировками, например, простой INSERT/UPDATE/DELETE в таблицу компании.

Для удаления этих триггеров (при удалении внешнего ключа или всей таблицы) требуется блокировка ACCESS EXCLUSIVE для этих таблиц. Этот замок конфликтует с любым другим замком!

Итак, представьте себе сценарий, в котором у вас есть работающая транзакция A, которая сначала выполнила простой SELECT из таблицы компании (заставив ее удерживать блокировку ACCESS SHARE для таблицы компании до тех пор, пока транзакция не будет зафиксирована или не будет отменена), а теперь выполняет какую-то другую работу для 3 минуты. Вы пытаетесь удалить таблицу bank_account в транзакции B. Для этого требуется блокировка ACCESS EXCLUSIVE, которая должна будет подождать, пока сначала не будет снята блокировка ACCESS SHARE. Кроме того, все другие транзакции, которые хотят получить доступ к таблице компаний (просто SELECT или, возможно, INSERT/UPDATE/DELETE), будут поставлены в очередь для ожидания блокировки ACCESS EXCLUSIVE, которая ожидает блокировки ACCESS SHARE.

Длительные транзакции и изменения DDL требуют деликатной обработки.

Я предполагаю, что DDL блокирует все, к чему прикасается, исключительно ради простоты — в любом случае вы не должны запускать DDL с невременными таблицами во время нормальной работы.

Чтобы избежать взаимоблокировки, вы можете использовать рекомендательную блокировку:

start transaction;
select pg_advisory_xact_lock(0);
drop table main;
commit;

Это гарантирует, что только один клиент одновременно запускает DDL с использованием ссылочных таблиц, поэтому не имеет значения, в каком порядке будут получены другие блокировки.

Вы можете избежать блокировки таблицы в течение длительного времени, сначала удалив внешний ключ:

start transaction;
select pg_advisory_xact_lock(0);
alter table main drop constraint fk_main_base;
commit;
start transaction;
drop table main;
commit;

Это по-прежнему потребует исключительной блокировки base, но на гораздо более короткое время.