REPEATABLE READ

Это уровень изоляции по умолчанию для InnoDB. В рамках одной транзакции чтение выполняется с использованием установленного первого чтения. Это означает, что если вы выполните несколько обычных (без блокировок) SELECT инструкций в рамках одной транзакции, эти SELECT инструкции будут согласованы и друг с другом. Смотрите Раздел 17.7.2.3, «Согласованные чтения без блокировок».

Для (SELECT с FOR UPDATE или FOR SHARE), UPDATE и DELETE инструкций, блокировка зависит от того, использует ли инструкция уникальный индекс с уникальным условием поиска или условие поиска типа диапазона.

Не рекомендуется смешивать инструкции с блокировкой (UPDATE, INSERT, DELETE или SELECT ... FOR ...) с инструкциями без блокировки SELECT в одной транзакции REPEATABLE READ, потому что, как правило, в таких случаях вам требуется SERIALIZABLE. Это связано с тем, что инструкция без блокировки SELECT отображает состояние базы данных по состоянию на момент чтения, которое состоит из транзакций, завершенных до создания состояния чтения и до собственных записей текущей транзакции, в то время как инструкции с блокировкой используют последнее состояние базы данных для использования блокировки. В общем случае эти два различных состояния таблицы не согласованы между собой и сложны для анализа.

READ COMMITTED

Каждое согласованное чтение, даже в рамках одной транзакции, создаёт и считывает свой свежий снимок. Дополнительная информация о согласованных чтениях доступна в Разделе 17.7.2.3, «Согласованные чтения без блокировок».

Для чтений с блокировкой (SELECT с FOR UPDATE или FOR SHARE), UPDATE инструкций и DELETE инструкций, InnoDB блокирует только записи индекса, а не промежутки перед ними, что позволяет свободно вставлять новые записи рядом с заблокированными записями. Блокировка промежутков используется только для проверки ограничений внешнего ключа и проверки уникальности ключей.

Поскольку блокировка промежутков отключена, могут возникнуть проблемы с фантомными строками, поскольку другие сессии могут вставлять новые строки в промежутки. Дополнительную информацию о фантомных строках см. в Разделе 17.7.4, «Фантомные строки».

Поддерживается только протоколирование строк с бинарным логом в режиме изоляции READ COMMITTED. Если вы используете READ COMMITTED с binlog_format=MIXED, сервер автоматически использует протоколирование строк.

Использование READ COMMITTED имеет дополнительные эффекты:

Рассмотрим таблицу, созданную и заполненную следующим образом:

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

В этом случае таблица не имеет индексов, поэтому поиск и сканирование индексов используют скрытый кластеризованный индекс для блокировки записей (см. Раздел 17.6.2.1, «Кластеризованные и вторичные индексы») вместо индексированных столбцов.

Предположим, что одна сессия выполняет обновление UPDATE с помощью следующих инструкций:

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

Предположим также, что вторая сессия выполняет обновление UPDATE с помощью следующих инструкций после инструкций первой сессии:

# Session B
UPDATE t SET b = 4 WHERE b = 2;

По мере выполнения InnoDB каждой UPDATE, он сначала приобретает эксклюзивную блокировку для каждой строки, а затем определяет, нужно ли её изменять. Если InnoDB не изменяет строку, он снимает блокировку. В противном случае InnoDB сохраняет блокировку до конца транзакции. Это влияет на обработку транзакций следующим образом.

При использовании уровня изоляции по умолчанию REPEATABLE READ, первая UPDATE приобретает блокировку x для каждой строки, которую он считывает и не снимает ни одну из них:

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

Вторая UPDATE блокируется, как только пытается приобрести какие-либо блокировки (потому что первое обновление сохранило блокировки на всех строках), и не продолжает работу до тех пор, пока первое UPDATE не подтвердит или не откатит:

x-lock(1,2); block and wait for first UPDATE to commit or roll back

Если используется READ COMMITTED, первая UPDATE приобретает блокировку x для каждой строки, которую он считывает, и снимает эти блокировки для строк, которые он не изменяет:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

Для второй UPDATE, InnoDB выполняет “полусогласованное” чтение, возвращая последнюю подтвержденную версию каждой строки, которую он считывает MySQL, чтобы MySQL мог определить, соответствует ли строка условию WHERE UPDATE:

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

Однако, если условие WHERE включает индексированный столбец, и InnoDB использует индекс, при получении и сохранении блокировок записей учитывается только индексированный столбец. В приведенном ниже примере первое обновление UPDATE получает и сохраняет блокировку x для каждой строки, где b = 2. Второе обновление UPDATE блокируется при попытке получить блокировки x для тех же записей, поскольку оно также использует индекс, определенный для столбца b.

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

Уровень изоляции READ COMMITTED может быть задан при запуске или изменён во время работы. Во время работы он может быть установлен глобально для всех сессий или индивидуально для каждой сессии.

READ UNCOMMITTED

Инструкции SELECT выполняются без блокировки, но может быть использована возможная более ранняя версия строки. Таким образом, используя этот уровень изоляции, такие чтения не согласованы. Это также называется . В противном случае этот уровень изоляции работает как READ COMMITTED.

SERIALIZABLE

Этот уровень похож на REPEATABLE READ, но InnoDB неявным образом преобразует все обычные SELECT запросы в SELECT ... FOR SHARE, если autocommit отключен. Если autocommit включён, то SELECT является собственной транзакцией. Следовательно, она известна как только для чтения и может быть сериализована, если выполняется как согласованное (без блокировок) чтение, и не должна блокироваться другими транзакциями. (Чтобы заставить обычный SELECT заблокироваться, если другие транзакции изменили выбранные строки, отключите autocommit.)

Операции DML, которые считывают данные из таблиц MySQL (через список соединений или подзапрос), но не изменяют их, не приобретают блокировки чтения на таблицах MySQL, независимо от уровня изоляции. Более подробную информацию можно найти в разделе Совместимость таблиц разрешений.