---

🟢 Junior Level

Фантомне читання (Phantom Read) — це аномалія паралельного доступу до даних, за якої одна транзакція двічі виконує пошук за діапазоном рядків з однією і тією ж предикатною умовою (наприклад, WHERE status = 'NEW'), але другий запит повертає інший набір рядків (з’явилися нові рядки або зникли старі), оскільки паралельна транзакція встигла вставити (INSERT) або видалити (DELETE) записи та зафіксувати транзакцію (COMMIT).

Нові рядки, яких не було під час першого читання, називають «рядками-фантомами».

Приклад сценарію фантомного читання

Час      Транзакція А (Генерація звіту)          Транзакція Б (Нове замовлення клієнта)
  |      -------------------------------         --------------------------------------
  |  T1: BEGIN;
  |  T2: SELECT COUNT(*) FROM orders             -- Результат: 10 замовлень
  |      WHERE status = 'PENDING';
  |  T3:                                         BEGIN;
  |  T4:                                         INSERT INTO orders (id, status, amount)
  |                                              VALUES (11, 'PENDING', 5000);
  |                                              COMMIT; -- Зафіксовано!
  |  T5: SELECT SUM(amount) FROM orders          -- Рахує суму вже за 11 замовленнями!
  |      WHERE status = 'PENDING';               -- Підсумковий звіт: 10 замовлень на суму 11 замовлень!
  v  T6: COMMIT;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class OrderAuditService {

    // Захист від фантомів на рівні бази даних:
    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public AuditReport calculateDailySummary() {
        long count = orderRepository.countByStatus("PENDING");
        BigDecimal total = orderRepository.sumByStatus("PENDING");
        // На рівні Repeatable Read у PostgreSQL/MySQL обидва запити
        // побачать суворо однаковий набір рядків.
        return new AuditReport(count, total);
    }
}

Аналогія: Екскурсовод рахує туристів в автобусі: «Всього 20 людей». Поки він робить помітку в журналі, через задні двері тихо заходять ще двоє запізнілих. Екскурсовод рахує знову — тепер туристів 22. Двоє нових туристів — це фантоми.

---

🟡 Middle Level

Відмінність: Неповторюване читання vs Фантомне читання

Критерій	Неповторюване читання (Non-Repeatable Read)	Фантомне читання (Phantom Read)
Що змінюється	Значення в вже наявних рядках	Кількість рядків у результуючій вибірці
SQL-команда конкурента	UPDATE (або точковий DELETE)	INSERT (або масовий DELETE за діапазоном)
Тип предиката	Точковий пошук рядка: WHERE id = 42	Предикатний діапазон: WHERE price > 100
Як захиститися	Рядкові блокування SELECT FOR UPDATE	Блокування діапазонів (Gap Locks) або MVCC Snapshot

Поведінка Repeatable Read у різних СУБД

За формальним стандартом ANSI SQL-92 рівень Repeatable Read зобов’язаний захищати від Dirty Read та Non-Repeatable Read, але має право допускати фантомне читання:

СУБД	Фантоми на Repeatable Read?	Механізм захисту
PostgreSQL	❌ Захищено (для чистих SELECT)	MVCC Snapshot з моменту першого запиту
MySQL (InnoDB)	❌ Захищено (для SELECT і FOR UPDATE)	Next-Key Locks (Record Lock + Gap Lock)
Oracle	❌ Захищено	MVCC Snapshot (аналог PostgreSQL)
MS SQL Server	⚠️ Можливі!	Встановлює S-блокування лише на знайдені рядки, проміжки вільні

---

🔴 Senior Level

Механіка Next-Key Locking у MySQL (InnoDB)

У MySQL InnoDB на рівні Repeatable Read для запобігання фантомам використовується гібридний механізм:

\[\text{Next-Key Lock} = \text{Record Lock (блокування рядка)} + \text{Gap Lock (блокування проміжку)}\]

-- Таблиця із записами id = 5, 10, 15
SELECT * FROM orders WHERE id BETWEEN 8 AND 12 FOR UPDATE;

Що саме блокує InnoDB:

1. Record Lock: Блокує сам запис id = 10.
2. Gap Lock (ліворуч): Блокує інтервал $(5, 10)$ — жодна інша транзакція не може виконати INSERT зі значенням $id \in [6, 9]$.
3. Gap Lock (праворуч): Блокує інтервал $(10, 15)$ — жодна транзакція не може виконати INSERT зі значенням $id \in [11, 14]$.

Побічний ефект Gap Locks: Блокування проміжків не конфліктують між собою на читання, але конфліктують зі вставками. Якщо дві паралельні транзакції накладають Gap-блокування, що перетинаються, подальші оператори INSERT обох транзакцій гарантовано призведуть до взаємного блокування (Deadlock).

Механіка Predicate Locks у PostgreSQL (SSI)

PostgreSQL на рівні Repeatable Read не використовує блокування проміжків, покладаючись на незмінність MVCC-знімка. Однак на рівні Serializable для повної ізоляції задіюється алгоритм SSI (Serializable Snapshot Isolation):

• Під час предикатного запиту SELECT * FROM orders WHERE status = 'PENDING' реєструється Predicate Lock (предикатний замок в оперативній пам’яті — SIREAD lock).
• Предикатний замок прив’язується до сторінок індексу B-Tree, що покривають умову status = 'PENDING'.
• Якщо паралельна транзакція вставляє новий запис, що потрапляє в ту саму сторінку індексу, ядро PostgreSQL миттєво фіксує виникнення rw-антизалежності ($T_{reader} \xrightarrow{rw} T_{inserter}$).
• У разі виявлення циклів залежностей одна з транзакцій переривається з помилкою серіалізації 40001.

