Механизм блокировки MySQL — здесь есть все, что вы хотите знать!

MySQL

Во-первых, тип замка.

блокировка строки

  • Общая блокировка (S Lock) позволяет транзакции читать строку данных.
  • Эксклюзивная блокировка (X Lock) позволяет транзакции записывать строку данных.

Блокировка таблицы (блокировка намерения)

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

  • Транзакция намеренной общей блокировки (IS Lock) хочет получить общие блокировки для определенных строк в таблице.

  • Транзакции с преднамеренной монопольной блокировкой (IX Lock) хотят получить монопольные блокировки для определенных строк в таблице.

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

Если заблокированный объект рассматривается как дерево, то для блокировки объекта самого низкого уровня, то есть для блокировки самого мелкозернистого объекта, сначала необходимо заблокировать крупнозернистый объект. Например, на приведенном выше рисунке, если вам нужно заблокировать X в записи r на странице, вам нужно намеренно заблокировать базу данных A, таблицу и страницу с помощью IX и, наконец, заблокировать запись r с помощью X. Если какая-либо из этих частей приводит к ожиданию, операция должна дождаться завершения грубой блокировки. Например, перед добавлением блокировки X к записи r транзакция уже выполнила блокировку таблицы S для таблицы 1, затем уже есть блокировка S для таблицы 1, а затем транзакции необходимо добавить блокировку IX к таблице 1 для записи r. , поэтому транзакции необходимо дождаться завершения операции блокировки таблицы.

Что именно делает блокировка намерений?

Блокировка намерения Innodb в основном используется, когда сосуществуют несколько блокировок детализации. Например, транзакция A хочет добавить к таблице блокировку S. Если строка в таблице была заблокирована транзакцией B, то приложение для блокировки также должно быть заблокировано. Если в таблице много данных, накладные расходы на проверку флага блокировки построчно будут большими, что повлияет на производительность системы. Чтобы решить эту проблему, можно ввести новый тип блокировки на уровне таблицы для представления ситуации блокировки строки, к которой она принадлежит, что приводит к понятию «блокировка по намерению».

Например, если в таблице 100 миллионов записей и транзакция А имеет блокировки строк для нескольких записей, то транзакции Б необходимо добавить в эту таблицу блокировки на уровне таблицы. Таблица Узнайте, заблокированы ли 100 миллионов записей. Если существует блокировка по намерению, то если транзакция A добавляет блокировку по намерению и блокировку X перед обновлением записи, транзакция B сначала проверяет, есть ли блокировка по намерению для таблицы и не конфликтует ли существующая блокировка по намерению с блокировкой, которую она намерена Если есть конфликт, подождите, пока транзакция A не будет освобождена, не проверяя каждую запись. Когда транзакция B обновляет таблицу, ей не нужно знать, какая строка заблокирована, ей просто нужно знать, что строка все равно заблокирована.

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

Во-вторых, алгоритм блокировки

  • Блокировка записи: блокировка записи одной строки.

  • Блокировка промежутка: блокировка промежутка, блокирует диапазон, но не содержит саму запись

  • Next-Key Lock: Gap Lock+Record Lock, блокирует промежуток между диапазоном и индексом и блокирует саму запись; цель состоит в том, чтобы предотвратить фантомное чтение

3. Как mysql обеспечивает параллельное чтение и запись (управление несколькими версиями)?

Многоверсионный контроль параллелизма MVCC — это вариант блокировки на уровне строк.Сохранение моментального снимка данных в определенный момент времени аналогично реализации блокировки на уровне строк. Поэтому разные транзакции могут одновременно видеть разные данные для одной и той же таблицы. С точки зрения реализации моментальный снимок данных реализуется путем добавления номера версии даты создания и номера версии даты удаления после разных строк данных, и номер версии постоянно увеличивается.

