REPEATABLE READ

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

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

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

READ COMMITTED

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

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

Поскольку блокировка пробелов отключена, могут возникнуть проблемы с фиктивными строками, поскольку другие сеансы могут вставить новые строки в пробелы. Сведения о фиктивных строках см. в Разделе 14.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;

В этом случае таблица не имеет индексов, поэтому для поиска и сканирования индекса используется скрытый кластеризованный индекс для блокировки записей (см. Раздел 14.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 сохранил блокировки на всех строках), и не продолжает работу, пока первый 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 такие же, как включение устаревшей переменной innodb_locks_unsafe_for_binlog, за исключением следующих пунктов:

READ COMMITTED поэтому обеспечивает более точный и гибкий контроль, чем innodb_locks_unsafe_for_binlog.

READ UNCOMMITTED

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

SERIALIZABLE

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