Java Interview Questions

Головна / Усі відповіді / Транзакції

Питання 12 · Розділ 11

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

В MySQL (при використанні рушія InnoDB) рівнем ізоляції за замовчуванням є Repeatable Read.

Ukrainian Транзакції Вихідний Markdown
Мовні версії: English Russian Ukrainian

🟢 Junior Level

В MySQL (при використанні рушія InnoDB) рівнем ізоляції за замовчуванням є Repeatable Read.

Що це означає простими словами: В рамках однієї транзакції ви завжди бачите одні й ті самі дані, навіть якщо інші транзакції в цей час їх змінили. Ніби ви зробили “фотографію” даних на початку транзакції і працюєте з цією фотографією.

Проста аналогія: Ви завантажили PDF-версію статті і читаєте її. Поки ви читаєте, автор може редагувати статтю на сайті, але ви бачите свою завантажену версію без змін.

SQL-приклад:

-- Початковий стан: account.balance = 1000

-- Транзакція 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- бачить 1000

-- Транзакція 2 (паралельно)
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 1;
COMMIT;

-- Транзакція 1 продовжує:
SELECT balance FROM accounts WHERE id = 1;  -- ВСЕ ЩЕ бачить 1000!
COMMIT;

Що захищено на Repeatable Read:

Аномалія Можлива?
Dirty Read (Брудне читання) Ні
Non-repeatable Read Ні
Phantom Read Ні для запитів за індексом (gap/next-key locks). Для full table scan або non-indexed range query фантоми можливі!

Важлива відмінність від PostgreSQL: В PostgreSQL дефолт — Read Committed, а в MySQL — Repeatable Read. MySQL обирає суворіший рівень.

Як змінити:

-- В конфізі (my.cnf)
[mysqld]
transaction-isolation = READ-COMMITTED

-- В рантаймі
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

🟡 Middle Level

Як це працює всередині

MySQL/InnoDB реалізує Repeatable Read через комбінацію:

  • MVCC (Multi-Version Concurrency Control — зберігання кількох версій рядків)
  • Next-Key Locking (Record Lock + Gap Lock). Gap = проміжок між індексними записами. Gap Lock блокує вставку нових рядків у цей проміжок.
MVCC (Multi-Version Concurrency Control):
  - Кожен рядок має приховані поля:
    * DB_TRX_ID: ID транзакції, яка змінила рядок
    * DB_ROLL_PTR: вказівник на попередню версію (в undo log)
    * DB_ROW_ID: унікальний ідентифікатор

  - При першому SELECT в транзакції створюється snapshot
  - Всі наступні SELECT використовують цей самий snapshot
  - Snapshot визначає, які версії рядків "видимі"

Senior-інсайт: InnoDB на Repeatable Read захищає не тільки від неповторюваного читання, але й від фантомного читання — це виходить за рамки стандарту ANSI SQL. Досягається це через Gap Locks та Next-Key Locks.

Next-Key Locking (унікальна особливість MySQL)

SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;

InnoDB блокує:
1. Record locks: рядки з id = 10, 11, ..., 20
2. Gap locks: проміжки (9,10), (10,11), ..., (19,20), (20,21)
3. Next-key lock = record lock + gap lock

Результат: Неможливо INSERT INTO orders (id=15) — gap locked!

Це запобігає фантомному читанню на Repeatable Read, але створює побічний ефект — більше блокувань і вища ймовірність deadlocks.

Чому MySQL обрав Repeatable Read за замовчуванням?

  1. Statement-based Replication (історична причина): В старих версіях MySQL реплікація копіювала SQL-запити. Для коректної реплікації на слейвах вимагалась детермінованість, яку забезпечував Repeatable Read. Без RR SELECT на слейві міг би побачити проміжні стани, які мастер ніколи не бачив у своїй оригінальній транзакції. Це призвело б до того, що replicated UPDATE торкнувся б інших рядків.
  2. Гарантія стабільності: Розробники MySQL вирішили, що безпека важливіша за продуктивність “з коробки”.
  3. Backward compatibility: Зміна дефолту зламала б існуючі застосунки.