тип чтения

  • Последовательное чтение без блокировки (чтение моментальных снимков)
    • На уровне изоляции транзакций Committed Read (RC) и Repeatable Read (RR) механизм хранения InnoDB использует неблокирующие согласованные чтения.
      • В режиме RC прочитать последний снимок
      • В режиме RR снимок в начале транзакции чтения
  • Непротиворечивое чтение блокировки (текущее чтение)
    • Операции чтения являются текущими операциями чтения при уровне изоляции Read Uncommitted (RN).
    • ВЫБЕРИТЕ... ДЛЯ ОБНОВЛЕНИЯ (добавьте блокировку записи)
    • SELECT…LOCK IN SHARE MODE (добавить блокировку чтения)

Четыре, анализ блокировки обработки

Следующие два простых SQL-запроса, какие блокировки они добавляют?

select * from t1 where id = 10

delete from t1 where id = 10

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

  • Помещение 1: Является ли столбец id первичным ключом?
  • Помещение 2: Каков уровень изоляции текущей системы?
  • Предпосылка три: Если столбец id не является первичным ключом, есть ли индекс в столбце id?
  • Предпосылка четвертаяЕсли в столбце :id есть вторичный индекс, является ли этот индекс уникальным?
  • Предпосылка пятая: Каковы планы выполнения двух SQL? Индексное сканирование? Полное сканирование таблицы?

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

  • Комбинация один:id столбец — это первичный ключ, уровень изоляции RC
  • Комбинация два:id столбец — вторичный уникальный индекс, уровень изоляции RC
  • Комбинация третья:id столбец — вторичный неуникальный индекс, уровень изоляции RC
  • Комбинация четыре: Нет индекса в столбце id, уровень изоляции RC
  • комбинация пять: столбец id — это первичный ключ, уровень изоляции RR
  • комбинация шестьСтолбец :id — вторичный уникальный индекс, уровень изоляции RR
  • Комбинация семь: столбец id является вторичным неуникальным индексом, уровень изоляции RR
  • Комбо восемь: Нет индекса в столбце id, уровень изоляции RR
  • Комбинация девять: Сериализуемый уровень изоляции

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

Комбинация 1: первичный ключ id + RC

   Эта комбинация является самой простой и легкой для анализа. id — это первичный ключ, уровень изоляции Read Committed, заданный SQL: удалить из t1, где id = 10; Просто добавить блокировку X к записи с id = 10 в первичном ключе. Как показано ниже:   

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

Комбинация 2: уникальный индекс id + RC

   В этой комбинации id является не первичным ключом, а уникальным значением ключа вторичного индекса. Тогда при уровне изоляции RC, какую блокировку нужно удалить из t1, где id = 10; нужно добавить? Увидеть ниже:        

   В этой комбинации идентификатор является уникальным индексом, а первичный ключ — столбцом имени. В настоящее время ситуация блокировки отличается из-за комбинации 1. Поскольку идентификатор является уникальным индексом, оператор удаления выберет индекс столбца идентификатора для фильтрации условия «где».После обнаружения записи с идентификатором = 10 он сначала добавит блокировку X к записи индекса с идентификатором = 10 на уникальном index, и в то же время в соответствии со столбцом прочитанного имени вернуться к индексу первичного ключа (кластеризованному индексу), а затем добавить блокировку X к элементу индекса первичного ключа, соответствующему name = 'd' в кластеризованном индексе. Почему записи в кластеризованном индексе также заблокированы? Представьте, что параллельный SQL обновляется через индекс первичного ключа: update t1 set id = 100, где name = 'd'; В этот момент, если оператор удаления не блокирует записи в индексе первичного ключа, тогда параллельное обновление будет Существование оператора удаления не будет восприниматься, что нарушает ограничение, заключающееся в том, что обновления/удаления одной и той же записи должны выполняться последовательно.

Вывод: если столбец id является уникальным столбцом, в нем есть уникальный индекс. Затем SQL должен добавить две блокировки X, одну, соответствующую записи с id = 10 в уникальном индексе id, а другую блокировку, соответствующую записи [name='d', id=10] в кластеризованном индексе.

Комбинация 3: id неуникальный индекс + RC

  По сравнению с комбинацией 1 и 2, комбинация 3 снова изменилась, уровень изоляции остался таким же, как у RC, но ограничение на столбец id было уменьшено, столбец id больше не уникален, и есть только один общий индекс. Предположим, что оператор удаления из t1, где id = 10; по-прежнему выбирает индекс в столбце id для фильтрации условия where, тогда какие блокировки будут удерживаться в это время? Также см. изображение ниже:

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

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

