Транзакции и уровни изоляции в mysql: когда возникают фантомы и как их избежать

От /

Фантомные чтения в MySQL возникают, когда в рамках одной логической операции вы повторяете диапазонный запрос, а параллельная транзакция успевает вставить или изменить строки, попадающие в диапазон. Предотвращают их выбором уровня изоляции (вплоть до mysql serializable), корректными блокировками диапазонов и уникальными/проверочными ограничениями, а также проверяемыми сценариями тестирования.

Короткая сводка по транзакциям и изоляции

Транзакции и уровни изоляции в MySQL: когда возникают
  • Фантомы связаны не с "грязными данными", а с изменением набора строк, подходящих под условие диапазона.
  • На InnoDB ключевой фактор - блокировки диапазонов (next-key) и то, используете ли вы SELECT ... FOR UPDATE/LOCK IN SHARE MODE для диапазонного чтения.
  • mysql repeatable read по умолчанию часто дает "стабильный снимок" для обычных SELECT, но не гарантирует отсутствие фантомов для логики "проверил- потом-вставил" без блокировок.
  • mysql serializable снижает риск фантомов, но увеличивает вероятность блокировок/дедлоков и падение конкурентности.
  • Самый практичный путь: правильные индексы + диапазонные блокировки там, где они нужны, и обязательные ограничения уникальности на уровне схемы.
  • Перед изменением уровни изоляции транзакций mysql фиксируйте метрики ожиданий/дедлоков и прогоняйте два-сеанса тесты.

Модель транзакций в MySQL: ключевые механизмы и MVCC

Транзакции MySQL (в первую очередь в InnoDB) опираются на MVCC (чтение из согласованного снимка) и блокировки строк/диапазонов для изменений. Это подходит, когда вам нужно сочетать высокую конкурентность чтений с предсказуемостью записи.

Когда не стоит "лечить фантомы" только изоляцией: если проблема - в отсутствии уникального ограничения или в логике "проверить наличие → вставить" без защиты, поднятие уровня изоляции лишь маскирует баг и может ухудшить производительность. В таких случаях правильнее начать с ограничений и явных блокировок.

Уровни изоляции в деталях: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Перед тем как менять уровни изоляции транзакций mysql, подготовьте доступы и инструменты: вам понадобится два параллельных соединения (Session A/B), права на SELECT/INSERT/UPDATE в тестовой схеме и возможность посмотреть статус InnoDB.

Что понадобится (минимальный набор)

  • Два SQL-клиента: два окна CLI mysql или два подключения в IDE.
  • Тестовая база/таблица (не гоняйте сценарии на проде).
  • Понимание текущего уровня изоляции: SELECT @@transaction_isolation;
  • Доступ к диагностике: SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST (или Performance Schema, если включен).

Сравнение уровней изоляции и типовых аномалий

Уровень Что допускается Что обычно предотвращается Практический пример риска
READ UNCOMMITTED Грязные чтения, неповторяемые чтения, фантомы Почти ничего Отчеты/проверки могут видеть незакоммиченные строки и принимать неверные решения
READ COMMITTED Неповторяемые чтения, фантомы (в зависимости от паттерна) Грязные чтения Повторный SELECT в транзакции может вернуть другой результат после коммита конкурентной записи
REPEATABLE READ (часто дефолт InnoDB) Фантомы в логике диапазонов без явных блокировок; race в "проверил- потом-вставил" Грязные и неповторяемые чтения для обычного consistent read mysql repeatable read: обычный SELECT стабилен, но без FOR UPDATE можно "не увидеть" вставку и получить конфликт на записи позже
SERIALIZABLE Меньше аномалий, но больше ожиданий/конфликтов Фантомы (ценой конкурентности) mysql serializable: диапазонные чтения склонны блокировать конкурентные вставки, увеличивая ожидания и дедлоки

Быстрые команды для задания уровня изоляции

  • На сессию: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • На одну транзакцию: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (до START TRANSACTION)
  • Проверка: SELECT @@transaction_isolation;

Фантомные чтения: когда и почему они появляются

Мини-чеклист подготовки перед тестами (безопасно)

  • Работайте в отдельной тестовой базе и явно задайте движок InnoDB.
  • Отключите автокоммит на время сценария: SET autocommit=0; (или используйте START TRANSACTION).
  • Включите воспроизводимый порядок: одинаковые запросы, одинаковые параметры, фиксируйте время и порядок действий Session A/B.
  • Убедитесь, что условие запроса использует индекс (иначе блокировки диапазона могут работать не так, как вы ожидаете): EXPLAIN.
  • Договоритесь, что именно считается "фантомом" в вашей задаче: изменение количества строк, появление нового id в диапазоне, нарушение бизнес-инварианта.
  1. Создайте таблицу и индекс под диапазон. Фантомы чаще проявляются на условиях вида WHERE x BETWEEN ... или WHERE x >= ..., поэтому нужен индекс по полю диапазона.

    • SQL:
    CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB;
  2. Зафиксируйте уровень изоляции и старт транзакции в Session A. Это задает, как будут видны изменения других транзакций и какие блокировки будут браться при чтении/записи.

    • SQL (Session A):
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
  3. Выполните диапазонный запрос в Session A и сохраните результат. Для демонстрации важно считать количество строк или набор идентификаторов.

    • SQL (Session A):
    SELECT COUNT(*) AS cnt
