Запрос UPDATE иногда зависает
Я использую SQL-запрос для воздействия на 120 000 строк. Иногда работает корректно (3-5 секунд), но часто зависает.
Б.: Постгрес. Пробовал образ докера Postgres 16, на БД сервера. Пробовал с консоли запросов, пробовал из кода Java - результат тот же. Иногда работает, иногда зависает.
update
renamed_account.renamed_account
set
level=0,
chain_id=gen_random_uuid()
where
old_number not in (
select distinct new_number from renamed_account.renamed_account
);
Определение таблицы:
CREATE TABLE IF NOT EXISTS renamed_account.renamed_account
(
ID bigserial PRIMARY key,
OLD_NUMBER varchar(40),
NEW_NUMBER varchar(40),
CHAIN_ID varchar(36),
LEVEL integer
);
CREATE INDEX old_number_idx ON renamed_account.renamed_account (OLD_NUMBER);
CREATE INDEX new_number_idx ON renamed_account.renamed_account (NEW_NUMBER);
Я вижу ваш chain_id = gen_random_uuid() <-- Это может быть частью проблемы, если chain_id является индексированным столбцом (или даже PK?), потому что вставка новых случайных значений в индекс обычно происходит медленно.
Добавьте в текст вопроса определение таблицы renamed_account.
Оффтоп: зачем VARCHAR, если у вас есть UUID? Я бы использовал UUID для UUID.
Ответы 1
NOT IN с подзапросом — классический антишаблон. Хуже того, ни new_number, ни old_number не определены NOT NULL. Таким образом, запрос приводит к сомнительной логике.
Вместо этого используйте NOT EXISTS, который работает быстрее и не прерывается, если подзапрос возвращает нулевое значение.
UPDATE renamed_account.renamed_account r
SET level = 0
, chain_id = gen_random_uuid ()
WHERE NOT EXISTS (
SELECT FROM renamed_account.renamed_account r1
WHERE r1.new_number = r.old_number
);
Видеть:
- Выберите строки, которых нет в другой таблице
- https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN
Однако ваш запрос все равно не должен «зависать». Вероятно, у вас есть проблемы с параллелизмом помимо медленного/неправильного запроса. Пока ваш запрос «застрял», изучите вывод:
SELECT * FROM pg_stat_activity;
Столбец wait_event сообщает вам, чего ждет «зависший» запрос. Примите соответствующие меры.
Для нескольких одновременных транзакций, записывающих в таблицу одновременно, примите политику обновления строк в согласованном, детерминированном порядке внутри одной транзакции — насколько это возможно. Дескать, заказывайте по ПК id.UPDATE не имеет ORDER BY. Обходной путь — заблокировать строки с помощью SELECT ... FOR UPDATE в CTE после ORDER BY id.
WITH sel AS (
SELECT id
FROM renamed_account.renamed_account r
WHERE NOT EXISTS (
SELECT FROM renamed_account.renamed_account r1
WHERE r1.new_number = r.old_number
)
ORDER BY id
FOR UPDATE
)
UPDATE renamed_account.renamed_account r
SET level = 0
, chain_id = gen_random_uuid ()
FROM sel
WHERE sel.id = r.id
);
Связанный:
Тем не менее, вы должны увидеть тупик. Еще один распространенный случай — это сеанс, который «простаивает в транзакции» или длительная транзакция после блокировки строк, которые вы пытаетесь обновить. Пока этот бездействующий сеанс просто сидит и не фиксируется и не откатывается, ваш запрос должен ждать. Также может быть поставлен диагноз pg_stat_activity. Посмотрите на колонку state. Любое упоминание о «простае транзакции» проблематично. Если такая транзакция блокирует ваш UPDATE, она фактически «застряла». Видеть:
Я сомневаюсь, что Postgres действительно «зависает» - какую трассировку вы выполняли?