Комбинация 4: id без индекса + RC

  По сравнению с предыдущими тремя комбинациями, это довольно частный случай. В столбце id нет индекса, где id = 10; это условие фильтрации не может быть отфильтровано по индексу, поэтому его можно отфильтровать только путем полного сканирования таблицы. В соответствии с этой комбинацией, какую блокировку добавит SQL? Или другими словами, какие блокировки будут добавлены при полном сканировании таблицы? Ответов на этот вопрос тоже много: кто-то говорит, что к таблице добавятся X блокировок, кто-то говорит, что X блокировок добавится к записям с id = 10; выбранным по кластеризованному индексу. А реальная ситуация? Пожалуйста, посмотрите на изображение ниже:

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

   Кто-то может спросить? Почему бы не блокировать только те записи, которые соответствуют условиям? Это связано с реализацией MySQL. Если условие не может быть быстро отфильтровано индексом, уровень механизма хранения заблокирует все записи и вернет их, а затем их отфильтрует уровень сервера MySQL. Поэтому все записи заблокированы.

Примечание: В реальной реализации MySQL есть некоторые улучшения.После того, как условия фильтра MySQL Server будут признаны неудовлетворительными, будет вызван метод unlock_row для блокировки записей, которые не соответствуют условиям (нарушение ограничений 2PL). Это гарантирует, что в конце будет удержана только блокировка записи, удовлетворяющей условиям, но операция блокировки каждой записи не может быть пропущена.

Вывод: если в столбце id нет индекса, SQL будет фильтровать через полное сканирование кластеризованного индекса, потому что фильтрация выполняется на уровне сервера MySQL. Поэтому каждая запись, независимо от того, соответствует ли она условиям, будет заблокирована с помощью X. Однако ради эффективности MySQL был оптимизирован. Для записей, которые не соответствуют условиям, блокировка будет снята после вынесения решения. Окончательное удержание — это блокировка записи, которая соответствует условию, но блокировка записи который не соответствует условию/ Блокирующее действие не будет пропущено. В то же время оптимизация также нарушает ограничения 2PL.

Комбинация пятая: id первичного ключа + RR

   Приведенные выше четыре комбинации — это действия блокировки на уровне изоляции Read Committed, а следующие четыре комбинации — действия блокировки на уровне изоляции Repeatable Read.

  Комбинация 5, столбец id — это столбец первичного ключа, уровень изоляции Repeatable Read, для удаления из t1, где id = 10; этот SQL, блокировка и комбинация 1: [первичный ключ id, зафиксированное чтение] последовательный.

Комбинация шесть: id уникальный индекс + RR

  Подобно пятой комбинации, блокировка шестой комбинации согласуется с комбинацией два: [уникальный индекс id, Read Committed]. Две блокировки X, одна для записи, чей уникальный индекс id удовлетворяет условию, и одна для записи в соответствующем кластеризованном индексе.

