Які рівні ізоляції транзакцій існують

Рівні ізоляції транзакцій визначають ступінь захисту даних однієї транзакції від одночасних змін, які виконуються іншими паралельними транзакціями.

🟢 Junior Level

Стандарт ANSI/ISO SQL-92 визначає 4 класичні рівні ізоляції, упорядковані за зростанням строгості та зниженням продуктивності:

Read Uncommitted (Читання незафіксованих даних): Найнижчий рівень. Транзакція може читати зміни, зроблені іншими транзакціями, які ще не були зафіксовані (COMMIT).
Read Committed (Читання зафіксованих даних): Транзакція бачить лише ті дані, які були успішно зафіксовані до моменту читання. Захищає від «брудного» читання.
Repeatable Read (Повторюване читання): Гарантує, що якщо транзакція прочитала рядок даних, то під час повторного читання цього ж рядка всередині тієї ж транзакції вона побачить точно ті самі дані.
Serializable (Серіалізований): Найстрогіший рівень. Транзакції виконуються з гарантією повної відсутності аномалій паралелізму, так, ніби вони виконувалися строго послідовно одна за одною.

Зведена таблиця класичних аномалій

Рівень ізоляції	Брудне читання (Dirty Read)	Неповторюване читання (Non-Repeatable Read)	Фантомне читання (Phantom Read)
Read Uncommitted	⚠️ Допускається	⚠️ Допускається	⚠️ Допускається
Read Committed	❌ Захищено	⚠️ Допускається	⚠️ Допускається
Repeatable Read	❌ Захищено	❌ Захищено	⚠️ Допускається (крім MySQL InnoDB*)
Serializable	❌ Захищено	❌ Захищено	❌ Захищено

*У MySQL (InnoDB) механізм предикатних блокувань Next-Key Locking запобігає фантомам навіть на рівні Repeatable Read.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderService {

    // Явне завдання рівня ізоляції в Spring
    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public void generateMonthlyReport(Long accountId) {
        // Повторні SELECT за accountId гарантовано повернуть однакові рядки
    }
}

Аналогія: Рівні ізоляції нагадують перегородки в читальній залі: від повної відсутності стін, де всі заглядають через плече сусіда (Read Uncommitted), до легких фіранок (Read Committed), окремих кабінок (Repeatable Read) та окремої замкненої кімнати з охороною для одного читача (Serializable).

🟡 Middle Level

Опис аномалій паралельного доступу

Брудне читання (Dirty Read): Транзакція $T_1$ змінила рядок, але ще не зафіксувала комміт. Транзакція $T_2$ прочитала цей змінений рядок. Потім $T_1$ виконує ROLLBACK. Транзакція $T_2$ залишилася з даними, яких фізично не існує.
Неповторюване читання (Non-Repeatable / Fuzzy Read): Транзакція $T_1$ зчитує рядок зі значенням balance = 100. Транзакція $T_2$ оновлює баланс на 150 і робить COMMIT. Транзакція $T_1$ повторно читає той самий рядок і отримує вже 150.
Фантомне читання (Phantom Read): Транзакція $T_1$ запитує діапазон рядків: SELECT * FROM users WHERE age > 18 (отримує 5 записів). Транзакція $T_2$ вставляє нового користувача з age = 20 і робить COMMIT. Транзакція $T_1$ повторює той самий запит і отримує вже 6 рядків (з’явився рядок-фантом).

Дефолтні рівні ізоляції в популярних СУБД

PostgreSQL, Oracle, Microsoft SQL Server: За замовчуванням використовують Read Committed. Це забезпечує максимальну пропускну здатність (TPS) і виключає накладні витрати на тривале зберігання старих версій рядків.
MySQL (InnoDB): Історично використовує за замовчуванням Repeatable Read (у ранніх версіях MySQL це вимагалося для коректної роботи реплікації на базі SQL-виразів Statement-Based Replication).

Архітектурна поведінка рівнів у PostgreSQL

У PostgreSQL механізм MVCC влаштований так, що створення знімка даних (Snapshot) є природною операцією:

На рівні Read Committed: Свіжий знімок даних (Snapshot) створюється перед виконанням кожного окремого SQL-запиту всередині транзакції.
На рівні Repeatable Read: Знімок даних створюється рівно один раз у момент старту першого запиту транзакції і залишається незмінним до виклику COMMIT.

🔴 Senior Level

Аномалія Write Skew (Перекіс запису) та Snapshot Isolation

Класичний стандарт ANSI SQL-92 критикують за неповноту, оскільки він не враховував багатоверсійність (MVCC) і пропустив критичну аномалію — Write Skew.

Багато промислових СУБД (PostgreSQL, Oracle) під рівнем Repeatable Read фактично реалізують алгоритм Snapshot Isolation (Ізоляція знімка). Snapshot Isolation захищає від Dirty Read, Non-Repeatable Read та фантомів під час читання, але допускає аномалію Write Skew.

Приклад аномалії Write Skew:

Бізнес-правило: На чергуванні в лікарні має залишатися хоча б один лікар (count(on_call) >= 1). Зараз чергують двоє: Лікар А та Лікар Б.
Транзакція 1 (Лікар А): Перевіряє SELECT count(*) WHERE on_call = true (результат: 2). Знімає чергування з Лікаря А: UPDATE doctors SET on_call = false WHERE name = 'A'.
Транзакція 2 (Лікар Б): Одночасно перевіряє SELECT count(*) (результат: 2, оскільки $T_1$ ще не закомічена). Знімає чергування з Лікаря Б: UPDATE doctors SET on_call = false WHERE name = 'B'.
Обидві транзакції успішно фіксуються (COMMIT)!
Підсумок: На чергуванні залишилося 0 лікарів. Інваріант системи порушено! Жоден рядок не зазнав конфлікту UPDATE на один і той самий запис, тому Repeatable Read (Snapshot Isolation) успішно пропустив обидві транзакції. Захистити від Write Skew може тільки рівень Serializable (або явний SELECT FOR UPDATE).

Serializable Snapshot Isolation (SSI) у PostgreSQL

Замість традиційних важких песимістичних блокувань предикатів (Predicate Locks, використовуваних у 2PL), PostgreSQL починаючи з версії 9.1 використовує прогресивний алгоритм SSI (Serializable Snapshot Isolation):

[T1: Читає A] ------ rw-antidependency -----> [T2: Пише в A]
                                                      |
                                             rw-antidependency
                                                      v
                                                [T3: Читає B]

Транзакції виконуються без блокувань читання на базі снапшотів.
В оперативній пам’яті підтримуються неблокувальні легковажні замки SIREAD locks.
Система відстежує небезпечні структури — цикли залежностей виду «читання-запис» (rw-antidependencies).
У разі виявлення потенційного циклу одна з транзакцій переривається ядром СУБД із помилкою: ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).

Патерн безпечного застосування Serializable в Java / Spring

Під час роботи на рівні Serializable застосунок зобов’язаний мати механізм повтору (Retry Logic), оскільки помилки серіалізації є штатними подіями:

import org.springframework.dao.CannotSerializeTransactionException;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class HospitalService {

    @Retryable(
        retryFor = { CannotSerializeTransactionException.class },
        maxAttempts = 5,
        backoff = @Backoff(delay = 100, multiplier = 2.0)
    )
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void leaveOnCallDuty(String doctorName) {
        long activeDoctors = doctorRepository.countByOnCallTrue();
        if (activeDoctors <= 1) {
            throw new IllegalStateException("Не можна зняти останнього чергового лікаря!");
        }
        doctorRepository.updateOnCall(doctorName, false);
    }
}

4 Tricky Questions

1. Чому в PostgreSQL встановлення `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;` не призводить до брудного читання?

Відповідь: У PostgreSQL рівень Read Uncommitted фізично відсутній у рушії. Якщо розробник виконує команду SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, PostgreSQL парсить її для забезпечення сумісності зі стандартом ANSI SQL, але непомітно мапить на рівень Read Committed. Архітектура PostgreSQL MVCC перевіряє видимість кожного кортежу за статусом транзакції в журналі pg_xact. Кортеж, створений незафіксованою транзакцією, вважається невидимим за замовчуванням без жодних накладних витрат. Рушій PostgreSQL технічно не вміє віддавати «брудні» кортежі.

