Серия MySQL Deadlock — анализ распространенных сценариев блокировки

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

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

Ниже мы сначала объясним основные правила уровня изоляции, различные операторы SQL и текущие данные базы данных о создании блокировок, а затем проследим за конкретными сценариями блокировки SQL.

Влияние уровня изоляции на блокировку

Уровень изоляции MySQL блокировки воздействия, поэтомуПри анализе конкретной сцены блокировки необходимо сначала определить текущий уровень изоляции..

Чтение неподтвержденных (Read Uncommitted, позже именуемых RU): незафиксированные чтения могут быть прочитаны, и этот уровень изоляции в основном не используется, поэтому пока игнорируйте его.
Чтение зафиксировано (Read Committed, далее именуемое RC): имеется проблема с фантомным чтением.Добавить блокировку записи к данным, полученным текущим чтением.
Повторяемое чтение (далее именуемое RR): проблемы с фантомным чтением нет. Блокировки записей добавляются к данным, полученным при текущем чтении, а блокировки пробелов добавляются к задействованным диапазонам, чтобы предотвратить вставку новых данных, что приводит к фантомному чтению. читает.
Сериализуемый: от деградации управления параллелизмом MVCC до управления параллелизмом на основе блокировки чтение моментального снимка не выполняется, все чтение выполняется в данный момент, эффективность параллелизма резко падает, и это не рекомендуется.

Объясните здесь,RC всегда читает последнюю версию записи, а RR — это версия, которая читала запись в начале транзакции., хотя две версии чтения различны, обе они являются данными снимка и не будут блокироваться операциями записи, поэтому эта операция чтения называетсяСнимок чтения

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

SELECT ... LOCK IN SHARE MODE: добавить общий (S) замок
ВЫБЕРИТЕ... ДЛЯ ОБНОВЛЕНИЯ: добавьте эксклюзивную (X) блокировку
INSERT / UPDATE / DELETE: добавить эксклюзивную (X) блокировку

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

Влияние различных операторов SQL на блокировку

Различные операторы SQL, конечно, добавляют разные блокировки, которые в основном делятся на пять ситуаций:

Оператор SELECT ... обычно представляет собой моментальный снимок, прочитанный без блокировки;
Оператор SELECT ... LOCK IN SHARE MODE является текущим чтением с блокировкой S;
Оператор SELECT ... FOR UPDATE — это текущее чтение плюс блокировка X;
Общие операторы DML (такие как INSERT, DELETE, UPDATE) представляют собой текущее чтение плюс блокировку X;
Общие операторы DDL (такие как ALTER, CREATE и т. д.) добавляют блокировки на уровне таблицы, и эти операторы неявно фиксируются и не могут быть отменены.

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

Влияние текущих данных на блокировку

Данные в базе данных также будут влиять на блокировку при выполнении оператора SQL.

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

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

Анализ конкретных сценариев

Анализ конкретного сценария SQL в основном основан на статье He Dengcheng «Анализ обработки блокировок MySQL» и серии статей aneasystone, а также на их основе они обобщаются и систематизируются.

В качестве примера мы используем следующую таблицу книг, где id — первичный ключ, ISBN (номер книги) — вторичный уникальный индекс, Author (автор) — вторичный неуникальный индекс, а score (оценка) не имеет индекса.

Анализ блокировки оператора UPDATE

Далее, давайте сначала проанализируем ситуацию блокировки связанного с UPDATE SQL в случае использования относительно простого предложения where. Приведенные здесь принципы анализа также применяются к читаемым в данный момент операторам, таким как UPDATE, DELETE и SELECT... FOR UPDATE.

Кластерный индекс, запрос

Кластеризованный индекс — это индекс первичного ключа, хранящийся в специальной ссылке движка InnoDB."Индекс MySQL".

На следующем рисунке показаны блокировки при уровнях изоляции RC и RR при попадании оператора UPDATE book SET score = 9,2 WHERE ID = 10. Нет никакой разницы между двумя уровнями изоляции, оба относятся исключительно к блокировке записи индекса ID = 10. .