Практичне застосування

Коли Repeatable Read корисний:

  • Застосунки, що мігрують з MySQL на інші СУБД (менше змін у поведінці)
  • Складні звіти, що вимагають консистентного snapshot
  • Історична statement-based реплікація

Коли варто переключити на Read Committed:

  • Високонавантажені системи з частими оновленнями
  • Мікросервіси, де кожна транзакція коротка
  • Row-based реплікація (сучасний підхід, не вимагає RR)

Типові помилки

Помилка Наслідок Рішення
Припущення, що всі СУБД мають однаковий дефолт Різна поведінка при міграції з PostgreSQL на MySQL Знати дефолти кожної СУБД
Ігнорування gap lock side effects Несподівані deadlocks Використовувати Read Committed для hot-spot таблиць
Довга транзакція на Repeatable Read Undo log росте, старі версії рядків не видаляються Укоротити транзакцію, використати Read Committed
Gap lock на великому діапазоні Блокування безлічі не пов’язаних рядків Використовувати точкові запити по primary key

Порівняння: MySQL vs PostgreSQL default

Характеристика MySQL (InnoDB) PostgreSQL
Default рівень Repeatable Read Read Committed
MVCC Undo log Dead tuples в таблиці
Фантоми на RR Ні (Next-Key Locks) Ні (MVCC snapshot)
Deadlocks Частіше (gap locks) Рідше
Undo overhead Росте при довгих tx Table bloat при довгих tx
VACUUM аналог Purge thread Autovacuum

Коли НЕ варто використовувати Repeatable Read в MySQL

  • Row-based replication (MySQL 5.7+) — Read Committed безпечний і швидший
  • High-write workload — gap locks створюють зайвий contention
  • Мікросервіси — короткі транзакції, RR оверхед надлишковий

🔴 Senior Level

Internal Implementation: InnoDB MVCC та Lock System

MVCC через Undo Log

InnoDB зберігає версії рядків в undo log (не в таблиці, на відміну від PostgreSQL):

Структура clustered index record:
  +------------------+
  | DB_ROW_ID (6B)   |  ← прихований PK, якщо немає явного
  | DB_TRX_ID (6B)   |  ← transaction ID, що змінила рядок
  | DB_ROLL_PTR (7B) |  ← вказівник на undo log record
  | data columns...  |
  +------------------+

Undo log record:
  +------------------+
  | type: INSERT/UPDATE |
  | table_id           |
  | prev_version_ptr   |  ← ланцюжок версій
  | old_values...      |
  +------------------+

При SELECT:
  1. Береться read view (snapshot) з up_limit_id та low_limit_id
  2. Якщо DB_TRX_ID < up_limit_id → версія видима
  3. Якщо DB_TRX_ID >= low_limit_id → версія НЕ видима
  4. Якщо up_limit_id <= DB_TRX_ID < low_limit_id → перевіряється commits
  5. Якщо не видима → слідувати по DB_ROLL_PTR до попередньої версії

Read View структура:

struct ReadView {
    trx_id_t up_limit_id;     // Найстаріша активна транзакція
    trx_id_t low_limit_id;    // ID наступної (ще не створеної) транзакції
    trx_id_t creator_trx_id;  // ID транзакції, що створила цей read view
    ids_t m_ids;              // Set IDs активних транзакцій
};

Next-Key Locking: Internal Structure

InnoDB lock manager зберігає блокування в hash table:

struct lock_t {
    trx_t* trx;        // транзакція-власник
    uint32_t type_mode; // LOCK_REC | LOCK_GAP | LOCK_ORDINARY
    hash_node_t hash;   // для hash table
    // ...
};

LOCK_ORDINARY = next-key lock (record + gap)
LOCK_GAP = тільки gap (без record)
LOCK_REC = тільки record (без gap)
LOCK_INSERT = блокування для INSERT операції

Gap lock escalation:

