Анализ уровня изоляции MySQL InnoDB

MySQL

предисловие

Или старые правила, сначала задайте два вопроса, которые нужно решить:

  • Каковы уровни изоляции транзакций в механизме хранения MySQL InnoDB?
  • Каков механизм реализации соответствующего уровня изоляции?

В этой статье мы ответим на два вышеуказанных вопроса, проанализируем уровень изоляции транзакции и соответствующий механизм блокировки.

Введение в изоляцию

Изоляция в основном означает, что система базы данных предоставляет определенный механизм изоляции, чтобы гарантировать выполнение транзакций в «независимой» среде, на которую не влияют параллельные внешние операции, а это означает, что при одновременном выполнении нескольких транзакций выполнение одной транзакции не должно выполняться. влиять на выполнение других транзакций.

Внедрение 4 уровней изоляции

существуетстандарт SQLСуществует 4 уровня изоляции, определенные в , а именно:

  • Read uncommitted: незафиксированное чтение, изменения в транзакции, даже если они не зафиксированы, видны другим транзакциям. Грязные чтения существуют
  • Read committed: Commit read, уровень изоляции по умолчанию для большинства систем баз данных (MySQL нет), транзакция от начала до фиксации, сделанные изменения не видны другим транзакциям. Решайте грязные чтения, фантомные чтения и неповторяющиеся чтения
  • repeatable read: Повторяющееся чтение, этот уровень гарантирует, что результаты чтения одной и той же записи несколько раз в одной и той же транзакции непротиворечивы. Решите грязные чтения и неповторяющиеся чтения, теоретически есть фантомные чтения, но фантомные чтения разрешаются в движке InnoDB.
  • Serializable: Serializable, заставляет транзакцию выполняться последовательно.

Вышеупомянутые четыре уровня изоляции определены стандартом SQL, но в разных механизмах хранения реализованы разные уровни изоляции. В этой статье в основном представлен уровень изоляции в механизме хранения MySQL InnoDB,В механизме хранения InnoDB Repeatable Read является уровнем изоляции транзакций по умолчанию, при этом реализация механизма основана на протоколе управления многоверсионным параллелизмом — MVCC (Multi-Version Concurrency Control), который решает проблему фантомные чтения, конечно, грязные чтения и неповторяемые чтения.Чтения не существует. Самым большим преимуществом MVCC является то, что чтение не блокируется, а чтение и запись не конфликтуют, что значительно увеличивает одновременную производительность системы.

Read uncommitted

Незафиксированное чтение.В этом случае транзакция A может видеть незафиксированные данные другой транзакции B. Если транзакция B в это время откатывается, то транзакция A получает грязные данные, в чем и заключается смысл грязного чтения. Этот уровень изоляции обычно не используется в MySQL InnoDB и не будет подробно объяснен.

Read Committed

Чтение фиксации, любые изменения, сделанные транзакцией с самого начала до момента фиксации, невидимы для других транзакций. Проблема грязного чтения решена, но есть феномен фантомного чтения.

Так называемое фантомное чтение означает, что в рамках одной и той же транзакции выполнение одного и того же SQL-оператора дважды подряд может привести к разным результатам.Второй SQL-оператор может возвращать строки, которых раньше не было, то есть «фантомные строки»..

Например следующий пример:

  1. Сначала создайте таблицу,
CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB

insert into t(a) values(1);
insert into t(a) values(2);
insert into t(a) values(4);
  1. Выполните транзакцию 1 и транзакцию 2 соответственно:

read_committed

Из приведенного выше рисунка видно, что уровень изоляции Read Committed имеет явление фантомного чтения. На самом деле Read Committed также может иметь проблему неповторяющегося чтения,Неповторяющееся чтение означает, что несколько запросов выполняются к записям строк в соответствии с одними и теми же условиями внутри транзакции, но результаты данных запроса несовместимы, поскольку данные интервала запроса изменяются другими транзакциями..

На самом деле неповторяющееся чтение немного похоже на фантомное чтение.Первый подчеркивает, что результаты данных одной и той же строки записей различны, а второй подчеркивает, что наборы результатов, возвращаемые несколькими запросами, различны, увеличиваются или уменьшаются..

Repeatable Read

