Який рівень ізоляції за замовчуванням в MySQL

У MySQL при використанні стандартного рушія зберігання даних InnoDB рівнем ізоляції за замовчуванням є Repeatable Read.

🟢 Junior Level

У MySQL при використанні стандартного рушія зберігання даних InnoDB рівнем ізоляції за замовчуванням є Repeatable Read.

Суть у 30 секундах

На рівні Repeatable Read транзакція працює з фіксованим «знімком» даних (Read View), який створюється в момент виконання першого оператора читання (SELECT).

Головні практичні властивості:

Неповторюване читання виключено: Скільки б разів усередині однієї транзакції ви не виконували SELECT balance FROM accounts WHERE id = 1, результат буде абсолютно ідентичним, навіть якщо в цей момент сотні паралельних транзакцій оновлять і зафіксують нові значення.
Фантомне читання відвернено: На відміну від вимог класичного стандарту ANSI SQL, MySQL InnoDB на рівні Repeatable Read блокує і фантоми для більшості сценаріїв (для звичайних SELECT — через незмінний MVCC-знімок, а для SELECT ... FOR UPDATE та UPDATE — через блокування діапазонів Next-Key Locks).

Транзакція A (Repeatable Read)          Транзакція B (Паралельна)
------------------------------          ---------------------------
BEGIN;
SELECT balance FROM accounts 
WHERE id = 1; -> 1000.00
                                        BEGIN;
                                        UPDATE accounts SET balance = 5000.00 WHERE id = 1;
                                        COMMIT; -- Зафіксовано!
SELECT balance FROM accounts 
WHERE id = 1; -> 1000.00                -- Все ще 1000.00! Зміни B не видно.
COMMIT;

Порівняння поведінки за замовчуванням: MySQL vs PostgreSQL

Характеристика	MySQL (InnoDB)	PostgreSQL
Рівень за замовчуванням	Repeatable Read	Read Committed
Модель знімка даних	Один знімок на всю транзакцію (при першому SELECT)	Новий знімок на кожен SQL-запит
Захист від фантомів	Захищено (MVCC + Next-Key Locks)	Ні (на дефолтному Read Committed фантоми можливі)
Ризик дедлоків	Вищий (через блокування проміжків Gap Locks)	Нижчий (блокуються лише реальні рядки)
Механізм MVCC	Undo Log (старі версії витісняються в undo-сегменти)	Heap Tuples (усі версії зберігаються прямо в сторінках таблиці)

Приклад у Spring Boot

Якщо в застосунку на Spring Boot налаштовано DataSource до MySQL, анотація @Transactional без явного зазначення isolation використовує Repeatable Read:

package com.example.service;

import com.example.entity.Account;
import com.example.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;

@Service
@RequiredArgsConstructor
public class MysqlReportService {

    private final AccountRepository accountRepository;

    @Transactional // У MySQL InnoDB: Repeatable Read
    public void generateConsistentAuditReport(Long accountId) {
        // Запит 1: фіксує Read View транзакції
        BigDecimal firstBalance = accountRepository.findBalanceNative(accountId);

        // Тривалі обчислення, зовнішні виклики...
        simulateHeavyCalculation();

        // Запит 2: гарантовано повертає те саме значення,
        // навіть якщо паралельні транзакції встигли внести 1000 змін
        BigDecimal secondBalance = accountRepository.findBalanceNative(accountId);

        assert firstBalance.compareTo(secondBalance) == 0 : "Баланси не збігаються!";
    }

