Какой уровень изоляции по умолчанию в 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)

Реплика (Slave) читает 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 или блокирующего чтения в обход снимка может спровоцировать фантомы).