Повторяющееся чтение, этот уровень гарантирует, что результаты чтения одной и той же записи несколько раз в одной и той же транзакции будут согласованными, и решает проблемы как фантомного чтения, так и неповторяющегося чтения в механизме хранения InnoDB. Что касается того, как InnoDB решает проблему фантомных и неповторяемых чтений, будет объявлено последующее содержание.

Сериализуемый (сериализуемый)

Serializable — это самый высокий уровень изоляции. Он позволяет избежать проблемы фантомного чтения, заставляя транзакции выполняться последовательно. Однако Serializable будет блокировать каждую прочитанную строку данных, поэтому это может привести к большому количеству тайм-аутов и проблемам блокировки блокировок. Поэтому параллелизм резко падает, и не рекомендуется использовать InnoDB в MySQL

Анализ блокировки на уровне изоляции Read Committed

Реализация уровня изоляции неотделима от механизма блокировки, поэтому необходимо ввести концепцию блокировки.Во-первых, давайте посмотрим на две стандартные блокировки на уровне строк, предоставляемые механизмом хранения InnoDB:

  • Общий замок (S Lock): также известная как блокировка чтения, она позволяет нескольким транзакциям одновременно читать один и тот же ресурс, не мешая друг другу. То есть, если транзакция T добавляет общую блокировку к данным A, другие транзакции могут добавлять только общие блокировки к A, но не исключительные блокировки, и могут только читать данные, но не изменять данные.
  • Эксклюзивный замок (X Lock): Также известная как блокировка записи, если транзакция T добавляет эксклюзивную блокировку к данным A, другие транзакции не могут добавлять какие-либо блокировки к A, а транзакция, которая получает монопольную блокировку, может как читать, так и изменять данные.

Примечание. Общие блокировки и монопольные блокировки несовместимы.

Механизм хранения MySQL InnoDB использует управление параллелизмом с несколькими версиями, отсутствие блокировок чтения и конфликтов между чтением и записью, если только блокировка чтения не отображается в определенном сценарии (не для изучения здесь).. В этом разделе в основном анализируется ситуация блокировки на уровне изоляции Read Committed.При действии MVCC это обычно операция записи плюс блокировка X.

Операция блокировки тесно связана с индексом.При выполнении анализа блокировки оператора SQL следует также тщательно учитывать тип индекса в столбце атрибутов. Предположим, есть таблица данных t1, есть два столбца, столбец name и столбец id, вставлено несколько фрагментов данных, и нет четкой ситуации с индексом:

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);

Ниже выполняется оператор SQL, удаляющий из t1, где id = 10. Уровень изоляции здесь установлен на Read Committed. Статус индекса столбца id не может быть известен из этого оператора SQL, поэтому его необходимо обсудить отдельно:

  • Столбец id является первичным ключом
  • Столбец id является вторичным уникальным индексом
  • Столбец id является вторичным неуникальным индексом
  • В столбце id нет индекса

Ниже приводится краткое описание ситуации с блокировкой в ​​вышеуказанных ситуациях.Подробнее см. в статье базы Даниэля:Анализ обработки блокировки MySQL

Столбец id является первичным ключом

Когда id является первичным ключом, приведенному выше SQL нужно только добавить блокировку X на запись с id = 10.

Столбец id является вторичным уникальным индексом

Если столбец идентификатора является уникальным индексом, а первичный ключ является столбцом имени, тогда SQL должен добавить две блокировки X, одну, соответствующую записи с идентификатором = 10 в индексе идентификатора, а другую блокировку, соответствующую [имя = " a", id=10] записи

Столбец id является вторичным неуникальным индексом

При наличии неуникального индекса в столбце id все соответствующие записи, удовлетворяющие условиям SQL-запроса, будут заблокированы, и в то же время будут заблокированы записи этих записей в индексе первичного ключа.

В столбце id нет индекса

Если в столбце id нет индекса, SQL будет фильтровать через полное сканирование кластеризованного индекса.Поскольку фильтрация выполняется на уровне сервера MySQL, каждая запись будет заблокирована с помощью X независимо от того, соответствует ли она условиям.

Анализ блокировки при уровне изоляции повторяющегося чтения

Как упоминалось ранее, на уровне изоляции Repeatable Read механизм хранения InnoDB решает проблему фантомных и неповторяемых чтений.Каков конкретный принцип?

