SQL Interview Prep · Урок

Грязное, неповторяемое и фантомное чтение

Три аномалии чтения и уровень изоляции, который предотвращает каждую из них

«Грязное, неповторяемое и фантомное чтение» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Три аномалии чтения

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

Грязное чтение — чтение незафиксированных данных
Неповторяющееся чтение — строка изменяется между двумя чтениями
Фантомное чтение — между двумя чтениями появляются новые строки

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

Грязное чтение: определение

Грязное чтение происходит, когда транзакция T1 читает строку, которую транзакция T2 изменила, но ещё не зафиксировала. Если затем T2 выполняет откат, T1 уже успела использовать данные, которых на самом деле никогда не существовало.

Только READ UNCOMMITTED допускает грязное чтение. Все более высокие уровни его запрещают.

Опасность в реальном мире: одобрение кредита на основе вклада, который откатывается несколько секунд спустя.

Грязное чтение: временная шкала

Читайте два столбца как временную шкалу. T1 работает на уровне READ UNCOMMITTED.

T1 видит баланс 700, но T2 так и не фиксирует изменения. Значение 700 было иллюзией, возникшей из-за незавершённой работы T2. После отката T2 реальное значение по-прежнему равно 500. T1 приняла решение на основе мусорных данных.

-- T2 (not committed)        | -- T1 (READ UNCOMMITTED)
BEGIN;                       |
UPDATE accounts              |
  SET balance = 700          |
  WHERE id = 1;              |
                             | SELECT balance FROM accounts
                             |   WHERE id = 1;  -- reads 700 (dirty!)
ROLLBACK;                    |
                             | -- T1 acted on a value that never existed

Неповторяющееся чтение: определение

Неповторяющееся чтение происходит, когда T1 читает строку, T2 фиксирует изменение или удаление этой же строки, а T1 читает её снова и видит другое значение.

Обратите внимание на ключевое отличие от грязного чтения: здесь T2 зафиксировала изменения. Данные настоящие, но в рамках одной транзакции они изменились прямо во время работы T1.

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

Неповторяющееся чтение: временная шкала

T1 работает на уровне READ COMMITTED и дважды читает одну и ту же строку. Между чтениями T2 фиксирует изменение.

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

-- T1 (READ COMMITTED)              | -- T2
BEGIN;                              |
SELECT balance FROM accounts        |
  WHERE id = 1;  -- 500            |
                                    | BEGIN;
                                    | UPDATE accounts SET balance = 900
                                    |   WHERE id = 1;
                                    | COMMIT;
SELECT balance FROM accounts        |
  WHERE id = 1;  -- 900 (changed!) |
COMMIT;                             |

Фантомное чтение: определение

Фантомное чтение происходит, когда T1 выполняет запрос с условием поиска, T2 фиксирует INSERT (или DELETE) строк, соответствующих этому условию, а T1 выполняет запрос повторно и получает другой набор строк.

Отличие от неповторяющегося чтения заключается в следующем: неповторяющееся чтение связано с тем, что значение существующей строки изменяется, а фантомное — с тем, что изменяется количество строк, соответствующих предикату.

Только SERIALIZABLE, согласно стандарту, гарантированно предотвращает фантомное чтение.

Фантомное чтение: временная шкала

T1 дважды подсчитывает счета с крупным балансом. Между подсчётами T2 добавляет новую подходящую строку и фиксирует изменения.

Ни одна существующая строка не изменилась, но результат COUNT стал другим. Новая строка и есть «фантом», появившийся в наборе результатов T1.

-- T1 (REPEATABLE READ, standard)      | -- T2
BEGIN;                                 |
SELECT COUNT(*) FROM accounts           |
  WHERE balance > 1000;  -- 3          |
                                       | INSERT INTO accounts(id, balance)
                                       |   VALUES (99, 5000);
                                       | COMMIT;
SELECT COUNT(*) FROM accounts           |
  WHERE balance > 1000;  -- 4 (phantom)|
COMMIT;                                |

Сопоставление аномалий с уровнями

Это сопоставление — основа темы. Уровень, начиная с которого предотвращается каждая аномалия:

Грязное чтение предотвращается начиная с READ COMMITTED.
Неповторяющееся чтение предотвращается начиная с REPEATABLE READ.
Фантомное чтение предотвращается на уровне SERIALIZABLE согласно стандарту.

Обратите внимание, что названия соответствуют сути: REPEATABLE READ делает чтение повторяемым, а уровни названы по аномалии, которую они впервые устраняют.

Неповторяющееся и фантомное чтение: чёткое различие

Это самая распространённая путаница на собеседованиях. Запомните одну фразу:

Неповторяющееся чтение = изменилось значение существующей строки. Фантомное чтение = изменился набор подходящих строк (строки добавились или исчезли).

Проверьте себя: T2 выполняет UPDATE ... WHERE id = 5, затем фиксирует изменения, а T1 повторно читает строку 5. Это неповторяющееся чтение. T2 выполняет INSERT новой строки, соответствующей условию WHERE в запросе T1, а T1 повторно выполняет запрос. Это фантомное чтение.

Перекос записи: дополнительная аномалия

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

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

Снимочная изоляция (Postgres REPEATABLE READ) допускает перекос записи; только SERIALIZABLE его предотвращает. Упоминание этого показывает глубину понимания темы.

Потерянное обновление: четвёртая ловушка

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

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

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

-- Safe pattern: lock the row, or compute atomically
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- locks row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- Or simply: UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Быстрая проверка

Определите аномалию по её поведению.

Итоги: аномалии и способы их устранения

Три аномалии чтения, каждая устраняется более высоким уровнем изоляции:

Грязное чтение (незафиксированные данные) — устраняется на уровне READ COMMITTED.
Неповторяющееся чтение (изменение значения существующей строки) — устраняется на уровне REPEATABLE READ.
Фантомное чтение (изменение набора подходящих строк) — устраняется на уровне SERIALIZABLE.

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

Часто задаваемые вопросы

Урок «Грязное, неповторяемое и фантомное чтение» бесплатный?

Да — полный текст урока «Грязное, неповторяемое и фантомное чтение» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Грязное, неповторяемое и фантомное чтение»?

Три аномалии чтения и уровень изоляции, который предотвращает каждую из них Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Грязное, неповторяемое и фантомное чтение»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Объяснение свойств ACID
Четыре уровня изоляции
Грязное, неповторяемое и фантомное чтение
Взаимные блокировки, блокировки и MVCC

← Назад к SQL Interview Prep