Комбинация 7: id неуникальный индекс + RR

   Помните разницу между четырьмя уровнями изоляции MySQL, упомянутыми ранее? Уровень изоляции RC разрешает фантомное чтение, а уровень изоляции RR не разрешает фантомное чтение. Но в комбинации 5 и комбинации 6 поведение блокировки точно такое же, как поведение блокировки при RC. Затем на уровне изоляции RR

  Комбинация 7, уровень изоляции Repeatable Read, имеется неуникальный индекс по id, выполнить удаление из t1, где id = 10; Предполагая, что индекс в столбце id выбран для условной фильтрации, каково окончательное поведение блокировки? Также посмотрите на картинку ниже:       

   Эта фигура, в отличие от Комбинации Три: [Неуникальная блокировка столбца id, фиксация чтения] Вроде бы одинаковые, а на самом деле есть большая разница. Самая большая разница в том, что на этой картинке есть дополнительная блокировка GAP, и блокировка GAP, похоже, не добавляется к записи, а загружает позицию между двумя записями.

   По сути, эта дополнительная блокировка GAP и есть уровень изоляции RR, по сравнению с уровнем изоляции RC здесь нет ключа к фантомному чтению. Действительно, позиция, заблокированная блокировкой GAP, — это не сама запись, а GAP между двумя записями.

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

   Как показано на рисунке, где могут быть вставлены новые элементы, отвечающие условиям (id = 10) Учитывая порядок индекса дерева B+, элементы, соответствующие условиям, должны храниться непрерывно. Перед записью [6,c] запись с id=10 вставляться не будет, [10,aa] можно вставить между [6,c] и [10,b], между [10,b] и [10 ,d], новые [10, bb], [10, c] и т. д. могут быть вставлены [10, e], [10, z] и т. д., удовлетворяющие условиям, могут быть вставлены между [10, d] и [11, f] и [11 ,f] также не будут вставлять записи, удовлетворяющие условию. Следовательно, чтобы гарантировать, что между [6,c] и [10,b], между [10,b] и [10,d], [10,d] и [11,f] не будут вставлены новые записи, которые соответствуют условиям, MySQL решил использовать блокировку GAP, чтобы заблокировать эти три GAP.

Операции вставки, такие как вставка [10,aa], сначала находятся между [6,c] и [10,b], а затем проверяют, заблокирован ли GAP перед вставкой.Если он заблокирован, вставка записей не может быть вставлена. . Поэтому при текущем чтении первого прохода блокируются не только записи, соответствующие условиям (блокировка X), аналогично комбинации три. В то же время добавляются 3 блокировки GAP, и 3 GAP, которые могут быть вставлены в записи, соответствующие условиям, блокируются, чтобы гарантировать, что последующие вставки не смогут вставить новые записи с id=10, что также предотвращает второе текущее чтение из та же транзакция.Фантомная ситуация.

  Любой, кто увидит это, может спросить: Поскольку для предотвращения фантомного чтения требуется защита GAP-блокировок, почему комбинация 5 и комбинация 6, которые также являются уровнями изоляции RR, но не нуждаются в блокировках GAP?

   Во-первых, это хороший вопрос. Во-вторых, ответ на этот вопрос тоже очень прост. Блокировка GAP предназначена для предотвращения фантомного чтения двух текущих операций чтения одной и той же транзакции. В пятой комбинации id является первичным ключом, в шестой комбинации id является уникальным ключом, оба из которых могут гарантировать уникальность. Запрос с равным значением может возвращать не более одной записи, и новая запись с тем же значением не будет вставлена ​​заново, что позволяет избежать использования блокировок GAP. На самом деле для этой задачи есть более глубокая проблема: если комбинация пять и шесть, для SQL: выберите * из t1, где id = 10 для обновления; первый запрос, нет записей, удовлетворяющих условиям запроса. найдено, то можно ли опустить блокировки GAP? Этот вопрос остается для размышления каждого.

Вывод: на уровне изоляции Repeatable Read имеется неуникальный индекс в столбце id, соответствующий SQL: удалить из t1, где id = 10; Сначала найти первую запись, удовлетворяющую условиям запроса, через индекс id, и добавьте блокировку X на запись, добавьте блокировку GAP на GAP, затем добавьте блокировку X записи на кластеризованный индекс первичного ключа, а затем вернитесь; затем прочитайте следующую и повторите. Пока не будет достигнута первая запись [11,f], которая не удовлетворяет условиям, в это время нет необходимости добавлять блокировку записи X, но необходимо добавить блокировку GAP и, наконец, вернуться в конец.

Когда будет получен замок gap или nextkey?