    private void simulateHeavyCalculation() {
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

🟡 Middle Level

Чому MySQL історично обрав Repeatable Read за замовчуванням

Головна причина вибору Repeatable Read у MySQL — реплікація на основі операторів (Statement-Based Replication), яка була стандартом у MySQL 3.x, 4.x та 5.x.

Сценарій при Statement-Based Replication:
Майстер:
  T1 (розпочалася о 10:00:00): UPDATE orders SET status = 'DONE' WHERE status = 'PENDING';
  T2 (розпочалася о 10:00:01): INSERT INTO orders (status) VALUES ('PENDING'); COMMIT;
  T1: COMMIT; (о 10:00:05)

Бінарний лог (Binlog) майстра:
  Події в binlog пишуться суворо ПІСЛЯ ЗАВЕРШЕННЯ ТРАНЗАКЦІЇ (у момент COMMIT).
  Тому порядок запису в binlog:
  1. T2 (COMMIT о 10:00:01)
  2. T1 (COMMIT о 10:00:05)

Репліка (Replica) читає binlog і виконує:
  1. INSERT INTO orders ... ('PENDING')
  2. UPDATE orders SET status = 'DONE' WHERE status = 'PENDING'
  -> На репліці новий рядок T2 перейде в статус 'DONE'!
  -> На майстрі рядок T2 залишився 'PENDING', тому що T1 не бачила рядок T2!
  -> ПІДСУМОК: Розсинхронізація даних між майстром і реплікою (Data Divergence)!

Щоб гарантувати ідентичність стану реплік при binlog_format=STATEMENT, InnoDB зобов’язаний був блокувати вставку будь-яких нових рядків у діапазони, скановані транзакцією T1. Це досягається лише рівнем Repeatable Read з використанням Next-Key Locks.

💡 Сучасний статус: Починаючи з MySQL 5.7/8.0 стандартом стала реплікація рядків (binlog_format=ROW). У цьому режимі фізичні зміни рядків передаються напряму, ризик розходження зникає, і більшість високонавантажених компаній (Uber, GitHub, Meta) примусово перемикають дефолт MySQL на READ COMMITTED.

Як працює MVCC в InnoDB: Clustered Index та Undo Log

На відміну від PostgreSQL, де всі версії рядків зберігаються в основній таблиці, InnoDB використовує принципово іншу архітектуру:

Кластерний індекс (Clustered Index / B+Tree): Завжди містить тільки найсвіжішу версію рядка.
У кожному рядку кластерного індексу присутні приховані системні поля:
- DB_TRX_ID (6 байтів): ідентифікатор транзакції, яка останньою виконала INSERT або UPDATE цього рядка.
- DB_ROLL_PTR (7 байтів): вказівник відкату (Rollback Pointer), що посилається на запис в Undo Log.
- DB_ROW_ID (6 байтів): прихований автоінкрементний ID (якщо у таблиці немає явного Primary Key).
Ланцюжок версій в Undo Log: Коли транзакція виконує UPDATE, стара версія рядка копіюється в Undo Tablespace, а в новому рядку поле DB_ROLL_PTR вказує на стару версію.

Кластерний індекс (Таблиця):
[ ID: 1 | balance: 1500 | DB_TRX_ID: 200 | DB_ROLL_PTR: &undo_1 ]
                                                   |
                                                   v
Undo Log (Undo Tablespace):
[ Запис undo_1: balance: 1200 | DB_TRX_ID: 150 | DB_ROLL_PTR: &undo_2 ]
                                                   |
                                                   v
[ Запис undo_2: balance: 1000 | DB_TRX_ID: 100 | DB_ROLL_PTR: NULL ]

Під час виконання SELECT:

Транзакція перевіряє DB_TRX_ID рядка в таблиці проти свого Read View.
Якщо транзакція 200 молодша, ніж наш знімок, InnoDB розмотує ланцюжок за DB_ROLL_PTR в оперативній пам’яті/буферному пулі, доки не знайде версію, що задовольняє правилам видимості знімка.

🔴 Senior Level

Структура Read View у коді InnoDB

У вихідному коді ядра MySQL (модуль storage/innobase/include/read0types.h) структура ReadView включає такі ключові поля:

class ReadView {
    trx_id_t m_low_limit_id;     // Верхня межа: XID першої транзакції, створеної ПІСЛЯ нашого Read View.
                                 // Усі транзакції з trx_id >= m_low_limit_id НЕВИДИМІ.
    trx_id_t m_up_limit_id;      // Нижня межа: найменший XID серед усіх активних транзакцій.
                                 // Усі транзакції з trx_id < m_up_limit_id гарантовано ВИДИМІ.
    trx_id_t m_creator_trx_id;   // XID транзакції, яка створила цей Read View (свої зміни завжди видно).
    ids_t    m_ids;              // Список XID усіх незафіксованих транзакцій на момент створення Read View.
};

Правило видимості:

Якщо trx_id == m_creator_trx_id $\rightarrow$ Видно (власні зміни).
Якщо trx_id < m_up_limit_id $\rightarrow$ Видно (зафіксовано до старту знімка).
Якщо trx_id >= m_low_limit_id $\rightarrow$ Не видно (розпочато після створення знімка).
Якщо m_up_limit_id <= trx_id < m_low_limit_id: перевіряється наявність trx_id у масиві m_ids. Якщо є в m_ids — транзакція була активна на момент створення знімка, її зміни не видно. Якщо немає в m_ids — видно.

Механізм Next-Key Locking: Record Lock + Gap Lock

В InnoDB існує 3 типи рядкових блокувань:

Record Lock: Блокує сам індексний запис.
Gap Lock: Блокує інтервал (проміжок) між індексними записами, забороняючи іншим транзакціям виконувати INSERT у цей діапазон.
Next-Key Lock: Комбінація Record Lock + Gap Lock на інтервал перед записом: $(\text{попередній ключ}, \text{поточний ключ}]$.

Індекс за полем age: значення [18, 25, 40]
Next-Key інтервали:
  (-∞, 18]
  (18, 25]
  (25, 40]
  (40, +∞) (блокування "supremum pseudo-record")

Приклад блокування діапазону:

-- Транзакція 1 (Repeatable Read)
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

InnoDB накладає Next-Key Lock на діапазони (18, 25] та (25, 40]. У цей момент будь-яка спроба іншої транзакції виконати:

-- Транзакція 2
INSERT INTO users (name, age) VALUES ('Bob', 22); -- ЗАБЛОКОВАНО! Чекає коміту T1

Саме так InnoDB запобігає появі фантомних рядків при блокуючих операціях!

Проблема дедлоків Gap Lock та оптимізація для Highload

Головний недолік дефолтного рівня Repeatable Read у високонавантажених системах — часті взаємні блокування (Deadlocks):

Gap Locks сумісні між собою: Дві транзакції можуть одночасно утримувати Gap Lock на один і той самий інтервал.
Але Gap Lock несумісний з Insert Intention Lock: Коли обидві транзакції намагаються зробити INSERT у заблокований ними ж проміжок, виникає циклічний дедлок:

T1: тримає Gap Lock на інтервалі (10, 20)
T2: тримає Gap Lock на інтервалі (10, 20)
T1: намагається виконати INSERT (id = 15) -> чекає зняття Gap Lock у T2
T2: намагається виконати INSERT (id = 16) -> чекає зняття Gap Lock у T1
-> DEADLOCK! InnoDB перериває одну з транзакцій з помилкою:
"Deadlock found when trying to get lock; try restarting transaction"

Production Best Practice для Highload:

У високонавантажених мікросервісах на MySQL наполегливо рекомендується:

Увімкнути реплікацію рядків у my.cnf:
```
binlog_format = ROW
```
Перемкнути рівень ізоляції за замовчуванням на Read Committed:
```
transaction-isolation = READ-COMMITTED
```
При READ COMMITTED рушій InnoDB повністю вимикає Gap Locks для пошуку та сканування (залишаючи їх лише для перевірки зовнішніх ключів та унікальності). Кількість дедлоків знижується на 90–99%, а пропускна здатність системи зростає на 20–40%!

4 Tricky Questions

1. Як на рівні Repeatable Read у MySQL отримати фантомний рядок за допомогою `UPDATE` (знаменита «фантомна пастка» InnoDB)?

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

Сценарій:

-- Транзакція T1:
BEGIN;
SELECT * FROM users WHERE status = 'NEW'; -- Бачить 0 рядків. Створено Read View.

-- Транзакція T2 (паралельно):
INSERT INTO users (id, name, status) VALUES (10, 'Alice', 'NEW');
COMMIT;

-- Транзакція T1 продовжує:
SELECT * FROM users WHERE status = 'NEW'; -- Як і раніше 0 рядків (захист Read View).

-- УВАГА: T1 виконує UPDATE усіх рядків зі статусом NEW:
UPDATE users SET status = 'ACTIVE' WHERE status = 'NEW';
-- Запит поверне: Rows matched: 1, Changed: 1!
-- UPDATE прочитав свіжий фізичний рядок і записав у його DB_TRX_ID ідентифікатор T1!

-- Тепер T1 знову робить SELECT:
SELECT * FROM users WHERE status = 'ACTIVE';
-- ЗНЕНАЦЬКА рядок з id = 10 З'ЯВИВСЯ в результатах SELECT!

Чому це сталося: Оскільки транзакція T1 сама модифікувала рядок 10, поле DB_TRX_ID кортежу стало рівним ID транзакції T1. За правилами MVCC транзакція завжди бачить рядки, змінені нею самою. У результаті рядок фантомно матеріалізувався всередині тієї ж сесії!

2. Що станеться, якщо в MySQL з `binlog_format=STATEMENT` спробувати виставити `SET TRANSACTION ISOLATION LEVEL READ COMMITTED`?

Відповідь: MySQL видасть критичну помилку при спробі виконати першу DML-операцію: ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. (або ERROR 1598).

MySQL на рівні ядра валідує безпеку реплікації. Оскільки при READ COMMITTED відсутні блокування діапазонів (Gap Locks), паралельні транзакції на майстрі можуть зафіксуватися в порядку, що не гарантує детерміноване відтворення на репліках через SQL-вирази. Для переходу на READ COMMITTED адміністратор зобов’язаний попередньо увімкнути binlog_format = ROW або MIXED.

3. Чим принципово відрізняється зберігання версій рядків (MVCC) у MySQL InnoDB від PostgreSQL, і як це впливає на довгі аналітичні транзакції?

Відповідь:

PostgreSQL: Використовує архітектуру In-Place MVCC. Старі версії рядків (dead tuples) залишаються лежати на тих самих сторінках таблиці, де й живі. Довга транзакція блокує роботу autovacuum, призводячи до катастрофічного розпухання файлів таблиць та індексів (Table Bloat).
MySQL InnoDB: Використовує архітектуру Rollback Segments / Undo Log. У кластерному індексі завжди лежить найостанніша версія рядка. Старі версії витісняються в системні файли Undo Tablespace.
Вплив на довгі транзакції: У MySQL довгий SELECT не роздуває основну таблицю, але блокує очищення Undo-логів процесом Purge Thread. У результаті розмір Undo Tablespace стрімко зростає, а читання гарячих рядків іншими транзакціями сповільнюється, оскільки їм доводиться розкручувати довгі ланцюжки DB_ROLL_PTR за посиланнями в пам’яті та на диску.

4. Чому запит `SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE` в InnoDB на рівні Repeatable Read може заблокувати ВСЮ таблицю, якщо на полі `status` немає індексу?

Відповідь: Під час виконання блокуючого читання (FOR UPDATE) InnoDB зобов’язаний накласти Next-Key Locks на всі проскановані індексні записи та проміжки між ними.

Якщо колонка status не проіндексована, оптимізатор змушений виконувати Full Table Scan за первинним кластерним ключем.
У процесі сканування таблиці InnoDB послідовно накладає Next-Key Lock на абсолютно кожен рядок кластерного індексу та на всі проміжки (Gaps) між ними, аж до псевдозапису supremum.
У результаті таблиця виявляється фактично заблокованою цілком: паралельні транзакції не зможуть виконати жоден INSERT, UPDATE або DELETE для будь-яких рядків, навіть якщо їхній статус не дорівнює 'PENDING'.

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

30-секундна відповідь

«У MySQL (InnoDB) рівнем ізоляції за замовчуванням є Repeatable Read. Він реалізований за допомогою зв’язки MVCC (через ланцюжки версій в Undo Log) та Next-Key Locks (комбінація блокування запису і проміжку Gap Lock). Знімок даних (Read View) фіксується в момент виконання першого оператора читання і зберігається незмінним до кінця транзакції. Головна історична причина вибору цього рівня за замовчуванням — забезпечення детермінованості Statement-Based Replication. У сучасних високонавантажених системах з реплікацією за рядками (binlog_format=ROW) дефолт часто змінюють на Read Committed для усунення Gap Lock-дедлоків».

Чек-лист ключових понять

Дефолт MySQL InnoDB: Repeatable Read.
MVCC в InnoDB: Clustered Index (свіжі дані) + Undo Log (старі версії через DB_ROLL_PTR та DB_TRX_ID).
Next-Key Lock: Record Lock + Gap Lock. Запобігає вставці фантомів в інтервали між індексами.
Фантомна пастка: Виконання UPDATE на рядках, вставлених паралельною транзакцією, робить їх видимими для наступного SELECT поточної транзакції.
Highload рекомендація: Використовувати binlog_format = ROW + transaction-isolation = READ-COMMITTED для усунення дедлоків.

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

❌ «У MySQL за замовчуванням встановлено Read Committed, як і в PostgreSQL» (У MySQL дефолт — Repeatable Read).
❌ «InnoDB зберігає старі версії рядків прямо в сторінках таблиці» (Це архітектура PostgreSQL. В InnoDB старі версії зберігаються в Undo Log).
❌ «Звичайний SELECT накладає блокування Shared Locks на рядки» (Звичайний non-locking consistent read в InnoDB взагалі не бере блокувань, він читає Read View).
❌ «На рівні Repeatable Read у MySQL фантоми неможливі за жодних обставин» (Логіка UPDATE ... WHERE або блокуючого читання в обхід знімка може спровокувати фантоми).