Какой уровень изоляции по умолчанию в MySQL

🟢 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 по умолчанию?

Statement-based Replication (историческая причина): В старых версиях MySQL репликация копировала SQL-запросы. Для корректной репликации на слейвах требовалась детерминированность, которую обеспечивал Repeatable Read. Без RR SELECT на слейве мог бы увидеть промежуточные состояния, которые мастер никогда не видел в своей оригинальной транзакции. Это привело бы к тому, что replicated UPDATE затронул бы другие строки.
Гарантия стабильности: Разработчики MySQL решили, что безопасность важнее производительности “из коробки”.
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

Переключите на Read Committed для write-heavy workloads — это снижает deadlock rate на 80-95%.
Используйте точечные запросы по primary key — избегает gap locks.
Укорачивайте транзакции — меньше window для lock contention.
Сортируйте обновления в одном порядке — предотвращает deadlocks (например, всегда по product_id ASC).
Настройте innodb_deadlock_detect = 0 для超高 throughput (при наличии application-level retries). Только для MySQL 5.7+.
Monitor undo log size — алерт при >10GB.
Используйте row-based replication (binlog_format=ROW) — не требует Repeatable Read.
Для reporting — используйте read replicas с отдельным connection pool.

Optimistic locking для hot-spot таблиц:

@Version
private Long version;
// Вместо SELECT ... FOR UPDATE

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 — это всегда хорошо” — могут вызывать unnecessary deadlocks
“Undo log не влияет на производительность” — при длинных RR транзакциях undo log растёт значительно

Связанные темы:

[[5. Что такое Repeatable Read]]
[[11. Какой уровень изоляции по умолчанию в PostgreSQL]]
[[2. Какие уровни изоляции транзакций существуют]]
[[10. Что такое потерянное обновление (Lost Update)]]