Кластеризованный индекс, промахи запроса

На следующем рисунке показана ситуация заблокировки под уровнем изоляции RR, когда набор обновлений набора книги = 9,2 где id = 16 оператор пропускается.

В рейтинге изоляции RC замок отсутствует; уровень изоляции RR в идентификаторе замка = плюс зазор 16 между двумя передними и задними индексами.

Стоит отметить, что блокировка пробела и блокировка пробела не конфликтуют друг с другом.Единственная функция блокировки пробела — предотвратить вставку новых строк другими транзакциями, что приводит к фантомным чтениям, поэтому нет никакой разницы между добавлением пробела. S-замок и добавление промежутка X-замка. .

Дополнительный уникальный индекс, обращение по запросу

На следующем рисунке показана блокировка, когда UPDATE book SET score = 9,2 WHERE ISBN = 'N0003' соответствует уровням изоляции RC и RR.

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

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

На следующем рисунке показана ситуация блокировки, когда оператор UPDATE book SET = 9,2 WHERE ISBN = 'N0008' отсутствует на уровне изоляции RR.На уровне изоляции RC оператор не будет заблокирован, если он отсутствует.

Поскольку N0008 превышает N0007, поэтому для блокировки (N0007, положительной бесконечности) этого раздела и индексы InnoDB обычно используются SUPRENUM Record и Infimum Record для представления записей верхних и нижних границ. Инфимум - это любая запись, которая будет меньше, чем значение страницы, и страница Supremum больше, чем наибольшее записанное значение, эти два записи при создании там страниц и не будут удалены.

Поэтому между N0007 и Suprenum Record добавляется блокировка пробела.

Почему бы не добавить блокировку GAP к первичному ключу? Добро пожаловать, чтобы оставить сообщение, чтобы выразить свои мысли.

Вторичный неуникальный индекс, обращение по запросу

На следующем рисунке показана ситуация блокировки, когда оценка UPDATE book SET = 9,2 WHERE Author = 'Tom' попадает под уровень изоляции RC.

Мы видим, что на уровне RC блокировка вторичного уникального индекса и вторичного неуникального индекса одинакова, и эксклюзивные блокировки записей добавляются к задействованным вторичным индексам и соответствующим индексам первичного ключа.

Однако на уровне изоляции RR случай, когда блокировка изменилась, но она не только добавляет вторичный индекс и индекс первичного ключа, но также добавляет три гэп-блокировки, заблокированные в неуникальном вторичном индексе. два значения индекса TOM.

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

Следует отметить, что, хотя на приведенном выше рисунке показаны четыре блокировки записи и три блокировки промежутка, на самом деле блокировка промежутка и блокировка записи с правой стороны будут объединены в блокировку Next-Key.

Таким образом, в реальной ситуации есть две блокировки Next-Key, блокировка пробела (Tom60, положительная бесконечность) и две блокировки записи.

Вторичный неуникальный индекс, запрос не выполняется

На следующем рисунке показана ситуация блокировки, когда UPDATE book SET score = 9,2 WHERE Author = 'Sarah' отсутствует на уровне изоляции RR, что добавит блокировку пробела между вторичными индексами Rose и Tom. Уровень изоляции RC не требует блокировки.

нет индекса

Если условие предложения Where не использует индекс, сканируется вся таблица, и ко всем данным на уровне изоляции RC применяется монопольная блокировка записей. На уровне изоляции RR в дополнение к блокировке записей между записями также добавляются блокировки пробелов. Как и выше, блокировка промежутка будет объединена с блокировкой записи слева, чтобы сформировать блокировку Next-Key.

На следующем рисунке показана ситуация блокировки оператора UPDATE book SET score = 9,2 WHERE score = 22 при двух уровнях изоляции.

Кластеризованный индекс, запрос диапазона

Описанные выше сценарии — это эквивалентные запросы предложений where, но как насчет блокировки запросов диапазона? Давайте помедленнее.