-- Запит без індексу:
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- Немає індексу на status → full table scan
-- InnoDB блокує ВСІ рядки + ВСІ gaps → table-level lock!
-- Це називається "gap lock on entire table"

-- З індексом:
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- (INDEX idx_status (status))

-- InnoDB блокує тільки рядки з status='pending' + adjacent gaps

Архітектурні Trade-offs

Підхід A: Repeatable Read (MySQL дефолт)

  • ✅ Плюси: Консистентний snapshot, захист від фантомів, сумісність зі statement-based replication
  • ❌ Мінуси: Undo log bloat (old versions retained), gap lock deadlocks, нижча throughput при write-heavy workload
  • Підходить для: legacy systems, reporting, migration compatibility

Підхід B: Read Committed

  • ✅ Плюси: Менше блокувань (немає gap locks для SELECT), undo log purge швидше, вища throughput
  • ❌ Мінуси: Non-repeatable reads, phantom reads, несумісність зі statement-based replication
  • Підходить для: high-write systems, row-based replication, мікросервіси

Підхід C: Serializable

  • ✅ Плюси: Повна ACID
  • ❌ Мінуси: Implicit LOCK IN SHARE MODE на всіх SELECT, максимальне блокування
  • Підходить для: legacy financial systems (рідко використовується в production)

Edge Cases та Corner Cases

1. Gap Lock на неіснуючих рядках:

-- В таблиці: id = 10, 20, 30
BEGIN;
SELECT * FROM t WHERE id = 15 FOR UPDATE;  -- нічого не знайдено
-- АЛЕ: gap lock встановлено на діапазон (10, 20)!

-- Інша транзакція:
INSERT INTO t (id, val) VALUES (15, 'test');
-- BLOCKED! Чекає release gap lock

2. Deadlock через Gap Locks:

T1: SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE;  -- locks gaps (10,20)
T2: SELECT * FROM t WHERE id BETWEEN 15 AND 25 FOR UPDATE;  -- locks gaps (15,25)
T1: INSERT INTO t (id) VALUES (17);  -- чекає T2 gap lock
T2: INSERT INTO t (id) VALUES (18);  -- чекає T1 gap lock
→ DEADLOCK

Чому deadlock: gap locks не сумісні для INSERT. Коли T1 тримає gap (10,20),
а T2 — gap (15,25), кожен INSERT в регіон, що перетинається (15-20), чекає
звільнення gap lock іншої транзакції → circular wait.

3. Undo Log Explosion:

T1: BEGIN; (Repeatable Read)
T1: SELECT * FROM accounts;  -- створює read view

-- 10,000 UPDATE на accounts від інших транзакцій
-- Кожен UPDATE створює undo log record
-- T1's read view не дає purge видалити старі версії

T1: SELECT * FROM accounts;  -- все ще бачить старі дані
-- Undo log size: може вирости до GBs за довгу транзакцію

4. Implicit Locking та Gap Locks:

-- InnoDB встановлює implicit lock при INSERT
-- Інша транзакція, що робить SELECT ... LOCK IN SHARE MODE,
-- може створити gap lock на вже вставлений (але не закомічений) рядок
-- Це призводить до unexpected blocking

5. Auto-Increment Locking (MySQL 5.7 vs 8.0):

MySQL 5.7: AUTO-INC lock — table-level lock на час INSERT (при `innodb_autoinc_lock_mode = 1` — traditional)
MySQL 8.0: AUTO-INC lock — released одразу після генерації ID (при `innodb_autoinc_lock_mode = 2` — interleaved, default в 8.0+)

При Read Committed в MySQL 8.0:
  T1: INSERT INTO t (val) VALUES ('a');  -- id=1
  T2: INSERT INTO t (val) VALUES ('b');  -- id=2
  T1: ROLLBACK;
  T2: COMMIT;

  Результат: id = 2, але next auto-inc = 3 (hole!)

6. Consistent Read vs Locking Read:

-- Repeatable Read:
SELECT * FROM t WHERE id = 1;       -- consistent read (snapshot, no lock)
SELECT * FROM t WHERE id = 1 FOR UPDATE;  -- locking read (current value, locks row)