Ранее я кратко познакомил вас с блокировками строк в InnoDB. Давайте рассмотрим три алгоритма блокировки нисходящей линии связи:

  • Блокировка записи: блокировка одной индексной записи, то есть добавление блокировки X
  • Блокировка промежутка: блокировка промежутка, блокирует диапазон, но не содержит саму запись
  • Next-Key Lock: Gap Lock + Record Lock, блокирует диапазон и блокирует себя.

Блокировка записи всегда блокирует записи индекса.Если механизм хранения InnoDB не устанавливает индекс при построении таблицы, InnoDB будет использовать неявный первичный ключ для блокировки. (Если таблица не определяет первичный ключ, InnoDB по умолчанию добавит неявный индекс первичного ключа)

Next-Key Lock — это алгоритм блокировки, который сочетает в себе Gap Lock и Record Lock, Например, если столбец индекса имеет 4 значения 10, 11, 13 и 20, то диапазон, в котором индекс может быть Блокировка следующего ключа, равен :

  • ($-\infty$,10)
  • (10,11]
  • (11,13]
  • (13,20]
  • (20,$+\infty$)

Следует отметить, что когда индекс запроса содержит уникальный атрибут, то есть индекс первичного ключа или уникальный индекс, механизм хранения InnoDB оптимизирует блокировку следующего ключа и понизит ее до блокировки записи, то есть только сам индекс будет заблокирован, как правило, с блокировкой X.

Целью механизма Next-Key Lock является решение проблемы фантомного чтения, в основном, когда индекс столбца запроса не является уникальным индексом. Проиллюстрируем на следующем примере:

  1. Сначала создайте тестовую таблицу t1, name — индекс первичного ключа, id — неуникальный индекс, то есть вспомогательный индекс
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `id_indx` (`id`)
) ENGINE=InnoDB

insert into t1(name,id) values("a",10);
insert into t1(name,id) values("b",11);
insert into t1(name,id) values("c",13);
insert into t1(name,id) values("d",20);
  1. Выполнить удаление из t1, где id = 11, состояние блокировки показано на следующем рисунке.

gap_lock

Эта операция удаления SQL выполняется через идентификатор столбца индекса, который не является уникальным индексом, поэтому он использует традиционный ключ Next-Key. Технология блокировки блокирует, а поскольку существует два индекса первичного ключа и вспомогательный индекс, их необходимо блокировать отдельно. Для индекса первичного ключа (то есть кластеризованного индекса) он только добавляет блокировку записи к индексу с именем столбца = "b", который на самом деле является блокировкой X.

Для неуникальных индексов добавляется Next-Key Lock, диапазон блокировки (10,11), и к нему добавляется Gap Lock (гэп-блокировка), GAP-блокировка фактически добавляется между двумя граничными записями. Следует также отметить, что InnoDB также добавляет блокировку пробела к следующему значению ключа вторичного индекса, то есть блокировка пробела добавляется между (11, 13). К самому значению 11 добавьте Record Lock, то есть X lock.

Если в это время будет открыта другая транзакция для выполнения следующего оператора, она будет заблокирована:

1. select * from t1 where name = "b";
2. insert into t1(name,id) values("c",12);

Например, первый оператор не может быть выполнен, поскольку блокировка X была добавлена ​​к значению столбца name="b" в кластеризованном индексе в начальной транзакции. Таким образом, выполнение будет заблокировано. Второй SQL также не может быть выполнен, вставленное значение 12 находится в заблокированном диапазоне (11, 13) и его необходимо заблокировать и подождать.

Поэтому, как видно из вышеприведенного примера, роль GAP Lock заключается в предотвращении вставки записей в один и тот же диапазон несколькими транзакциями, что эффективно решает проблему фантомного чтения.

Сводка уровней изоляции

Резюме нижеПод механизмом хранения InnoDBРазличные уровни изоляции для:

уровень изоляции Возможность грязного чтения возможность неповторяемого чтения Возможность фантомного чтения блокировка чтения
Read Uncommitted Yes Yes Yes No
Read Committed No Yes Yes No
Repeatable Read No No No No
Serializable No No No Yes

Ссылки и благодарности

Категории