2. Чому в MySQL InnoDB рівень `Repeatable Read` захищає від фантомних читань, а в PostgreSQL — ні?

Відповідь: Різниця полягає в механізмах ізоляції:

PostgreSQL: Реалізує Repeatable Read на чистому MVCC (Snapshot Isolation). Знімок бачить лише записи, закомічені до старту транзакції. Проте під час виконання подальших операцій модифікації (UPDATE ... WHERE) Postgres може зачепити рядки, вставлені паралельною транзакцією, що призводить до аномалій фантомів під час запису.
MySQL (InnoDB): Для запобігання фантомам доповнює MVCC спеціальним типом песимістичних блокувань — Next-Key Locks (комбінація блокування самого запису Record Lock та блокування діапазону між записами Gap Lock). Якщо транзакція зчитує діапазон рядків із блокуванням, інші транзакції фізично не можуть вставити новий рядок у цей діапазон.

3. Чим відрізняється створення знімка даних (Snapshot) у PostgreSQL на рівнях `Read Committed` та `Repeatable Read`?

Відповідь:

На рівні Read Committed: Знімок видимості кортежів формується заново на початку кожного окремого SQL-виразу (statement-level snapshot). Якщо транзакція виконує SELECT, потім чекає 5 секунд і повторює SELECT, другий запит отримає новий знімок і побачить усі комміти, що відбулися за ці 5 секунд.
На рівні Repeatable Read: Знімок видимості створюється один раз у момент виконання першого запиту транзакції (transaction-level snapshot) і зберігається до кінця транзакції. Будь-які повторні запити в межах транзакції бачать базу даних у суворо зафіксованому стані на момент старту.

4. У чому полягає небезпека тривалих транзакцій на рівні `Repeatable Read` та `Serializable` для СУБД?

Відповідь: Тривалі транзакції з довгоживучими знімками даних блокують процес очищення старих версій рядків:

У PostgreSQL (Table Bloat): Фоновий процес VACUUM не має права видаляти мертві рядки (dead tuples), доки існує хоча б одна транзакція, чий xmin старший за транзакцію, що видалила цей рядок. Тривала транзакція «заморожує» вакуум, призводячи до лавиноподібного розростання таблиць та індексів на диску.
У MySQL InnoDB (Undo Log Overflow): Сервер змушений зберігати ланцюжки відкату в сегментах Undo Tablespace. Довга транзакція призводить до гігантського зростання системних файлів undo-логів і сповільнення збирання сміття (Purge Thread).

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

4 рівні ізоляції:
- Read Uncommitted: Допускає Dirty, Non-repeatable, Phantom Read. У Postgres працює як Read Committed.
- Read Committed: Захищає від Dirty Read. Знімок створюється на кожен SQL-запит. Дефолт у Postgres, Oracle.
- Repeatable Read: Захищає від Dirty та Non-Repeatable Read. Знімок створюється 1 раз на транзакцію. Дефолт у MySQL.
- Serializable: Повна ізоляція (захист від фантомів та Write Skew).
Аномалія Write Skew: Ситуація, коли дві транзакції паралельно читають перехресні дані, змінюють неперехресні рядки і порушують спільний інваріант. Вирішується тільки на Serializable або через SELECT FOR UPDATE.
PostgreSQL SSI: Не використовує блокування читання; будує граф rw-antidependencies і скасовує конфліктні транзакції (помилка 40001).
Retry Logic: На рівні Serializable застосунок обов’язково повинен мати @Retryable на випадок CannotSerializeTransactionException.

Червоні прапорці (чого уникати)

Твердження, що Repeatable Read в усіх СУБД гарантує захист від фантомів (це гарантує MySQL через Next-Key Locks, але не ANSI SQL і не чистий Snapshot Isolation).
Твердження, що Serializable завжди реалізується через важкі блокування таблиць (PostgreSQL використовує оптимістичний алгоритм SSI).
Забування про необхідність Retry Logic при роботі з рівнем Serializable.
Ігнорування проблеми Table Bloat через довгі транзакції на Repeatable Read у PostgreSQL.