---

4 Tricky Questions

1. Чому в стандарті ANSI SQL рівень Repeatable Read припускає появу фантомів, а в PostgreSQL вони не відтворюються під час звичайних SELECT?

Відповідь: Стандарт ANSI SQL-92 створювався в епоху домінування класичних блокувальних рушіїв (Lock-Based Concurrency Control / 2PL). У блокувальних СУБД для забезпечення Repeatable Read на прочитані рядки накладалися розділювані блокування (S-locks). Оскільки блокувалися тільки вже наявні рядки, новий рядок вільно вставлявся в таблицю паралельною транзакцією, породжуючи фантом. У PostgreSQL ізоляція реалізована на базі MVCC. Знімок даних заморожує транзакційний горизонт (xmin/xmax). Будь-який новий рядок, вставлений паралельною транзакцією, отримує xmin, який є більшим за горизонт поточного знімка, і апаратно відсікається ядром СУБД під час будь-яких наступних SELECT.

---

2. Чи може SELECT ... FOR UPDATE на рівні Read Committed захистити від появи фантомів під час вставки нових записів?

Відповідь: Ні, в загальному випадку не може! Запит SELECT ... FOR UPDATE на рівні Read Committed блокує монопольними блокуваннями (X-locks) виключно ті рядки, які вже фізично існують у таблиці на момент виконання запиту. Якщо паралельна транзакція виконує INSERT нового рядка, що задовольняє умову предиката:

• Нового рядка в таблиці ще не було, заблокувати його було неможливо.
• На рівні Read Committed блокування проміжків (Gap Locks) не накладаються.
• Вставка успішно виконається і зафіксується. Повторний запит SELECT ... FOR UPDATE на рівні Read Committed побачить новий рядок і накладе на нього замок, зафіксувавши фантом. Запобігти фантомам можна лише на рівні Serializable або Repeatable Read у MySQL.

---

3. У чому полягає аномалія «Фантомного оновлення» (Phantom Update) у PostgreSQL на рівні Repeatable Read?

Відповідь: Хоча звичайний SELECT у PostgreSQL на Repeatable Read захищений від фантомів, операція UPDATE працює за поточними зафіксованими даними (Current Read). Розглянемо сценарій:

-- Транзакція T1 (Repeatable Read):
SELECT count(*) FROM orders WHERE status = 'PENDING'; -- Бачить 5 рядків

-- Транзакція T2:
INSERT INTO orders (status) VALUES ('PENDING'); -- Вставляє 6-й рядок!
COMMIT;

-- Транзакція T1 виконує масовий UPDATE:
UPDATE orders SET status = 'PROCESSED' WHERE status = 'PENDING';
-- Результат: 6 rows updated! (T1 оновила рядок, якого сама не бачила!)

-- Повторний SELECT у T1:
SELECT count(*) FROM orders WHERE status = 'PROCESSED'; -- Поверне 6 рядків!

Транзакція $T1$ оновила прихований від неї рядок-фантом, а згідно з правилами MVCC транзакція завжди бачить рядки, модифіковані нею самою.

---

4. Чому використання тимчасових таблиць (Temporary Tables) може порушити захист від фантомів на рівні Serializable у PostgreSQL?

Відповідь: У PostgreSQL алгоритм Serializable Snapshot Isolation (SSI) відстежує предикатні блокування (SIREAD locks) тільки для постійних таблиць бази даних. Тимчасові таблиці (CREATE TEMP TABLE) сесійно ізольовані і не реєструються в загальному менеджері предикатних блокувань shared memory. Якщо бізнес-логіка копіює дані в тимчасову таблицю, виконує над нею агрегатні розрахунки і приймає рішення для основної бази, алгоритм SSI не зможе виявити міжтранзакційні конфлікти залежностей (rw-antidependencies), що призведе до пропуску фантомів і порушення серіалізовності.

---

🎯 Шпаргалка для інтерв’ю

• Суть Phantom Read: Повторний предикатний запит (WHERE condition) повертає іншу кількість рядків через паралельний INSERT або DELETE та COMMIT.
• Різниця з Non-Repeatable Read:
  • Non-Repeatable Read — змінюються поля вже прочитаного рядка (UPDATE).
  • Phantom Read — змінюється склад рядків у діапазоні вибірки (INSERT/DELETE).
• Поведінка в СУБД:
  • PostgreSQL: MVCC-знімок захищає від фантомів під час звичайних SELECT на рівні Repeatable Read.
  • MySQL InnoDB: Next-Key Locks (Record + Gap Locks) захищають від фантомів на Repeatable Read.
  • MS SQL Server: На Repeatable Read фантоми можливі.
• Next-Key Lock: Комбінація блокування запису і блокування інтервалів між індексами (запобігає вставкам у проміжки, але провокує Deadlocks).
• Абсолютний захист: Гарантується тільки на рівні Serializable.

Червоні прапорці (Red Flags)

• Стверджувати, що Repeatable Read за стандартом ANSI SQL зобов’язаний захищати від фантомів (за стандартом він це дозволяє; захист у PG/MySQL — реалізація рушія).
• Вважати, що SELECT ... FOR UPDATE на Read Committed захищає від фантомів (він блокує лише наявні рядки, вставка нового рядка не блокується).
• Не знати різниці між блокуванням рядка (Record Lock) та блокуванням проміжку (Gap Lock).

Пов’язані теми

• Що таке Repeatable Read
• Що таке Serializable
• Що таке неповторюване читання (Non-Repeatable Read)
• Що таке втрачене оновлення (Lost Update)