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

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

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

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

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

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

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

Медленный запрос часто пытаются исправить созданием индекса или переписыванием SQL. Это не поможет, если запрос почти всё время не выполняет операторы, а ждёт завершения другой транзакции.

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

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

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

При высокой стоимости плана процессор, чтение страниц или операции сортировки активно выполняются длительное время. При блокировке запрос обычно находится в состоянии ожидания: его прогресс не объясняется объёмом вычислений, а продолжительность зависит от того, когда блокирующая транзакция зафиксирует изменения или откатится.

Практическая диагностика должна включать:

сравнение CPU-времени, логических и физических чтений с общим временем ответа;
проверку состояния ожидания и типа ожидаемого ресурса;
поиск блокирующей транзакции и построение цепочки «блокирует — ожидает»;
проверку длительности транзакции, места фиксации и того, не удерживает ли приложение транзакцию во время сетевого обмена.

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

SELECT a.pid, a.wait_event_type, a.wait_event,
       l.relation::regclass, l.mode, l.granted,
       a.query
FROM pg_stat_activity AS a
JOIN pg_locks AS l ON l.pid = a.pid
WHERE a.state <> 'idle';

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

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

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

Сервис обновляет заказ и затем выполняет внешний HTTP-вызов до фиксации транзакции. Параллельный запрос к тому же заказу имеет стабильный план и малое число чтений, но периодически ждёт десятки секунд.

Рассматривались варианты:

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

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

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

1. Может ли быстрый план всё равно долго выполняться из-за блокировки?

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

Нужно отличать время CPU и чтения от elapsed time. Большая разница между ними часто указывает на ожидания, хотя для окончательного вывода необходимо проверить конкретные события ожидания и состояние транзакций.

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

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

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

3. Почему увеличение тайм-аута не является исправлением проблемы?

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

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