Комбинация 8: id не имеет индекса + RR

   Комбинация 8, последний случай на уровне изоляции Repeatable Read, в столбце id нет индекса. В этот момент SQL: удалить из t1, где id = 10; другого пути на выбор нет, и можно выполнить только полное сканирование таблицы. Окончательная ситуация блокировки показана на следующем рисунке:   

   Как показано на картинке, это очень страшное явление. Во-первых, все записи в кластеризованном индексе X-locked. Во-вторых, зазор (GAP) между каждой записью кластеризованного индекса также добавляется с блокировкой GAP. В этой примерной таблице всего 6 записей, всего требуется 6 блокировок записей и 7 блокировок GAP. Только представьте, а что если в таблице 10 миллионов записей?

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

   Конечно, с комбинацией из четырех: [id не имеет индекса, Read Committed] Точно так же в этом случае MySQL также сделал некоторые оптимизации, которые представляют собой так называемое полусогласованное чтение. Когда включено полусогласованное чтение, MySQL заранее снимает блокировки для записей, которые не соответствуют условиям запроса. Для приведенного выше варианта использования, за исключением записей [d, 10], [g, 10], все блокировки записей будут сняты, а блокировки GAP добавлены не будут. Как инициировать полусогласованное чтение: либо чтение зафиксированного уровня изоляции, либо уровень изоляции Repeatable Read и установкаinnodb_locks_unsafe_for_binlogпараметр. Для более подробного ознакомления с полусогласованным чтением, пожалуйста, обратитесь к моему предыдущему блогу:Принцип полусогласованного чтения MySQL+InnoDB и анализ реализации.

Вывод: при уровне изоляции Repeatable Read, если выполняется текущее чтение полного сканирования таблицы, все записи в таблице будут заблокированы, и все GAP в кластеризованном индексе будут заблокированы одновременно, предотвращая все одновременные обновления/ операции удаления/вставки. Конечно, вы также можете инициировать полусогласованное чтение, чтобы уменьшить накладные расходы на блокировку и влияние параллелизма, но само по себе полусогласованное чтение также приведет к другим проблемам и не рекомендуется.

Комбинация девять: сериализуемый

   Для простого SQL, упомянутого ранее, последний случай: Сериализуемый уровень изоляции. Для SQL2: удалите из t1, где id = 10; уровень изоляции Serializable точно такой же, как уровень изоляции Repeatable Read, поэтому он не будет введен.

Уровень изоляции   Serializable влияет на SQL1: выберите * из t1, где id = 10; Этот SQL при уровнях изоляции RC и RR представляет собой моментальный снимок, прочитанный без блокировки. Однако на уровне изоляции Serializable SQL1 добавит блокировку чтения, что означает, что чтение моментального снимка больше не существует, а уровень управления параллелизмом MVCC понижен до CC на основе блокировки.

Вывод: В MySQL/InnoDB так называемая блокировка без чтения применима не ко всем ситуациям, а связана с уровнем изоляции. Сериализуемый уровень изоляции, чтение больше не устанавливается без блокировки, все операции чтения являются текущими операциями чтения.

5. Случаи взаимоблокировки

1. Конфликт блокировки строк одной и той же записи в разных таблицах

Эта ситуация хорошо понятна.Транзакция A и транзакция B управляют двумя таблицами, но существует циклическое ожидание блокировок.

2. Конфликт блокировки строк в одной и той же записи таблицы

Эта ситуация довольно распространена.Когда два задания выполняют пакетное обновление данных, список идентификаторов, обрабатываемый jobA, имеет вид [1,2,3,4], а список идентификаторов, обрабатываемый заданием, — [8,9,10, 4,2 ], что приводит к взаимоблокировке.

3. Различные конфликты блокировки индекса

Эта ситуация довольно неясна. Когда транзакция A выполняется, помимо блокировки вторичного индекса, она также блокирует кластеризованный индекс. Порядок блокировки кластеризованного индекса [1,4,2,3, 5] , а когда выполняется транзакция B, она блокирует только кластеризованный индекс, а последовательность блокировки — [1, 2, 3, 4, 5], что приводит к возможности взаимоблокировки.

4. Конфликт блокировки пробела

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