FROM orders
WHERE user_id = 10
  AND created_at >= '2026-03-01'
  AND created_at <  '2026-04-01';

    Ожидаемо: вы получили cnt = N и будете сравнивать с повторным чтением.

  4. Вставьте подходящую строку в Session B и закоммитьте. Это и есть "кандидат в фантом": строка, которая начинает удовлетворять условию диапазона.

    • SQL (Session B):
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

INSERT INTO orders(user_id, amount, created_at)
VALUES (10, 500, '2026-03-15 12:00:00');

COMMIT;

    Ожидаемо: вставка успешна и стала видимой для новых чтений (в зависимости от уровня изоляции Session A).

  5. Повторите диапазонный запрос в Session A и сравните. Если результат изменился (N → N+1), вы наблюдаете фантом в смысле изменения множества строк, подходящих под условие.

    • SQL (Session A):
    SELECT COUNT(*) AS cnt
FROM orders
WHERE user_id = 10
  AND created_at >= '2026-03-01'
  AND created_at <  '2026-04-01';

    Ожидаемо: в READ COMMITTED часто увидите N+1. В mysql repeatable read обычный consistent read чаще даст прежнее N, но это не решает гонки "проверил- потом-вставил".

  6. Проверьте, что блокировка диапазона предотвращает вставку. Для этого повторите сценарий, но в Session A используйте блокирующее чтение.

    • SQL (Session A):
    ROLLBACK;
START TRANSACTION;

SELECT id
FROM orders
WHERE user_id = 10
  AND created_at >= '2026-03-01'
  AND created_at <  '2026-04-01'
FOR UPDATE;

    Ожидаемо: в Session B попытка вставки в этот диапазон может ждать (или привести к дедлоку) - это и есть практический способ как избежать фантомных чтений mysql для диапазонных инвариантов.

Репродукция проблем: пошаговые сценарии и SQL-кейсы

Используйте эти кейсы как "минимальный набор" для проверки поведения приложения и того, как именно работают ваши транзакции mysql под нагрузкой.

Сценарий 1: фантом в READ COMMITTED (контрольный)

Транзакции и уровни изоляции в MySQL: когда возникают 
-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE user_id=10 AND created_at >= '2026-03-01' AND created_at < '2026-04-01';

-- Session B
START TRANSACTION;
INSERT INTO orders(user_id, amount, created_at) VALUES (10, 100, '2026-03-20 10:00:00');
COMMIT;

-- Session A (повтор)
SELECT COUNT(*) FROM orders WHERE user_id=10 AND created_at >= '2026-03-01' AND created_at < '2026-04-01';
COMMIT;

Ожидаемый результат: второй COUNT(*) в Session A может стать больше.

Сценарий 2: логика "проверил- потом-вставил" (типичная гонка)

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT 1
FROM orders
WHERE user_id=10
  AND created_at='2026-03-21 09:00:00'
LIMIT 1;

-- Session B
START TRANSACTION;
INSERT INTO orders(user_id, amount, created_at) VALUES (10, 200, '2026-03-21 09:00:00');
COMMIT;

-- Session A (пытаемся вставить "такую же" запись)
INSERT INTO orders(user_id, amount, created_at) VALUES (10, 300, '2026-03-21 09:00:00');
COMMIT;

Ожидаемый результат: без уникального ограничения это приведет к дубликатам; с уникальным - к ошибке, которую надо обрабатывать (ретрай/компенсация).

Чек-лист проверки результата (после каждого прогона)

  • Проверьте текущие уровни изоляции в обеих сессиях: SELECT @@transaction_isolation;
  • Убедитесь, что запрос действительно использует индекс: EXPLAIN SELECT ...
  • Зафиксируйте, где именно блокируется Session B (если блокируется): SHOW PROCESSLIST;
  • Посмотрите, нет ли дедлоков/последней причины ожиданий: SHOW ENGINE INNODB STATUS;
  • Сравните результаты повторных чтений (count/список id) и отметьте, изменился ли набор строк.
  • Проверьте, что приложение обрабатывает ошибки блокировок/дедлоков (ретраи с backoff, идемпотентность).
  • После теста откатите/очистите тестовые данные, чтобы следующий прогон был сопоставим.

Способы устранения фантомов: блокировки, индексные ограничения и оптимистичные схемы

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