-- Важливо: FOR UPDATE ОНОВЛЮЄ read view!
-- Після FOR UPDATE, наступні consistent reads бачать ПОТОЧНІ дані

Performance Implications

Рівень Latency (p50) Latency (p99) Throughput Lock overhead
Read Committed 1-2ms 3-10ms 40,000+ TPS Мінімальний
Repeatable Read 1-3ms 5-20ms 30,000-35,000 TPS Gap locks: 5-15% overhead
Serializable 10-50ms 100-500ms 3,000-8,000 TPS Table-level locks possible

Конкретні цифри (MySQL 8.0, InnoDB, 8 cores, NVMe, sysbench):

  • Read Committed, read-only: ~45,000 TPS
  • Repeatable Read, read-only: ~42,000 TPS (7% overhead від gap lock management)
  • Read Committed, 50/50 read/write: ~20,000 TPS
  • Repeatable Read, 50/50 read/write: ~15,000 TPS (25% зниження через gap lock contention)
  • Deadlock rate (RR, 100 concurrent writers): ~0.5-2%

Undo log impact:

  • При RR з довгими транзакціями: undo log може вирости до 5-10x від normal size
  • Purge thread не може видалити версії, видимі active read views
  • Disk space: undo tablespace може вирости до 100GB+ при long-running reporting queries

Memory Implications

  • Read View: ~100-500 bytes (залежить від кількості активних транзакцій). Аллокується при першому SELECT.
  • Gap locks: ~100 bytes per lock в lock hash table. При 10,000 gap locks = ~1MB.
  • Undo log retention: Кожна версія рядка ~50-200 bytes. При 1M оновлень = 50-200MB retained undo.
  • InnoDB Buffer Pool: Старі версії рядків займають місце в buffer pool → eviction of hot pages → increased disk I/O.

Concurrency Aspects

Deadlock detection:

InnoDB deadlock detector:
  - Запускається при кожному lock wait timeout
  - Будує wait-for graph: транзакція → чекає → транзакція → ...
  - Пошук циклу: DFS, O(V + E)
  - Жертва: транзакція з найменшим "work" (менше undo log records)
  - Rollback жертви: пропорційно кількості undo records

Deadlock frequency pattern:

Read Committed:  deadlock rate ~0.01-0.1% (тільки record locks)
Repeatable Read: deadlock rate ~0.5-5% (record + gap locks)
Serializable:    deadlock rate ~5-20% (table-level locks possible)

Real Production Scenario

Ситуація: E-commerce платформа (2023), MySQL 8.0, обробка замовлень 3000/год.

Проблема: В години пік (Black Friday) deadlock rate виріс до 15%, замовлення падали з помилкою:

Deadlock found when trying to get lock; try restarting transaction

Root cause: Repeatable Read + Gap Locks на order_items:

@Transactional  // Repeatable Read за замовчуванням
public void processOrder(Long orderId) {
    Order order = orderRepo.findById(orderId);

    // InnoDB: gap lock на order_items по order_id
    List<OrderItem> items = orderItemRepo.findByOrderId(orderId);

    // Обробка кожної позиції
    for (OrderItem item : items) {
        // UPDATE з gap lock на product inventory
        productRepo.decrementStock(item.getProductId(), item.getQuantity());
    }

    order.setStatus("PROCESSED");
    orderRepo.save(order);
}

При 3000 замовлень/год, множинні транзакції блокували overlapping ranges в order_items та product_inventory. Gap locks створювали cyclic dependencies → deadlocks.

Рішення 1 (швидке): Переключення на Read Committed:

SET GLOBAL transaction_isolation = 'READ-COMMITTED';
-- my.cnf: transaction-isolation = READ-COMMITTED

Рішення 2 (довгострокове): Усунення gap lock тригерів:

@Transactional
public void processOrder(Long orderId) {
    // Точковий SELECT по primary key — немає gap lock
    Order order = orderRepo.findById(orderId);

    // Використати atomic update замість read-modify-write
    productRepo.decrementStockWhereSufficient(itemId, quantity);

    order.setStatus("PROCESSED");
    orderRepo.save(order);
}