Шестое: как избежать взаимоблокировки, насколько это возможно

  1. Доступ к таблицам и строкам в фиксированном порядке. Например, в случае пакетного обновления двух заданий в разделе 2 простым методом является сначала отсортировать список идентификаторов, а затем выполнить его, что позволяет избежать ситуации перекрестного ожидания блокировок; например, в случае В разделе 3.1 упорядочена последовательность sql двух транзакций. Регулировка согласованности также позволяет избежать взаимоблокировок.
  2. Большие дела разбиваются на маленькие. Крупные транзакции более подвержены взаимоблокировкам.Если бизнес позволяет, разделите крупные транзакции на мелкие.
  3. В одной и той же транзакции попытайтесь заблокировать все необходимые ресурсы одновременно, чтобы уменьшить вероятность взаимоблокировки.
  4. Понизьте уровень изоляции. Если бизнес позволяет, также рекомендуется понизить уровень изоляции.Например, изменение уровня изоляции с RR на RC может избежать многих взаимоблокировок, вызванных гэп-блокировками.
  5. Добавьте в таблицу разумные индексы. Видно, что если индекс не использовать, то к каждой строке таблицы будет добавлена ​​блокировка, и вероятность взаимоблокировки сильно возрастет. знак равно

7. Как проверить замок

Начиная с InnoDB1.0, таблицы INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS были добавлены в схему INFORMATION_SCHEMA. (Подробности см. в приложении.) С помощью этих трех таблиц пользователям легче отслеживать текущие транзакции и анализировать возможные проблемы с блокировками.

#全局分析系统上中行锁的争夺情况
show status like 'innodb_row_lock%';
#查看事务
SELECT * FROM information_schema.INNODB_TRX;
#查看锁
SELECT * FROM information_schema.INNODB_LOCKS;
#查看锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
#通过联合查询可以比较直观的查看哪个事务阻塞了哪个事务
SELECT r.trx_id              '等待事务ID',
       r.trx_mysql_thread_id '等待线程ID',
       r.trx_query           '等待事务运行语句',
       b.trx_id              '阻塞事务ID',
       b.trx_mysql_thread_id '阻塞线程ID',
       b.trx_query           '阻塞事务运行语句'
FROM information_schema.innodb_lock_waits w
         INNER JOIN information_schema.innodb_trx b
                    ON b.trx_id = w.blocking_trx_id
         INNER JOIN information_schema.innodb_trx r
                    ON r.trx_id = w.requesting_trx_id;

8. Как mysql предотвращает взаимоблокировки?

innodb_lock_wait_timeout дождитесь тайм-аута блокировки, чтобы откатить транзакцию

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

Алгоритм ожидания графа для активного обнаружения взаимоблокировок

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

График ожидания требует, чтобы база данных сохраняла следующие два вида информации:

  • список информации о блокировке

  • список ожидания транзакций

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

  • Транзакция T1 ожидает ресурсов, занятых транзакцией T2.

  • Транзакция Т1 в итоге ждет ресурсов, занятых Т2, то есть транзакции ждут тех же ресурсов, и транзакция Т1 происходит после транзакции Т2

Пример статуса транзакции и информации о блокировке

В списках ожидания транзакций вы можете видеть, что есть 4 транзакции t1, t2, t3 и t4, поэтому в графе ожидания должно быть 4 узла. В то время как транзакция t2 занимает блокировку x для строки1, транзакция t1 занимает блокировку s для строки2. Транзакция t1 должна ожидать ресурсы строки 1 в транзакции t2, поэтому в графе ожидания есть ребро от узла t1 до узла t2. Транзакция t2 должна дождаться объекта row2, занятого транзакциями t1 и t4, поэтому существует ребро от узла t2 к узлам t1 и t4. Точно так же есть ребра от узла t3 к узлам t1, t2 и t4, поэтому окончательный граф ожидания показан на следующем рисунке.

ps: если он существует, существует взаимоблокировка.Вообще говоря, механизм хранения InnoDB выбирает откат транзакции с наименьшим количеством отмен и запуск заново.

приложение

INNODB_ROW_LOCK

имя столбца описывать
innodb_row_lock_current_waits Количество ожидающих блокировок
innodb_row_lock_time Общая продолжительность времени от запуска системы до момента блокировки
innodb_row_lock_time_avg Среднее время ожидания
innodb_row_lock_time_max Время, затраченное на ожидание наиболее частого из них от запуска системы до настоящего момента
innodb_row_lock_waits Общее количество ожиданий системы с момента запуска, напрямую определяет направление и стратегию оптимизации