Рабочие подходы (выбирайте по инварианту)

  • Диапазонные блокировки: SELECT ... FOR UPDATE по индексируемому диапазону, чтобы конкурентные вставки/изменения в диапазоне ждали.
  • Ограничения уникальности: UNIQUE по бизнес-ключу (часто лучше любого уровня изоляции для "не допустить дубликат").
  • Вынесение инварианта в одну строку: хранить агрегат/лимит в отдельной записи и блокировать ее по PK (SELECT ... FOR UPDATE по одной строке).
  • Оптимистичная схема: попытка записи + обработка конфликта (ошибка уникальности, обнаружение изменения версии) + ретрай.

Частые ошибки, из-за которых фантомы "возвращаются"

  • Использовать FOR UPDATE на запросе, который не идет по индексу (или идет по неподходящему) - вы не получаете ожидаемой защиты диапазона.
  • Пытаться лечить дубликаты уровнем изоляции вместо UNIQUE - дубликаты все равно возможны при ошибках логики или иных путях записи.
  • Смешивать в одной операции разные условия выбора (сначала один фильтр, потом другой) без общей стратегии блокировок.
  • Думать, что mysql repeatable read автоматически защищает "проверил- потом-вставил" - без ограничений и/или блокировок это не гарантия бизнес-инварианта.
  • Поднимать изоляцию до mysql serializable для всего приложения, не выделив критические транзакции точечно (рост ожиданий и неожиданные таймауты).
  • Игнорировать обработку дедлоков: даже правильные блокировки могут дедлочиться при другом порядке захвата.
  • Держать транзакции открытыми "на время HTTP-запроса" - вы увеличиваете окно конфликтов и давление на undo/историю.
  • Не фиксировать контракт на ретраи: сколько попыток, какие ошибки ретраим, как обеспечиваем идемпотентность.

Мониторинг и настройка: параметры сервера, метрики и практический чеклист

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

Что смотреть регулярно (практичные метрики)

  • Длительные транзакции и активные блокировки: SHOW PROCESSLIST, InnoDB статус.
  • Дедлоки и их паттерны: SHOW ENGINE INNODB STATUS (последний deadlock).
  • Доля запросов, которые вынужденно ждут блокировок (по логам/наблюдаемости приложения), и среднее время ожидания.
  • План выполнения критических диапазонных запросов: регулярный EXPLAIN после изменений индексов/схемы.

Практический чеклист перед миграциями и изменением изоляции

  • Определите критические транзакции (где нарушение инварианта реально опасно) и применяйте изоляцию/блокировки точечно.
  • Проверьте наличие нужных индексов под диапазонные условия, которые планируете блокировать.
  • Добавьте/проверьте ограничения уникальности на бизнес-ключи, которые не должны дублироваться.
  • Прогоните два-сеанса сценарии из раздела репродукции и зафиксируйте ожидаемое поведение.
  • Заранее определите политику ретраев на дедлок/lock wait timeout и проверьте идемпотентность.

Альтернативы "повышаем изоляцию", когда они уместны

  1. UNIQUE + обработка конфликта: лучший вариант для запрета дублей по ключу (быстро, прозрачно, надежно).
  2. Явная сериализация на одной строке (лок по PK): если инвариант можно свести к счетчику/лимиту в отдельной записи.
  3. Очередь/саги на уровне приложения: если бизнес-операция длинная, включает внешние сервисы и не должна держать SQL-транзакцию.
  4. Узконаправленный SERIALIZABLE: только для коротких критичных транзакций, где диапазонные фантомы недопустимы и цена блокировок приемлема.

Ответы на типичные вопросы по фантомам и изоляции

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

Нет: неповторяемое чтение - изменение значений уже прочитанной строки, фантом - появление/исчезновение строк в результате диапазонного запроса. В практике они часто идут рядом, но лечатся по-разному.

Правда ли, что mysql repeatable read полностью убирает фантомные чтения?

Для обычных consistent reads часто кажется, что фантомов нет, потому что вы читаете один и тот же снимок. Но логика "проверил диапазон → потом записал" без блокировок/ограничений все равно может приводить к гонкам и нарушениям инвариантов.

Когда имеет смысл включать mysql serializable?

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

Как понять, что SELECT ... FOR UPDATE действительно защищает от вставок в диапазон?

Проверьте, что запрос использует индекс по полю диапазона (через EXPLAIN) и воспроизведите сценарий двумя сессиями: вставка в Session B должна ждать или конфликтовать. Если запрос не индексный, ожидаемого эффекта может не быть.

Достаточно ли READ COMMITTED, чтобы избежать фантомов?

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

Что лучше: блокировки или уникальные ограничения?

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

Какие ошибки приложения чаще всего маскируются "под фантомы"?

Отсутствие уникальности, слишком длинные транзакции и отсутствие ретраев на дедлок/таймаут. Изоляция не заменяет корректный контракт записи и обработки конфликтов.

Похожие статьи

Прокрутить вверх