На следующем рисунке показана ситуация блокировки UPDATE book SET score = 9,2 WHERE ID

Подобно эквивалентному запросу в сценарии RC, к задействованным индексам ID = 10, ID = 18 и ID = 25 добавляются только эксклюзивные блокировки записей.

Он отличается под уровнем изоляции RR, который добавляет блокировку зазора, которая называется блокировкой следующего ключа с соответствующим блокировкой записи. Кроме того, он также добавляет следующие ключи замки на (25, 30] соответственно. Это очень особенное, а конкретные причины должны быть дополнительно изучены.

Средний индекс, диапазон запросов

На следующем рисунке показана ситуация блокировки книги UPDATE SET ISBN = N0001 WHERE, оценка

Изменить значение индекса

Случай, когда оператор UPDATE изменяет значение индекса, можно проанализировать отдельно.Во-первых, анализ блокировок предложения Where, как описано выше, с еще одним шагом блокировки части Set.

На следующем рисунке показана ситуация блокировки книги UPDATE SET Author = 'John' WHERE ID = 10 при уровнях изоляции RC и RR. В дополнение к блокировке идентификатора первичного ключа также будут блокировки для Боба (старое значение) и Джона (новое значение) для вторичного индекса.

Анализ блокировки оператора DELETE

Вообще говоря, блокировка DELETE мало чем отличается от блокировки SELECT FOR UPDATE или UPDATE.

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

Анализ блокировки оператора INSERT

Далее давайте посмотрим на блокировку оператора Insert.

Вставьте блокировку операторов в двух случаях:

Чтобы предотвратить фантомное чтение, если между записями есть блокировка промежутка, Вставка не может быть выполнена в это время;
Если вставленная запись и существующая запись вызывают конфликт уникальных ключей, вставка не может быть выполнена в это время;

За исключением указанных выше случаев, блокировки оператора Insert являются неявными блокировками. Неявная блокировка — это механизм отложенной блокировки, реализованный InnoDB для уменьшения количества блокировок.

Характерной чертой неявных блокировок является то, что они блокируются только тогда, когда могут возникнуть конфликты, что уменьшает количество блокировок. Кроме того, неявные блокировки предназначены для измененных записей B+Tree, поэтому все они являются блокировками типа записи и не могут быть блокировками пробелов или типами Next-Key.

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

Сначала добавьте замки Insert Intension Locks к вставленному промежутку.
- Если гэп был добавлен с помощью гэп-блокировки или блокировки Next-Key, блокировка не работает и переходит в ожидание;
- Если нет, блокировка выполнена успешно, что означает, что ее можно вставить;
Затем оцените, имеет ли вставленная запись уникальный ключ, и если да, выполните проверку ограничения уникальности.
- Если такое же значение ключа не существует, завершите вставку
- Если такое же значение ключа существует, определите, заблокировано ли значение ключа.
  - Если блокировки нет, определить, помечена ли запись на удаление
    - Если он помечен как удаленный, значит, транзакция была зафиксирована и времени на очистку нет, тогда добавляем S-блокировку для ожидания;
    - Если не будет отмеченное удаление, будет сообщена ошибка дубликата ключей;
  - Если есть блокировка, указывающая на то, что запись обрабатывается (добавление, удаление или обновление), а транзакция не была отправлена, плюс ожидание блокировки S;
Вставьте запись и добавьте блокировку записи X к записи;

постскриптум

Операторы SQL, описанные в этой статье, очень просты.Когда оператор SQL содержит несколько условий запроса, процесс анализа блокировки часто оказывается более сложным. Нам нужно использовать инструменты, связанные с MySQL, для анализа, а иногда даже запрашивать информацию журнала, связанную с MySQL, чтобы понять, какие блокировки добавляются к оператору или почему возникают взаимоблокировки.В следующей статье мы в основном узнаем об этом содержимом, пожалуйста, продолжайте обратите внимание .

Личный блог, добро пожаловать в игру