INNODB_TRX

Предоставляет информацию о каждой транзакции, выполняемой в настоящее время внутри InnoDB, в том числе о том, ожидает ли транзакция блокировку, когда транзакция началась, а также оператор SQL (если есть), который выполняет транзакция. смотрите подробностиDev.MySQL.com/doc/Furious/…

имя столбца описывать
TRX_ID Номер транзакции
TRX_WEIGHT Вес транзакции, отражающий (но не обязательно точный подсчет) количество измененных строк и количество строк, заблокированных транзакцией. Для устранения взаимоблокировок InnoDB выбирает транзакцию с наименьшим весом в качестве «жертвы» отката. Транзакции, которые изменили нетранзакционные таблицы, считаются более тяжелыми, чем другие транзакции, независимо от количества измененных и заблокированных строк.
TRX_STATE Статус выполнения сделки. Допустимое значение: РАБОТАЕТ，LOCK WAIT, ОТКАТ，和 COMMITTING.
TRX_STARTED Время начала сделки.
TRX_REQUESTED_LOCK_ID ID блокировки, которую в данный момент ожидает транзакция, если TRX_STATE是LOCK WAIT; иначе NULL``.
TRX_WAIT_STARTED Время начала ожидания блокировки транзакцией, если TRX_STATE是LOCK WAIT; иначе NULL``.
TRX_MYSQL_THREAD_ID Идентификатор потока MySQL, соответствующий значению идентификатора в списке процессов show
TRX_QUERY Оператор SQL, который выполняет транзакция
TRX_OPERATION_STATE Текущая операция транзакции, если таковая имеется, иначе NULL``.
TRX_TABLES_IN_USE Количество таблиц, используемых InnoDB при обработке текущего оператора SQL для этой транзакции.
TRX_TABLES_LOCKED InnoDB ``Количество таблиц с блокировками строк для текущего оператора SQL. (Поскольку это блокировки строк, а не таблиц, таблица обычно может быть прочитана и записана несколькими транзакциями, хотя некоторые строки заблокированы.)
TRX_LOCK_STRUCTS Количество блокировок, удерживаемых транзакцией.
TRX_LOCK_MEMORY_BYTES Общий размер, занимаемый блокирующими структурами этой транзакции в памяти.
TRX_ROWS_LOCKED Приблизительное количество или количество строк, заблокированных этой транзакцией. Значение может включать удаление помеченных строк, которые действительно существуют, но не видны транзакции.
TRX_ROWS_MODIFIED Количество строк, измененных и вставленных в эту транзакцию.
TRX_CONCURRENCY_TICKETS Значение, указывающее, сколько работы может выполнить текущая транзакция, прежде чем она будет заменена.
TRX_ISOLATION_LEVEL Уровень изоляции текущей транзакции.
TRX_UNIQUE_CHECKS Включить или выключить уникальную проверку для текущей транзакции. Например, они могут быть отключены во время массовой загрузки данных.
TRX_FOREIGN_KEY_CHECKS Включить или отключить проверку внешнего ключа для текущей транзакции. Например, они могут быть отключены во время массовой загрузки данных.
TRX_LAST_FOREIGN_KEY_ERROR Подробное сообщение об ошибке для последней ошибки внешнего ключа (если есть); в противном случае NULL``
TRX_ADAPTIVE_HASH_LATCHED Блокируется ли адаптивный хеш-индекс текущей транзакцией. Когда система поиска с адаптивным хэш-индексом разделена, одна транзакция не блокирует весь адаптивный хэш-индекс. Разделение адаптивного хеш-индекса поinnodb_adaptive_hash_index_parts``, значение по умолчанию — 8.
TRX_ADAPTIVE_HASH_TIMEOUT Отбрасывать ли блокировку поиска сразу для адаптивных хеш-индексов или сохранять ее между вызовами MySQL. При отсутствии конкуренции за адаптивный хэш-индекс значение остается равным нулю, а операторы сохраняют защелки до завершения. Во время состязания он отсчитывает до нуля, и инструкция освобождает защелку сразу после поиска каждой строки. Когда система поиска с адаптивным хэш-индексом разделена (управляетсяinnodb_adaptive_hash_index_parts``), значение остается равным 0.
TRX_IS_READ_ONLY Значение 1 указывает, что транзакция доступна только для чтения.
TRX_AUTOCOMMIT_NON_LOCKING Значение 1 указывает, что транзакцияSELECT](https://dev.mysql.com/doc/refman/5.7/en/select.html)不使用FOR UPDATEили ЗАБЛОКИРОВАТЬ В ОБЩЕМ РЕЖИМЕ子句的语句，并且正在执行， [autocommitТаким образом, транзакция будет содержать только этот один оператор. Когда этот столбец и TRX_IS_READ_ONLY都为1时，InnoDBОптимизируйте транзакции, чтобы уменьшить накладные расходы, связанные с транзакциями, которые изменяют данные таблицы.

INNODB_LOCKS

Предоставляет информацию о каждой блокировке, запрошенной, но не полученной транзакцией InnoDB, и о каждой блокировке, удерживаемой транзакцией, которая блокирует другую транзакцию. смотрите подробностиDev.MySQL.com/doc/Furious/…

имя столбца описывать
LOCK_ID Уникальный идентификационный номер блокировки внутри InnoDB``.
LOCK_TRX_ID ID транзакции удерживающей блокировку
LOCK_MODE Как запросить блокировку. Разрешить дескриптор режима блокировки S，X, ЯВЛЯЕТСЯ，IX, ЗАЗОР，AUTO_INCи НЕИЗВЕСТНО``. Дескрипторы режима блокировки могут использоваться в комбинации для идентификации конкретного режима блокировки.
LOCK_TYPE тип замка
LOCK_TABLE Имя таблицы, которая заблокирована или содержит заблокированные записи
LOCK_INDEX имя индекса, если LOCK_TYPE равно RECORD, иначе NULL
LOCK_SPACE Идентификатор табличного пространства заблокированной записи, если LOCK_TYPE是RECORD; иначе NULL``
LOCK_PAGE Номер страницы заблокированной записи, если LOCK_TYPE是RECORD; иначе NULL``.
LOCK_REC Номер кучи заблокированной записи на странице, если LOCK_TYPE是RECORD; иначе NULL``.
LOCK_DATA Данные, связанные с блокировкой, если таковые имеются. если LOCK_TYPE是RECORD, является значением первичного ключа заблокированной записи, иначе NULL。此列包含锁定行中主键列的值，格式为有效的SQL字符串。如果没有主键，LOCK_DATAединственная InnoDB内部行ID号。如果对键值或范围高于索引中的最大值的间隙锁定，则LOCK_DATAотчет _supremum_pseudo-record. InnoDB, когда страница, содержащая заблокированную запись, не находится в пуле буферов (если она выгружается на диск, пока блокировка удерживается)不从磁盘获取页面，以避免不必要的磁盘操作。相反， LOCK_DATAУстановите значение NULL``.

INNODB_LOCK_WAITS

Содержит одну или несколько строк для каждой заблокированной транзакции InnoDB с указанием запрошенных блокировок и любых блокировок, которые блокируют запрос. смотрите подробностиDev.MySQL.com/doc/Furious/…

имя столбца описывать
REQUESTING_TRX_ID Идентификатор запрашивающей (блокирующей) транзакции.
REQUESTED_LOCK_ID Идентификатор блокировки, которую ожидает транзакция.
BLOCKING_TRX_ID Идентификатор блокирующей транзакции.
BLOCKING_LOCK_ID Идентификатор блокировки, удерживаемой транзакцией, которая препятствует выполнению другой транзакции.

Цитата

«Инсайдер технологии MySQL: механизм хранения InnoDB»

Анализ обработки блокировок MySQL Хэ Дэнчэна

Подробное объяснение процесса блокировки Mysql

Транзакции и блокировки баз данных (3)

Анализ идей для решения проблемы взаимоблокировки MySQL

Категории