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

Это фантомное чтение: при уровне READ COMMITTED каждый оператор обычно видит отдельный согласованный снимок данных. Поэтому второй SELECT может увидеть строку, добавленную другой транзакцией после первого SELECT.

Конкретное поведение зависит от СУБД и её реализации изоляции, но полагаться на одинаковый набор строк внутри транзакции при READ COMMITTED нельзя.

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

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

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

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

В примере сеанс A дважды выполняет один логический запрос. Между этими операторами сеанс B добавляет подходящую под условие строку и фиксирует транзакцию.

Новая строка является «фантомом»: она не существовала в результате первого запроса, но появилась во втором. Если приложение рассчитывает количество строк, формирует пакет на обработку или проверяет отсутствие подходящих записей, это может привести к неверному решению.

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

При READ COMMITTED снимок данных обычно формируется на уровне отдельного оператора. Первый SELECT видит состояние базы на момент своего начала, а второй — уже более новое зафиксированное состояние. Незакоммиченные изменения при этом не читаются.

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

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

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id FROM orders WHERE status = 'new';
-- Параллельная вставка уже не должна менять снимок этой транзакции
SELECT id FROM orders WHERE status = 'new';
COMMIT;

Выбор уровня нельзя делать только по названию аномалии. READ COMMITTED обычно обеспечивает лучшую конкурентность, но требует, чтобы бизнес-логика не строилась на неизменности результата повторного чтения. REPEATABLE READ даёт более стабильное наблюдение, но может увеличить количество конфликтов и не всегда защищает от всех сценариев так, как ожидает команда. SERIALIZABLE даёт наиболее сильную гарантию, но требует обработки откатов, повторов и возможного снижения производительности.

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

В сервисе массовой обработки заказов транзакция сначала выбирала все заказы со статусом new, а затем создавала для них задания. При READ COMMITTED между чтением и повторной проверкой появлялись новые заказы, из-за чего число заданий не совпадало с первоначальным набором.

Рассматривались три варианта. Повторять SELECT на READ COMMITTED было просто и быстро, но результат оставался нестабильным. Переключить всю транзакцию на SERIALIZABLE давало строгую гарантию, однако при высокой нагрузке приводило к частым конфликтам и повторам.

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

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

Фантомное чтение — это то же самое, что неповторяемое чтение?

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

Достаточно ли REPEATABLE READ, если транзакция проверяет, что подходящих заказов нет, а затем вставляет новый?

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

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

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