Результат:

  • Deadlock rate: з 15% до 0.1%
  • Throughput: +25% (менше блокувань)
  • Latency p99: з 500ms до 50ms

Monitoring та Діагностика

MySQL internal stats:

-- Поточні блокування
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- Deadlock лог (останній deadlock)
SHOW ENGINE INNODB STATUS\G
-- Шукайте секцію "LATEST DETECTED DEADLOCK"

-- Transaction isolation level
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

-- Undo log size
SELECT
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.tables
WHERE table_name LIKE '%undo%';

-- Long-running transactions
SELECT
    trx_id, trx_state, trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Application-level monitoring:

// Micrometer: track deadlocks
Counter.builder("mysql.deadlocks")
    .tag("table", "order_items")
    .register(meterRegistry);

// Track isolation level usage
Counter.builder("db.transaction.isolation")
    .tag("level", "repeatable_read")
    .tag("operation", "process_order")
    .register(meterRegistry);

Percona Monitoring:

-- Percona Server: extended InnoDB metrics
SELECT * FROM information_schema.innodb_metrics
WHERE name LIKE '%lock%' OR name LIKE '%deadlock%';

Best Practices для Highload

  1. Переключіть на Read Committed для write-heavy workloads — це знижує deadlock rate на 80-95%.
  2. Використовуйте точкові запити по primary key — уникає gap locks.
  3. Укорочуйте транзакції — менше вікно для lock contention.
  4. Сортуйте оновлення в одному порядку — запобігає deadlocks (наприклад, завжди по product_id ASC).
  5. Налаштуйте innodb_deadlock_detect = 0 для надвисокої throughput (за наявності application-level retries). Тільки для MySQL 5.7+.
  6. Monitor undo log size — алерт при >10GB.
  7. Використовуйте row-based replication (binlog_format=ROW) — не вимагає Repeatable Read.
  8. Для reporting — використовуйте read replicas з окремим connection pool.
  9. Optimistic locking для hot-spot таблиць: 
    @Version
private Long version;
// Замість SELECT ... FOR UPDATE
  10. Tune InnoDB lock settings: 
    innodb_lock_wait_timeout = 10  # default 50, знизити для faster deadlock detection
innodb_table_locks = ON        # для explicit LOCK TABLES

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

Обов’язково знати:

  • MySQL (InnoDB) дефолт: Repeatable Read — стабільний snapshot на всю транзакцію
  • MySQL запобігає фантомам на RR через Next-Key Locking (Record + Gap Lock)
  • MVCC через undo log (не dead tuples в таблиці, як в PostgreSQL)
  • Історична причина: RR для statement-based replication детермінованості
  • Gap Locks — причина частіших deadlocks в MySQL vs PostgreSQL
  • Для write-heavy workload рекомендується переключити на Read Committed

Часті уточнюючі запитання:

  • Чому MySQL обрав RR за замовчуванням? — Statement-based replication, stability, backward compatibility
  • Чим MVCC в MySQL відрізняється від PostgreSQL? — MySQL: undo log, PG: dead tuples в тих самих сторінках
  • Що станеться при Gap Lock deadlock? — InnoDB deadlock detector обирає жертву (менше undo records)
  • Коли переключити на Read Committed? — Row-based replication, high-write workload, мікросервіси

Червоні прапорці (НЕ говорити):

  • “MySQL і PostgreSQL мають однаковий дефолт” — MySQL: RR, PG: RC
  • “Gap Locks — це завжди добре” — можуть викликати непотрібні deadlocks
  • “Undo log не впливає на продуктивність” — при довгих RR транзакціях undo log росте значно

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

  • [[5. Що таке Repeatable Read]]
  • [[11. Який рівень ізоляції за замовчуванням в PostgreSQL]]
  • [[2. Які рівні ізоляції транзакцій існують]]
  • [[10. Що таке втрачене оновлення (Lost Update)]]