Блокировка — очень важная технология для реализации контроля параллелизма базы данных. Прежде чем транзакция обработает объект данных, она сначала отправляет системе запрос на его блокировку. После блокировки транзакция имеет определенный контроль над объектом данных.Пока транзакция не снимает блокировку, другие транзакции не могут обновлять объект данных.
Что такое замок?
Блокировка — это механизм, с помощью которого компьютер координирует одновременный доступ к ресурсу несколькими процессами или потоками..
Блокировки обеспечивают согласованность и достоверность одновременного доступа к данным;
Конфликт блокировок также является важным фактором, влияющим на производительность параллельного доступа к базе данных.
Блокировка - это контроль параллелизма Mysql на уровне сервера и уровне механизма хранения.
зачем запирать
База данных — это общий ресурс, используемый несколькими пользователями. Когда несколько пользователей одновременно обращаются к данным, в базе данных будет несколько транзакций, обращающихся к одним и тем же данным в одно и то же время. Без контроля над параллельными операциями неверные данные могут быть прочитаны и сохранены, что нарушит согласованность базы данных.
Блокировки используются для управления параллелизмом над общими ресурсами.То есть в случае параллелизма будет конкуренция за ресурсы, поэтому требуется блокировка.
Блокировка решает проблему обеспечения целостности и непротиворечивости базы данных в многопользовательской среде.
Объект блокировки — это транзакция, которая используется для блокировки объектов в базе данных, таких как таблицы, страницы и строки. А общий объект блокировки освобождается только после фиксации или отката транзакции (время освобождения может быть разным для разных уровней изоляции транзакций).
классификация замков
Общая блокировка||блокировка чтения||S-блокировка (общая блокировка): другие транзакции могут читать, но не записывать. Позволяет одной транзакции читать строку, предотвращая получение другими транзакциями монопольной блокировки того же набора данных.
-
Эксклюзивная блокировка||Блокировка записи||Блокировка X (эксклюзивная): другие транзакции не могут читать или записывать. Позволяет транзакциям, которые получают эксклюзивные блокировки, обновлять данные, не позволяя другим транзакциям получать общие блокировки чтения и эксклюзивные блокировки записи в том же наборе данных.
Тип разбивки:
Блокировка общего доступа к намерениям (блокировка IS/блокировка общего доступа к намерениям)
Эксклюзивная блокировка по намерению||блокировка взаимного исключения (блокировка IX/монопольная блокировка по намерению)
-
Пессимистическая блокировка || Консервативная блокировка (пессимистическая блокировка): предполагается, что будут возникать конфликты параллелизма, экранируя все операции, которые могут нарушить целостность данных.
Пессимистичные блокировки — это блокировки на уровне базы данных, которые блокируют ожидание блокировок.
-
Оптимистическая блокировка: если предположить, что конфликтов параллелизма не возникнет, проверка нарушений целостности данных выполняется только при фиксации операции.
Идея оптимистической блокировки, конкретная реализация в том, что в таблице есть поле версии, и это поле получается при первом чтении. После обработки бизнес-логики и начала обновления нужно еще раз проверить, совпадает ли значение поля с первым. Если же обновить, иначе отклонить. Причина, по которой он называется оптимистичным, заключается в том, что этот режим не заблокирован от базы данных, и он будет ждать, пока она не будет обновлена, чтобы определить, можно ли ее обновить.
Недостатки: при высоком уровне параллелизма возникает много бесполезных повторных попыток. Оптимистическая блокировка не может решить проблему грязного чтения.
Стратегия блокировки: блокировка детализации/детализации
Накладные расходы на блокировку относительно дороги,Стратегия блокировки на самом деле является стратегией баланса между обеспечением безопасности потоков и получением максимальной производительности..
блокировка на уровне строки
блокировка строки: То есть только одна строка данных может быть прочитана транзакцией. Гранулярность блокировок строк верна для каждой строки данных, что, конечно, приводит к наибольшим накладным расходам, но блокировки строк могут поддерживать параллельную обработку в максимально возможной степени.
Высокие накладные расходы, медленная блокировка, взаимные блокировки, наименьшая степень детализации блокировки, самая низкая вероятность конфликтов блокировок и самый высокий уровень параллелизма.
Он в наибольшей степени поддерживает параллелизм, но также приводит к наибольшей нагрузке на блокировку.
Блокировки на уровне строк больше подходят для приложений с большим количеством одновременных обновлений небольшого количества различных данных на основе условий индекса и параллельных запросов, таких как некоторые системы оперативной обработки транзакций (OLTP).
В InnoDB, за исключением одной транзакции SQL, блокировки приобретаются постепенно, что определяет возможность взаимоблокировок в InnoDB.
Блокировки на уровне строк реализуются только на уровне механизма хранения, но не на уровне сервера Mysql.
блокировка на уровне таблицы
блокировка стола: позволяет транзакциям удерживать блокировки как на уровне строки, так и на уровне таблицы. Блокирует всю таблицу с минимальными затратами, но также блокирует всю таблицу.
Низкие накладные расходы и быстрая блокировка, отсутствие взаимоблокировок, высокая степень детализации блокировки, самая высокая вероятность конфликта блокировок и самый низкий уровень параллелизма.
Эти механизмы хранения избегают взаимоблокировок, всегда запрашивая все необходимые блокировки одновременно и всегда запрашивая блокировки таблиц в одном и том же порядке.
Блокировки на уровне таблицы больше подходят для приложений, которые сосредоточены на запросах, имеют несколько одновременных пользователей и имеют лишь небольшое количество обновлений данных на основе условий индекса, таких как веб-приложения.
Если пользователь выполняет операцию записи, он получит эксклюзивную «блокировку записи», которая может заблокировать всю таблицу и заблокировать операции чтения и записи других пользователей;
Если пользователь выполняет операцию чтения, сначала будет получена разделяемая блокировка «блокировка чтения», которая запускает другие блокировки чтения для одновременного чтения таблицы, не мешая друг другу. Блокировки чтения могут быть одновременными чтениями универсального ресурса, пока не установлены блокировки записи.
Блокировки уровня таблицы Mysql делятся на две категории: блокировки метаданных (MDL) и блокировки таблицы.
Блокировка метаданных (MDL)
Блокировки метаданных (MDL) не нужно использовать явно, они автоматически добавляются при доступе к таблице. Эта функция требует поддержки MySQL 5.5 или выше.
При добавлении, удалении, изменении и запросе таблицы таблица будет добавлена с блокировкой чтения MDL.
При внесении структурных изменений в таблицу добавьте блокировку записи MDL
Правила блокировки MDL:
Блокировки чтения не являются взаимоисключающими, поэтому несколько потоков могут добавлять, удалять, изменять и запрашивать одну и ту же таблицу.
Блокировки чтения-записи и блокировки записи являются взаимоисключающими.Чтобы обеспечить безопасность изменений структуры таблицы, если для добавления полей в одну и ту же таблицу и другие структуры таблиц требуется несколько потоков, они будут сериализованы и должны ждать блокировок. .
Приоритет блокировки записи MDL выше, чем приоритет блокировки чтения MDL, но системная переменная max_write_lock_count может изменить эту ситуацию.Когда запрос блокировки записи превышает число, установленное этой переменной, блокировка чтения MDL будет иметь более высокий приоритет, чем блокировка записи MDL Высокий приоритет. (По умолчанию это число будет большим, поэтому не беспокойтесь о потере приоритета блокировок записи)
Снятие блокировки MDL не должно быть снято до конца транзакции.
блокировка на уровне страницы
Блокировка на уровне страницы — это уникальный уровень блокировки в MySQL, и он не слишком распространен в других программах управления базами данных.
Накладные расходы на блокировку страниц и время блокировки находятся между блокировками таблиц и блокировками строк; возникают взаимоблокировки; степень детализации блокировок находится между блокировками таблиц и блокировками строк, а степень параллелизма является средней.
Характеристика блокировки на уровне страницы заключается в том, что степень детализации блокировки находится между блокировкой на уровне строки и блокировкой на уровне таблицы, поэтому накладные расходы ресурсов, необходимые для получения блокировки, и возможности параллельной обработки, которые она может обеспечить, также находятся между двумя вышеуказанными. Кроме того, блокировка на уровне страницы, как и блокировка на уровне строки, может привести к взаимоблокировкам.
В процессе реализации блокировки ресурсов в базе данных по мере уменьшения детализации блокировки ресурсов увеличивается объем памяти, необходимый для блокировки одного и того же объема данных, и алгоритм реализации будет становиться все более сложным.
Однако по мере уменьшения степени детализации блокирующих ресурсов вероятность того, что запросы на доступ к приложениям столкнутся с ожиданием блокировки, будет уменьшаться, а общий параллелизм системы также будет увеличиваться.
Блокировка на уровне страниц в основном используется механизмом хранения BerkeleyDB..
глобальная блокировка
MySQL предоставляет глобальные блокировки для блокировки всего экземпляра базы данных.
FLUSH TABLES WITH READ LOCK
Эта инструкция обычно используется для резервного копирования.При выполнении этой инструкции все открытые таблицы в базе данных будут закрыты, а все таблицы в базе данных будут заблокированы глобальной блокировкой чтения.Операции определения данных (создание таблиц, изменение структуры таблиц) и фиксации транзакций для классов обновления заблокированы.
После mysql 8.0 для резервного копирования mysql может напрямую использовать блокировку резервного копирования.
LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE
Эта блокировка имеет более широкий охват. Эта блокировка запрещает создание, переименование и удаление файлов, включая операции REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE и управление аккаунтом. Конечно, эти операции можно выполнять и для временных таблиц в памяти, но почему на таблицы в памяти не распространяются эти ограничения? Поскольку резервное копирование таблицы памяти не требуется, нет необходимости выполнять эти условия.
Различные механизмы хранения MySQL поддерживают разные механизмы блокировки.
Все механизмы хранения раскрывают механизмы блокировки по-своему, и уровень сервера совершенно не знает о реализации блокировки в механизме хранения:
Механизмы хранения MyISAM, MEMORY и CSV используют блокировку на уровне таблицы.
Механизм хранения BDB (Berkeley DB) использует блокировку на уровне страницы, но также поддерживает блокировку на уровне таблицы.
Механизм хранения InnoDB поддерживает как блокировку на уровне строки, так и блокировку на уровне таблицы, но по умолчанию используется блокировка на уровне строки.
Блокировки строк InnoDB реализованы путем блокировки элементов индекса в индексе.Особенности реализации блокировки строк InnoDB означают, что InnoDB использует блокировки на уровне строк только тогда, когда данные извлекаются с помощью условий индекса, в противном случае InnoDB будет использовать блокировки таблиц!
Все блокировки на уровне строк основаны на индексах.Если оператор SQL не использует индекс, он не будет использовать блокировки на уровне строк, а будет использовать блокировки на уровне таблицы. Недостатком блокировок на уровне строк является то, что из-за необходимости запрашивать большое количество ресурсов блокировки скорость низкая, а потребление памяти велико.
По умолчанию блокировки таблицы и строки получаются автоматически, никаких дополнительных команд не требуется..
Однако в некоторых случаях пользователю необходимо явно заблокировать таблицу или управлять транзакцией, чтобы обеспечить целостность всей транзакции, что требует использования операторов управления транзакциями и блокировки для завершения.
Между InnoDB и MyISAM есть два самых больших различия: одно заключается в поддержке транзакций (ТРАНЗАКЦИЯ); другое — в использовании блокировок на уровне строк..
Поскольку подсистема хранения Innodb реализует блокировку на уровне строк, хотя потеря производительности, вызванная реализацией механизма блокировки, может быть выше, чем при блокировке на уровне таблицы, она намного превосходит MyISAM с точки зрения общих возможностей параллельной обработки. Когда системный параллелизм высок, общая производительность Innodb будет иметь очевидные преимущества по сравнению с MyISAM.
Однако блокировка на уровне строк в Innodb также имеет свою хрупкую сторону: при неправильном использовании общая производительность Innodb может быть не выше, чем у MyISAM, а может быть и хуже.
Блокировки InnoDB на уровне строки и блокировки на уровне таблицы
Режим блокировки InnoDB:
InnoDB реализует следующие два типа блокировки строк:
Общая блокировка (S-shared): позволяет транзакции читать строку, предотвращая получение другими транзакциями монопольной блокировки того же набора данных.
Эксклюзивная блокировка (X-эксклюзивная): позволяет транзакции, получившей монопольную блокировку, обновлять данные, предотвращая получение другими транзакциями общей блокировки чтения и монопольной блокировки записи того же набора данных.
Для поддержки операций блокировки с разной степенью детализации (позволяя сосуществованию блокировок строк и блокировок таблиц для реализации многогранулярного механизма блокировки) InnoDB также имеет две внутренне используемые блокировки намерения, обе из которых являются блокировками таблиц.
-
Транзакция Intent Share Lock (IS-Intent Share Lock) хочет получить общую блокировку определенных строк в таблице.
Если транзакция намеревается добавить общую блокировку строки к строке данных, транзакция должна сначала получить блокировку IS таблицы, прежде чем добавлять общую блокировку к строке данных.
-
Намеренная монопольная блокировка (IX - монопольная блокировка с намерением) Транзакция хочет получить монопольную блокировку определенных строк в таблице.
Транзакция намеревается добавить монопольную блокировку строки к строке данных, и транзакция должна сначала получить блокировку IX для таблицы, прежде чем добавлять монопольную блокировку к строке данных.
Поскольку механизм хранения InnoDB поддерживает блокировки на уровне строк, блокировки намерений фактически не блокируют никаких запросов, кроме полного сканирования таблицы. Таким образом, совместимость между блокировками намерений на уровне таблицы и блокировками на уровне строк выглядит следующим образом.
Если режим блокировки, запрошенный транзакцией, совместим с текущей блокировкой, 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 обновляет таблицу, ей не нужно знать, какая строка заблокирована, ей просто нужно знать, что строка все равно заблокирована.
Основная функция заключается в устранении противоречия между блокировками строк и блокировками таблицы, что может показать, что «транзакция удерживает блокировку определенной строки или готовится удерживать блокировку».
Реализация блокировки строк InnoDB:
Блокировки строк добавляются в индекс
Структура данных индекса в Innodb представляет собой дерево B+, данные расположены по порядку, и соответствующие данные находятся слой за слоем от корневого узла к конечному узлу..
Обычный индекс, также известный как вспомогательный индекс, конечный узел хранит значение первичного ключа. Индекс по первичному ключу называется кластерным индексом, и каждая запись в таблице хранится на листовом узле первичного ключа. При запросе данных через выбор вспомогательного индекса соответствующее значение первичного ключа сначала будет найдено во вспомогательном индексе, а затем запись будет найдена в кластеризованном индексе с использованием значения первичного ключа.
Например, при запросе с именем = Алиса сначала будет найдено соответствующее значение первичного ключа 18, а затем используется 18 для поиска записей с именем = Алиса как 77 и Алиса в следующем кластеризованном индексе.
Данные каждой строки в таблице организованы и хранятся в кластеризованном индексе, поэтому он называется индексно-организованной таблицей..
Блокировки строк InnoDB реализуются путем блокировки записей индекса в индексе., что отличается от Oracle, что достигается блокировкой соответствующей строки данных в блоке данных. Эта функция реализации блокировки строк в InnoDB означает, что InnoDB использует блокировки на уровне строк только тогда, когда данные извлекаются с помощью условий индекса, в противном случае InnoDB будет использовать блокировки таблиц!
Независимо от того, используете ли вы индекс первичного ключа, уникальный индекс или обычный индекс, InnoDB использует блокировки строк для блокировки данных..
Блокировки строк можно использовать только в том случае, если план выполнения действительно использует индекс: Даже если в условии используется поле индекса, MySQL определяет, следует ли использовать индекс для извлечения данных, оценивая стоимость различных планов выполнения.Если MySQL считает, что полное сканирование таблицы более эффективно, например, для некоторых небольших таблицы, индексы не будут использоваться, и в этом случае InnoDB будет использовать блокировки таблиц вместо блокировок строк. Поэтому при анализе конфликтов блокировок не забудьте проверить план выполнения SQL (вы можете проверить план выполнения SQL через объяснение), чтобы убедиться, что индекс действительно используется. (Читать далее:Сводка индексов MySQL)
Поскольку блокировка строки MySQL является блокировкой индекса, а не блокировкой записи, хотя несколько сеансов обращаются к записям разных строк, если используется один и тот же ключ индекса, возникнут конфликты блокировок.(Сеанс, который позже использует эти индексы, должен дождаться, пока сеанс, использующий индекс, освободит блокировку, прежде чем он сможет получить блокировку). Помните об этом при разработке приложения.
Алгоритм блокировки строки (размерность алгоритма)
Блокировка записи (однострочная запись)
Gap Lock (блокировка пробела, блокирует диапазон, но не содержит заблокированных записей)
Next-Key Lock (Record Lock + Gap Lock, блокирует диапазон, и блокирует саму запись, MySql предотвращает фантомное чтение, реализуется с помощью этой блокировки)
Блокировки записи, блокировки промежутка и блокировки близости являются эксклюзивными блокировками.
Блокировка записи
После блокировки транзакции блокируется только одна запись таблицы.
Условия возникновения блокировки записи:Точное совпадение условия, а поле условия совпадения является уникальным индексом;
Например: update user_info set name='Zhang San', где id=1, id здесь является уникальным индексом.
Блокировка записи всегда будет блокировать записи индекса.Если таблица механизма хранения InnoDB не имеет индекса, установленного при ее создании, то механизм хранения InnoDB будет использовать неявный первичный ключ для блокировки.
Роль блокировки записи: после добавления блокировки записи можно избежать проблемы повторного чтения, когда данные изменяются во время запроса, а также избежать проблемы грязного чтения, которая считывается другими транзакциями до того, как измененная транзакция будет зафиксирована.
Блокировка следующей клавиши
Блокировка близости — это алгоритм блокировки строк INNODB по умолчанию. Это комбинация блокировки записи и блокировки промежутка. Блокировка близости заблокирует запрошенные записи, а также заблокирует все промежутки в запросе диапазона. Кроме того, он заблокирует следующий соседний интервал .
Условия возникновения блокировки клавиш:Запрос диапазона и попадание, запрос попадает в индекс.
Например, для данных в следующей таблице выполните select * from user_info, где id>1 и id
Записи с идентификаторами 1, 5, 10 будут заблокированы, при этом будут заблокированы интервалы от 1 до 5, от 5 до 10 и от 10 до 15.
Роль ключевого замка: в сочетании с характеристиками блокировок записей и блокировок промежутков блокировки по ключу позволяют избежать проблем грязного чтения, повторного чтения и фантомного чтения во время запросов диапазона. После добавления блокировки клавиш данные в диапазоне не могут быть изменены и вставлены.
Next-Key Lock — это алгоритм блокировки, который сочетает в себе Gap Lock и Record Lock.В рамках алгоритма Next-Key Lock InnoDB использует этот алгоритм блокировки для запросов строк.
В дополнение к Next-Key Locking есть технология Previous-Key Locking.
Блокировка пробела:
Когда мы извлекаем данные с условиями диапазона вместо условий равенства и запрашиваем общую или эксклюзивную блокировку, InnoDB блокирует записи индекса существующих записей данных, которые соответствуют условиям; для записей, значения ключей которых находятся в пределах диапазона условий, но не существует, Он называется "пробел", и InnoDB также заблокирует этот "пробел". Этот механизм блокировки является так называемой блокировкой промежутка..
Очевидно, что при использовании условий диапазона для извлечения и блокировки записей механизм блокировки InnoDB будет блокировать одновременную вставку значений ключа в допустимом диапазоне, что часто приводит к серьезным ожиданиям блокировки. Поэтому при практической разработке приложений, особенно в приложениях с большим количеством одновременных вставок, мы должны изо всех сил стараться оптимизировать бизнес-логику, стараться использовать условия равенства для доступа к обновленным данным и избегать использования условий диапазона.
Назначение щелевого замка:
Предотвращение фантомного чтения для соответствия требованиям к соответствующему уровню изоляции;
Для удовлетворения потребностей в восстановлении и репликации:
Условия для генерации гэп-блокировок (при уровне изоляции транзакции RR):
Используйте обычную блокировку индекса;
Используйте многоколоночный уникальный индекс;
Используйте уникальный индекс для блокировки нескольких строк записей.
В вышеперечисленных случаях произойдет гэп-лок.
Рекомендуемое чтениеМеханизм блокировки MySQL - блокировка записи, блокировка пробела, блокировка ключа》
MySQL вводит и выполняет успешные операторы INSERT, UPDATE, DELETE и другие операторы SQL для обновления данных через BINLOG и, таким образом, обеспечивает восстановление базы данных MySQL и репликацию master-slave. Механизм восстановления MySQL (репликация фактически выполняет восстановление на основе BINLOG в Slave Mysql) имеет следующие характеристики:
Во-первых, восстановление MySQL происходит на уровне оператора SQL, то есть повторно выполняется оператор SQL в BINLOG.
Во-вторых, Binlog MySQL записывается в том порядке, в котором были отправлены транзакции, и восстановление также выполняется в этом порядке.
Отсюда видно, чтоМеханизм восстановления MySQL требует, чтобы до того, как транзакция будет зафиксирована, другие параллельные транзакции не могли вставлять какие-либо записи, соответствующие условиям блокировки, то есть фантомное чтение не разрешено..
На этой картинке три вида замков
блокировка записи: блокировка записи одной строки
Gap lock: Блокирует диапазон между записями, но не сами записи.
Next Key Lock: блокировка записи + блокировка промежутка, блокировка диапазона, включая саму запись.
Не все индексы будут добавлены с блокировкой следующего ключа. Если столбец запроса является уникальным индексом (включая индекс первичного ключа), уровень блокировки следующего ключа будет понижен до блокировки записи.
CREATE TABLE z (a INT,b INT,PRIMARY KEY(a),KEY(b));// a是主键索引，b是普通索引
INSERT INTO z select1,1;
INSERT INTO z select3,1;
INSERT INTO z select5,3;
INSERT INTO z select7,6;
INSERT INTO z select10,8;В это время выполните SELECT*FROM z WHERE b=3FOR UPDATE в сеансе A, и индекс будет заблокирован следующим образом:
В это время оператор, выполняемый сеансом B, попадает в диапазон блокировки и будет ожидать.
SELECT * FROM z WHERE a =5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;Пользователи могут явно отключить Gap Lock следующими двумя способами:
Установите уровень изоляции транзакции на READ COMMITED.
Установите для параметра innodblocksunsafeforbinlog значение 1.
Как видно из приведенного выше примера, роль Gap Lock заключается в том, чтобы предотвратить вставку записей в один и тот же диапазон несколькими транзакциями, и она предназначена для решения проблемы Phontom (проблема фантомного чтения). При уровне изоляции MySQL по умолчанию (Repeatable Read) InnoDB использует его для решения проблемы фантомного чтения.
Метод блокировки InnoDB:
Блокировки намерения автоматически добавляются InnoDB без вмешательства пользователя..
Для операторов UPDATE, DELETE и INSERT InnoDB автоматически добавит эксклюзивную блокировку (X) к задействованному набору данных.;
-
Для обычных операторов SELECT InnoDB не будет добавлять никаких блокировок.;
Транзакция может явно добавить разделяемую блокировку или монопольную блокировку к набору записей с помощью следующего оператора:
Совместно используемые блокировки: SELECT * FROM table_name WHERE... LOCK IN SHARE MODE. Другие сеансы могут по-прежнему запрашивать запись, а также могут добавлять к записи общую блокировку режима общего доступа. Но если текущей транзакции необходимо обновить запись, это, вероятно, вызовет взаимоблокировку.
Эксклюзивная блокировка (X): SELECT * FROM имя_таблицы, ГДЕ... ДЛЯ ОБНОВЛЕНИЯ. Другие сеансы могут запрашивать запись, но не могут добавлять общие или эксклюзивные блокировки к записи, но ждут, чтобы получить блокировку.
Неявная блокировка:
InnoDB использует двухфазный протокол блокировки во время выполнения транзакции:
Блокировку можно выполнить в любое время, и InnoDB будет автоматически блокироваться при необходимости в соответствии с уровнем изоляции;
Блокировки снимаются только при фиксации или откате, и все блокировки снимаются одновременно.
Явная блокировка:
select ... lock in share mode //共享锁
select ... for update //排他锁select for update
При выполнении этого оператора запроса select соответствующая запись доступа к индексу будет помещена в эксклюзивную блокировку (X-блокировку), то есть блокировка, соответствующая этому оператору, эквивалентна эффекту обновления.
Используйте сценарий выбора *** для обновления: чтобы гарантировать, что данные, которые вы найдете, являются последними данными, и данные, которые вы найдете, могут быть изменены только вами, вам необходимо использовать предложение для обновления.
select * from user where id=10 for update
Эта запись блокируется блокировкой узла в кластеризованном индексе (блокируется индекс с id=10, то есть блокируется запись).
выберите * от пользователя, где имя = 'b' для обновления
Здесь к имени добавляется уникальный индекс.По сути, уникальный индекс является вспомогательным индексом с ограничением уникальности. Таким образом, запись индекса с именем d сначала будет найдена во вспомогательном индексе, заблокирована во вспомогательном индексе, а затем будет произведен поиск в кластеризованном индексе для блокировки соответствующей записи индекса.
Почему записи в кластеризованном индексе также заблокированы?
Только представьте, если есть другой параллельный SQL, который обновляется напрямую через индекс первичного ключа id=30, он сначала запросит блокировку в кластерном индексе. Если заблокирован только вспомогательный индекс, два параллельных SQL не могут воспринимать друг друга.
select lock in share mode
Функция предложения в режиме общего доступа состоит в том, чтобы добавить блокировку общего доступа к найденным данным, что означает, что другие транзакции могут выполнять только простые операции выбора с этими данными и не могут выполнять операции DML.
Используйте сценарий выбора блокировки *** в режиме общего доступа: чтобы гарантировать, что данные, найденные вами, не будут изменены другими транзакциями, то есть, чтобы убедиться, что найденные данные являются последними данными, и другие люди не допускаются изменить данные. Но вы, возможно, не сможете изменить данные самостоятельно, потому что возможно, что другие транзакции также используют метод в режиме общего доступа для блокировки S-блокировки данных.
Разница между обновлением и блокировкой в режиме общего доступа:
Первая представляет собой эксклюзивную блокировку (X-блокировка), когда транзакция получает эту блокировку, другие транзакции не могут выполняться для обновления этих данных; последняя представляет собой общую блокировку, несколько транзакций могут выполняться с одними и теми же данными одновременно блокировка в общем доступе режим.
Влияние явной блокировки на производительность:
Оператор выбора для обновления эквивалентен оператору обновления. В случае загруженности бизнеса, если транзакция не фиксируется или не откатывается вовремя, это может привести к длительному ожиданию других транзакций, что повлияет на эффективность одновременного использования базы данных.
Оператор выбора блокировки в режиме общего доступа — это функция установки общей блокировки (S-блокировки) на искомые данные, которая позволяет другим транзакциям также размещать S-блокировку на данных, но не позволяет модифицировать данные. Если фиксация или откат несвоевременны, это также может привести к большому количеству ожиданий транзакций.
Операция чтения по умолчанию, она заблокирована?
По умолчанию используется механизм MVCC ("consistent nonlocking read-consistent nonlocking read") для обеспечения правильности изоляции на уровне RR и не блокируется.
Вы можете выбрать блокировку вручную: выберите xxxx для обновления (монопольная блокировка); выберите блокировку xxxx в режиме общего доступа (общая блокировка), которая называется «согласованное чтение блокировки».
После использования блокировки можно избежать фантомных чтений на уровне RR. Конечно, чтение MVCC по умолчанию также позволяет избежать фантомных чтений.
Поскольку RR может предотвратить фантомное чтение, в чем польза SERIALIZABLE? Предотвращение потери обновлений.
В настоящее время мы должны использовать уровень SERIALIZABLE для последовательного чтения.
Наконец, принцип реализации блокировки строк заключается в том, чтобы заблокировать кластеризованный индекс.Если вы не нажмете индекс правильно при запросе, оптимизатор MySql откажется от блокировки строки и будет использовать блокировку таблицы.
Различия в последовательном чтении и блокировке InnoDB на разных уровнях изоляции:
Блокировки и многоверсионные данные (MVCC) — это средства InnoDB для достижения согласованного чтения и уровней изоляции ISO/ANSI SQL92.
Следовательно, при разных уровнях изоляции InnoDB использует разные стратегии последовательного чтения и блокировки при обработке SQL:
Для многих SQL, чем выше уровень изоляции, тем строже блокировки, которые InnoDB добавляет к набору записей (особенно при использовании условий диапазона), тем выше вероятность конфликтов блокировок и тем больше влияние на производительность параллельных транзакций.
Поэтому в нашем приложенииВы должны попытаться использовать более низкий уровень изоляции, чтобы уменьшить вероятность конфликта блокировок.. Фактически, за счет оптимизации логики транзакций большинству приложений достаточно использовать уровень изоляции Read Committed. Для некоторых транзакций, которые действительно нуждаются в более высоком уровне изоляции, вы можете динамически изменить уровень изоляции, выполнив команду SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ или SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE в программе, чтобы выполнить требования.
Рекомендации по оптимизации блокировки строк InnoDB
Разумно используйте блокировку InnoDB на уровне строк, чтобы максимизировать сильные стороны и избежать слабых сторон.
Насколько это возможно, весь поиск данных выполняется через индекс, чтобы предотвратить эскалацию InnoDB до блокировки на уровне таблицы, поскольку ключ индекса не может быть заблокирован.
Спроектируйте индекс разумно, чтобы InnoDB мог быть максимально точным при блокировке ключа индекса, максимально сузить диапазон блокировки и избежать ненужной блокировки, которая влияет на выполнение других запросов.
Максимально сведите к минимуму фильтры извлечения данных на основе диапазонов, чтобы избежать блокировки записей, которые не должны блокироваться из-за негативного влияния блокировки пробелов.
Старайтесь контролировать размер транзакции, чтобы уменьшить количество заблокированных ресурсов и продолжительность блокировки.
Если бизнес-среда позволяет, попробуйте использовать более низкий уровень изоляции транзакций, чтобы снизить дополнительные затраты на MySQL из-за реализации уровня изоляции транзакций.
Когда infoDB добавляет блокировки таблиц
Для таблиц InnoDB в большинстве случаев следует использовать блокировки на уровне строк, потому что транзакции и блокировки строк часто являются причинами, по которым мы выбираем таблицы InnoDB.
Транзакция должна обновить большую часть или все данные, а таблица относительно велика. Если используется блокировка строки по умолчанию, не только эффективность выполнения транзакции будет низкой, но и другие транзакции могут вызвать длительное ожидание блокировки и конфликты блокировок. В этом случае вы можете рассмотреть возможность использования блокировок таблиц для повышения скорости выполнения транзакции.
Транзакции включают несколько таблиц, которые являются сложными и могут вызывать взаимоблокировки, что приводит к большому количеству откатов транзакций. В этом случае вы также можете рассмотреть возможность одновременной блокировки таблиц, участвующих в транзакции, чтобы избежать взаимоблокировки и уменьшить нагрузку на базу данных из-за отката транзакции.
В InnoDB обратите внимание на следующие два момента при использовании блокировок таблиц:
Использование LOCK TABLES может добавить блокировки на уровне таблиц в InnoDB, но следует отметить, что блокировки таблиц управляются не уровнем механизма хранения InnoDB, а верхним уровнем, сервером MySQL. Только когда autocommit=0 (не автофиксация, по умолчанию используется автоматическая фиксация), InnoDB_table_locks=1 (настройка по умолчанию), слой InnoDB может знать блокировки таблиц, добавленные MySQL, и сервер MySQL также может воспринимать блокировки строк, добавленные InnoDB. В этом случае InnoDB может автоматически идентифицировать взаимоблокировки, связанные с блокировками на уровне таблицы, в противном случае InnoDB не сможет автоматически обнаруживать и обрабатывать такие взаимоблокировки.
При использовании LOCK TABLES для блокировки таблиц InnoDB обратите внимание на установку AUTOCOMMIT в 0, иначе MySQL не заблокирует таблицу. Не снимайте блокировки таблиц с помощью UNLOCK TABLES, пока транзакция не завершится, поскольку UNLOCK TABLES неявно фиксирует транзакцию. COMMIT или ROLLBACK не могут снять блокировки на уровне таблицы, добавленные с помощью LOCK TABLES, блокировки таблицы должны быть сняты с помощью UNLOCK TABLES.
См. следующий оператор для правильного пути Например, если вам нужно записать в таблицу t1 и прочитать из таблицы t, вы можете сделать это следующим образом:
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;Блокировка таблицы MyISAM
Режим блокировки на уровне таблицы MyISAM:
Блокировка общего чтения таблицы (блокировка чтения таблицы): не будет блокировать запросы других пользователей на чтение в ту же таблицу, но будет блокировать запросы на запись в ту же таблицу;
Блокировка записи таблицы: блокирует операции чтения и записи других пользователей в той же таблице;
Операции чтения и записи таблицы MyISAM, а также между операциями записи выполняются последовательно.. Когда поток получает блокировку записи в таблице, только поток, удерживающий блокировку, может обновить таблицу. Операции чтения и записи другими потоками будут ждать снятия блокировки.
по умолчанию,Блокировки записи имеют более высокий приоритет, чем блокировки чтения.: Когда блокировка снимается, она отдает приоритет запросу на получение блокировки, ожидающему в очереди блокировки на запись, а затем запросу на получение блокировки, ожидающему в очереди блокировки на чтение. (Это гарантирует, что обновления в таблице не будут «зависать от голода», даже если для таблицы выполняется интенсивная операция SELECT. Однако, если для таблицы имеется много обновлений, операторы SELECT ждут, пока не закончатся обновления.).
Вот почему таблицы MyISAM не подходят для приложений с большим количеством операций обновления и операций запросов, поскольку большое количество операций обновления затруднит получение блокировок чтения операциями запросов, которые могут заблокироваться навсегда. В то же время некоторые операции запросов, которые должны выполняться в течение длительного времени, также будут "голодать" поток записи.В приложении старайтесь избегать длительных операций запросов (по возможности можно использовать промежуточные таблицы и другие меры для операторы SQL Некоторая «декомпозиция», чтобы каждый шаг запроса мог быть выполнен за короткое время, тем самым уменьшая конфликты блокировок.Если сложные запросы неизбежны, они должны выполняться в период простоя базы данных как можно Например, некоторые периодические статистические данные могут выполняться ночью).
Вы можете установить приоритет смены блокировок чтения и блокировок записи:
Указав параметр запуска low-priority-updates, механизм MyISAM по умолчанию отдает приоритет запросам на чтение.
При выполнении команды SET LOW_PRIORITY_UPDATES=1 снижается приоритет запроса на обновление, выдаваемого этим соединением.
Уменьшите приоритет инструкции INSERT, UPDATE, DELETE, указав свойство LOW_PRIORITY инструкции.
Установите соответствующее значение для системного параметра max_write_lock_count.Когда блокировка чтения таблицы достигает этого значения, MySQL временно снижает приоритет запроса на запись, давая процессу чтения определенный шанс получить блокировку.
Метод блокировки таблицы MyISAM plus:
Перед выполнением оператора запроса (SELECT) к задействованной таблице будет автоматически добавлена блокировка чтения.
Перед выполнением операции обновления (UPDATE, DELETE, INSERT и т. д.) в задействованные таблицы автоматически добавляется блокировка записи
Этот процесс не требует вмешательства пользователя, поэтому обычно пользователям не нужно явно блокировать таблицы MyISAM напрямую с помощью команды LOCK TABLE.
В случае автоматической блокировки MyISAM всегда одновременно получает все блокировки, требуемые оператором SQL, поэтому таблица MyISAM не имеет взаимоблокировки (Deadlock Free)..
Механизм хранения MyISAM поддерживает одновременные вставки, чтобы уменьшить конкуренцию между операциями чтения и записи для данной таблицы:
Строки всегда вставляются в конец файла данных, если в таблице MyISAM нет свободных блоков в середине файла данных. В этом случае вы можете смешивать и сопоставлять операторы INSERT и SELECT, используя таблицу MyISAM одновременно без блокировки — вы можете вставлять строки в таблицу MyISAM одновременно с чтением другими потоками. Свободные блоки в середине файла могут быть результатом удаления или обновления строк из середины таблицы. Параллельные вставки отключаются, если в середине файла есть свободные блоки, но автоматически снова включаются, когда все свободные блоки заполняются новыми данными. Для управления этим поведением можно использовать системную переменную MySQL concurrent_insert.
Если вы получаете блокировку таблицы явно с помощью LOCK TABLES, вы можете запросить блокировку READ LOCAL вместо блокировки READ, чтобы другие сеансы могли использовать параллельные вставки, пока таблица заблокирована.
Когда для параметра concurrent_insert установлено значение 0, параллельные вставки не допускаются.
Когда concurrent_insert имеет значение 1, MyISAM позволяет одному потоку читать таблицу, в то время как другой поток вставляет записи с конца таблицы, если в таблице MyISAM нет дыр (то есть нет удаленных строк в середине таблицы). ). Это также настройка по умолчанию для MySQL.
Когда для параметра concurrent_insert установлено значение 2, одновременная вставка записей в конец таблицы разрешена независимо от наличия дыр в таблице MyISAM.
показать оператор блокировки sql
Общая блокировка чтения: блокировка таблицы tableName для чтения
Эксклюзивная блокировка записи: блокировка таблицы tableName на запись
Одновременное добавление нескольких блокировок: таблица блокировок t1 для записи, t2 для чтения
Пакетная разблокировка: разблокировка столов
Рекомендации по оптимизации блокировки таблицы MyISAM
Сократите время блокировки
Единственный способ - максимально сократить время выполнения нашего запроса.
Сведите к минимуму большие и сложные запросы и разделите сложные запросы на несколько небольших распределений запросов.
Создайте как можно более эффективный индекс, чтобы ускорить поиск данных.
Попробуйте позволить таблице механизма хранения MyISAM хранить только необходимую информацию и управлять типом поля.
Воспользуйтесь правильными возможностями для оптимизации файлов данных таблиц MyISAM.
Разделение операций, которые можно распараллелить
Механизм хранения MyISAM имеет параметр параметра, который определяет, следует ли включать функцию параллельной вставки:
concurrent_insert=2, независимо от наличия дыр в таблице MyISAM, разрешает одновременную вставку записей в конец таблицы.
concurrent_insert=1, если в таблице MyISAM нет дыр (то есть нет удаленных строк в середине таблицы), MyISAM разрешает одному процессу читать таблицу, а другому процессу вставлять записи с конца таблицы. Это также настройка по умолчанию для MySQL.
concurrent_insert=0, параллельные вставки не разрешены
Функция параллельной вставки механизма хранения MyISAM может использоваться для разрешения конфликта блокировок между запросами и вставками в одной и той же таблице в приложении.
Например, установка для системной переменной concurrent_insert значения 2 всегда будет разрешать одновременные вставки, в то же время периодически выполняя оператор OPTIMIZE TABLE в периоды бездействия системы для дефрагментации пространства и восстановления промежуточных дыр, вызванных удалением записей.
Рациональное использование приоритета чтения и записи
Выполняя команду SET LOW_PRIORITY_UPDATES=1, приоритет чтения соединения выше, чем приоритет записи.Если наша система является системой, основанной на чтении, этот параметр можно установить.Если она основана на записи, нет необходимости установить его.
Понизьте приоритет оператора INSERT, UPDATE, DELETE, указав атрибут LOW_PRIORITY этого оператора.
MySQL также предоставляет компромиссный метод для настройки конфликтов чтения и записи, то есть для установки соответствующего значения для системного параметра max_write_lock_count.Когда блокировка чтения таблицы достигает этого значения, MySQL временно снижает приоритет запроса на запись, давая Процесс чтения должен иметь возможность получить блокировку.
Операции запросов, требующие длительного выполнения, также "заставят голодать" процесс записи. Старайтесь избегать длительных операций запросов. Не всегда пытайтесь использовать оператор SELECT для решения проблемы, потому что этот, казалось бы, оригинальный оператор SQL часто оказывается более сложно и требует много времени на выполнение.
Многостоловый каскад. Транзакции включают несколько таблиц, а сложные связанные запросы, вероятно, вызовут взаимоблокировки и вызовут большое количество откатов транзакций.В этом случае, если таблицы, участвующие в транзакции, могут быть заблокированы одновременно, взаимоблокировок можно избежать, а снижается из-за отката транзакций.
Тупик Бесплатно
Тупик производит:
Тупик означает, что две или более транзакций занимают друг друга на одном и том же ресурсе и запрашивают блокировку ресурсов, занятых друг другом, что приводит к порочному кругу.
Взаимоблокировки могут возникать, когда транзакции пытаются заблокировать ресурсы в другом порядке. Взаимоблокировки также могут возникать, когда несколько транзакций одновременно блокируют один и тот же ресурс.
Поведение и порядок блокировок зависят от механизма хранения. Выполняя операторы в одном и том же порядке, некоторые подсистемы хранения будут заблокированы, а некоторые нет - тупиковые блокировки по двум причинам: реальные конфликты данных и то, как реализована подсистема хранения.
Обнаружить взаимоблокировку:В системе базы данных реализованы различные механизмы обнаружения взаимоблокировок и тайм-аута взаимоблокировок. Механизм хранения InnoDB обнаруживает заблокированные циклические зависимости и немедленно возвращает ошибку.
Восстановление тупика:После возникновения тупиковой ситуации тупиковую ситуацию можно преодолеть только путем частичного или полного отката одной из транзакций.В настоящее время InnoDB обрабатывает взаимоблокировки путем отката транзакции, которая содержит наименьшее количество эксклюзивных блокировок на уровне строк. Таким образом, при проектировании транзакционных приложений необходимо учитывать, как справляться с взаимоблокировками.В большинстве случаев требуется повторное выполнение только тех транзакций, которые были отменены из-за взаимоблокировок.
Обнаружение взаимоблокировки для внешних блокировок:После возникновения взаимоблокировки InnoDB обычно может обнаружить ее автоматически и заставить одну транзакцию снять блокировку и выполнить откат, а другая транзакция получает блокировку и продолжает выполнять транзакцию. Однако в случае внешних блокировок или блокировок таблиц InnoDB не может полностью автоматически обнаруживать взаимоблокировки, что необходимо решить, установив параметр тайм-аута ожидания блокировки innodb_lock_wait_timeout.
Взаимоблокировка влияет на производительность:Взаимоблокировки влияют на производительность, а не вызывают критические ошибки, поскольку InnoDB автоматически обнаруживает условия взаимоблокировки и откатывает одну из затронутых транзакций. В системах с высокой степенью параллелизма обнаружение взаимоблокировок может вызвать замедление работы, когда несколько потоков ожидают одной и той же блокировки. Иногда может быть более эффективным отключить обнаружение взаимоблокировки (используя параметр конфигурации innodb_deadlock_detect), когда возникает взаимоблокировка, и вы можете положиться на параметр innodb_lock_wait_timeout для отката транзакции.
MyISAM избегает взаимоблокировок:
В случае автоматической блокировки таблица MyISAM не будет заблокирована.(MyISAM всегда сразу получает все блокировки, требуемые оператором SQL).
InnoDB избегает взаимоблокировок:
Чтобы избежать взаимоблокировок при выполнении нескольких одновременных операций записи в одной таблице InnoDB, необходимые блокировки могут быть получены в начале транзакции с помощью оператора SELECT ... FOR UPDATE для каждого кортежа (строки), который, как ожидается, будет изменен, даже если Операторы изменений для этих строк выполняются позже.
В транзакции, если вы хотите обновить запись, вы должны напрямую применить достаточный уровень блокировки, то есть эксклюзивную блокировку, вместо того, чтобы сначала применять общую блокировку, а затем применять эксклюзивную блокировку при обновлении, потому что в это время, когда пользователь подает заявку на монопольную блокировку, другие транзакции могут быть снова получены совместно используемой блокировкой той же записи, что приводит к конфликтам блокировок и даже взаимоблокировкам.
Если транзакции необходимо изменить или заблокировать несколько таблиц, операторы блокировки должны использоваться в одном и том же порядке в каждой транзакции. В приложении, если разные программы будут обращаться к нескольким таблицам одновременно, вы должны попытаться договориться о доступе к таблицам в одном и том же порядке, что может значительно снизить вероятность взаимоблокировок.
После получения блокировки чтения строки с помощью SELECT ... LOCK IN SHARE MODE, если текущей транзакции необходимо обновить запись, это, вероятно, вызовет взаимоблокировку.
Измените уровень изоляции транзакций, например, понизив уровень изоляции (если это позволяет бизнес, также рекомендуется понизить уровень изоляции, например, скорректировать уровень изоляции с RR на RC, что может избежать многих взаимоблокировок, вызванных разрывом). замки)
Добавьте в таблицу разумные индексы. Видно, что если индекс не использовать, то к каждой строке таблицы будет добавлена блокировка, и вероятность взаимоблокировки сильно возрастет.
В случае взаимоблокировки можно использовать команду SHOW INNODB STATUS, чтобы определить причину последней взаимоблокировки. Возвращаемый результат включает в себя подробную информацию о транзакциях, связанных с взаимоблокировкой, например оператор SQL, вызвавший взаимоблокировку, блокировки, полученные транзакцией, ожидающие блокировки и транзакции, для которых был выполнен откат. Исходя из этого, можно проанализировать причину тупика и меры по улучшению.
Анализ обработки блокировки
Следующие два простых SQL-запроса, какие блокировки они добавляют?
select * from t1 where id = 10
delete from t1 where id = 10
Если вы хотите проанализировать ситуацию блокировки, вы также должны знать следующую предпосылку: предпосылка другая, и способ блокировки другой.
Является ли столбец id первичным ключом?
Каков уровень изоляции текущей системы?
Если столбец id не является первичным ключом, есть ли индекс в столбце id?
Если в столбце id есть вторичный индекс, является ли этот индекс уникальным?
Каковы планы выполнения двух SQL? Индексное сканирование? Полное сканирование таблицы?
В соответствии с приведенной выше ситуацией, существуют следующие комбинации
Столбец id — это первичный ключ, уровень изоляции RC
Столбец id — это вторичный уникальный индекс, уровень изоляции RC
Столбец id — это вторичный неуникальный индекс, уровень изоляции RC
Нет индекса в столбце id, уровень изоляции RC
Столбец id — это первичный ключ, уровень изоляции RR.
Столбец id — это вторичный уникальный индекс, уровень изоляции RR
Столбец id — это вторичный неуникальный индекс, уровень изоляции RR
Нет индекса в столбце id, уровень изоляции RR
Сериализуемый уровень изоляции
Перестановки и комбинации не перечислены полностью, но кажется, что их уже много. Неужели обязательно так усложнять? На самом деле, чтобы анализировать блокировки, это нужно так сложно. Но с другой точки зрения, пока вы выбираете комбинацию, которая блокирует SQL, которая должна быть добавлена, фактически определяется. Затем выберите еще несколько классических комбинаций.
1. идентификатор первичного ключа + RC
Эта комбинация является самой простой и легкой для анализа. id — это первичный ключ, уровень изоляции Read Committed, заданный SQL: удалить из t1, где id = 10; Просто добавить блокировку X к записи с id = 10 в первичном ключе. Как показано на рисунке 1 ниже:
Вывод: когда id является первичным ключом, этому SQL нужно только добавить блокировку X на запись с id=10.
2. id уникальный индекс + RC
В этой комбинации id является не первичным ключом, а уникальным значением ключа вторичного индекса. Тогда при уровне изоляции RC, какую блокировку нужно удалить из t1, где id = 10; нужно добавить? См. рисунок 2 ниже:
id — это уникальный индекс, а первичный ключ — это столбец имени. В настоящее время ситуация блокировки отличается из-за комбинации 1. Поскольку идентификатор является уникальным индексом, оператор удаления выберет индекс столбца идентификатора для фильтрации условия «где».После обнаружения записи с идентификатором = 10 он сначала добавит блокировку X к записи индекса с идентификатором = 10 в уникальном index, и в то же время в соответствии со столбцом прочитанного имени вернуться к индексу первичного ключа (кластеризованному индексу), а затем добавить блокировку X к элементу индекса первичного ключа, соответствующему 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, тогда какие блокировки будут удерживаться в это время? См. также рисунок 3 ниже:По этому рисунку видно, что в первую очередь по индексу столбца id были заблокированы записи, удовлетворяющие условию запроса id = 10. При этом также блокируются записи индекса первичного ключа, соответствующие этим записям. Единственное отличие от комбинации 2 состоит в том, что комбинация 2 имеет не более одной записи, удовлетворяющей эквивалентному запросу, а комбинация 3 блокирует все записи, удовлетворяющие условиям запроса.
Вывод: если в столбце id есть неуникальный индекс, все соответствующие записи, удовлетворяющие условиям SQL-запроса, будут заблокированы. В то же время эти записи в индексе первичного ключа также будут заблокированы.
4. id неуникальный индекс + RR
Помните разницу между четырьмя уровнями изоляции MySQL, упомянутыми ранее? Уровень изоляции RC разрешает фантомное чтение, а уровень изоляции RR не разрешает фантомное чтение. Но в комбинации 5 и комбинации 6 поведение блокировки точно такое же, как поведение блокировки при RC. Затем на уровне изоляции RR
Комбинация 7, уровень изоляции Repeatable Read, имеется неуникальный индекс по id, выполнить удаление из t1, где id = 10; Предполагая, что индекс в столбце id выбран для условной фильтрации, каково окончательное поведение блокировки? Также посмотрите на рисунок 1 ниже:
Вывод: на уровне изоляции Repeatable Read имеется неуникальный индекс в столбце id, соответствующий SQL: удалить из t1, где id = 10; Сначала найти первую запись, удовлетворяющую условиям запроса, через индекс id, и добавьте блокировку X на запись, добавьте блокировку GAP на GAP, затем добавьте блокировку X записи на кластеризованный индекс первичного ключа, а затем вернитесь; затем прочитайте следующую и повторите. Пока не будет достигнута первая запись [11,f], которая не удовлетворяет условиям, в это время нет необходимости добавлять блокировку записи X, но необходимо добавить блокировку GAP и, наконец, вернуться в конец.
Когда будет получен замок gap или nextkey?
5.id без индекса + RC
По сравнению с предыдущими тремя комбинациями, это довольно частный случай. В столбце id нет индекса, где id = 10; это условие фильтрации не может быть отфильтровано по индексу, поэтому его можно отфильтровать только путем полного сканирования таблицы. В соответствии с этой комбинацией, какую блокировку добавит SQL? Или другими словами, какие блокировки будут добавлены при полном сканировании таблицы? Ответов на этот вопрос также много: кто-то говорит, что в таблицу добавится X блокировок, кто-то говорит, что X блокировок добавится к записям с id = 10; выбранным по кластеризованному индексу. А реальная ситуация? См. рисунок 2 ниже:
Поскольку в столбце id нет индекса, для полного сканирования можно использовать только кластеризованный индекс. Как видно из рисунка, есть две записи, удовлетворяющие условиям удаления, но все записи кластеризованного индекса добавляются с X-блокировками. Независимо от того, соответствует ли запись условиям, все заблокированы с помощью X. К записям, удовлетворяющим условиям, не добавляются ни блокировки таблиц, ни блокировки строк.
Кто-то может спросить? Почему бы не блокировать только те записи, которые соответствуют условиям? Это связано с реализацией MySQL. Если условие не может быть быстро отфильтровано индексом, уровень механизма хранения заблокирует все записи и вернет их, а затем их отфильтрует уровень сервера MySQL. Поэтому все записи заблокированы.
Вывод: если в столбце id нет индекса, SQL будет фильтровать через полное сканирование кластеризованного индекса, потому что фильтрация выполняется на уровне сервера MySQL. Поэтому каждая запись, независимо от того, соответствует ли она условиям, будет заблокирована с помощью X. Однако для повышения эффективности MySQL был оптимизирован. Для записей, которые не соответствуют условиям, блокировка будет снята после вынесения решения. Окончательное удержание — это блокировка записи, которая соответствует условию, но блокировка записи который не соответствует условию/ Действие блокировки не будет пропущено. В то же время оптимизация также нарушает ограничения 2PL.
6: id не имеет индекса + RR
Комбинация восемь, последний случай на уровне изоляции Repeatable Read, в столбце id нет индекса. В этот момент SQL: удалить из t1, где id = 10; другого пути на выбор нет, и можно выполнить только полное сканирование таблицы. Окончательная ситуация блокировки показана на рисунке 3:
Вывод: при уровне изоляции Repeatable Read, если выполняется текущее чтение полного сканирования таблицы, все записи в таблице будут заблокированы, и все GAP в кластеризованном индексе будут заблокированы одновременно, предотвращая все одновременные обновления/ операции удаления/вставки. Конечно, вы также можете инициировать полусогласованное чтение, чтобы уменьшить накладные расходы на блокировку и влияние параллелизма, но само по себе полусогласованное чтение также приведет к другим проблемам и не рекомендуется.
7 идентификатор первичного ключа + RR
Вышеуказанные четыре комбинации — это поведение блокировки на уровне изоляции Read Committed, а следующие четыре комбинации — поведение блокировки на уровне изоляции Repeatable Read.
Комбинация 5, столбец id — это столбец первичного ключа, уровень изоляции Repeatable Read, для удаления из t1, где id = 10; этот SQL, блокировка и комбинация 1: [первичный ключ id, зафиксированное чтение] последовательный.
8. Комбинация 6: уникальный индекс id + RR
Подобно пятой комбинации, блокировка шестой комбинации согласуется с комбинацией два: [уникальный индекс id, Read Committed]. Две блокировки X, одна для записи, чей уникальный индекс id удовлетворяет условию, и одна для записи в соответствующем кластеризованном индексе.
9. Комбинация девять: Сериализуемый
Для простого 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, и следующие ситуации также вызовут взаимоблокировку, что относительно неясно. Учащиеся, которым не ясно, могут проанализировать его в соответствии с принципом блокировки пробелов, описанным в предыдущем разделе.
в заключении:В MySQL/InnoDB так называемая блокировка без чтения применяется не ко всем случаям, а связана с уровнем изоляции. Сериализуемый уровень изоляции, чтение больше не устанавливается без блокировки, все операции чтения являются текущими операциями чтения.
Некоторые предложения по оптимизации работы блокировки
Попробуйте использовать более низкий уровень изоляции;
Тщательно спроектируйте индекс и попытайтесь использовать индекс для доступа к данным, чтобы сделать блокировку более точной, тем самым уменьшив вероятность конфликтов блокировок.
Выберите разумный размер транзакции, и у небольших транзакций будет меньше шансов на конфликты блокировок.
При явной блокировке набора записей лучше всего за один раз запросить достаточный уровень блокировки. Например, если вы хотите изменить данные, лучше всего применить эксклюзивную блокировку напрямую, а не сначала применять общую блокировку, а затем запрашивать эксклюзивную блокировку при изменении, что может привести к взаимоблокировкам.
Когда разные программы обращаются к набору таблиц, они должны попытаться согласовать доступ к таблицам в одном и том же порядке.Для таблицы доступ к строкам в таблице должен осуществляться в максимально возможном фиксированном порядке. Это значительно снижает вероятность взаимоблокировок
Попробуйте получить доступ к данным с одинаковыми условиями, чтобы избежать влияния блокировок пробелов на одновременные вставки.
Не применяйте больше уровней блокировки, чем требуется на самом деле
Не отображать блокировки при запросе без необходимости. MVCC MySQL может выполнять запросы в транзакциях без блокировки и оптимизировать производительность транзакций; MVCC работает только при двух уровнях изоляции: COMMITTED READ (фиксация чтения) и REPEATABLE READ (повторяющееся чтение).
Для некоторых конкретных транзакций можно использовать блокировки таблиц для повышения скорости обработки или уменьшения вероятности взаимоблокировок.
Ссылки по теме:
Сводка блокировки MySQLzhuanlan.zhihu.com/p/29150809
Механизм блокировки MySQL — здесь есть все, что вы хотите знать!zhuanlan.zhihu.com/p/75673270
Подробно объясните различные блокировки mysql (блокировки таблицы, блокировки строк, общие блокировки, общие блокировки намерения, блокировки записей, блокировки пробелов и блокировки смежных клавиш).zhuanlan.zhihu.com/p/52312376
Блокировка знаний, которые вы должны знать о MySQLzhuanlan.zhihu.com/p/62525459
Краткое изложение механизма блокировки mysql и предложений по оптимизацииzhuanlan.zhihu.com/p/70889229
Глубокое понимание MySQL — блокировки, транзакции и управление параллелизмом.zhuanlan.zhihu.com/p/36060546
Глубокое понимание блокировок MySQLzhuanlan.zhihu.com/p/8355298
Принцип индекса MySQL (Innodb)zhuanlan.zhihu.com/p/62018452
Два основных артефакта базы данных [индекс и блокировка]zhuanlan.zhihu.com/p/40396971
Глубокое понимание блокировок MySQLzhuanlan.zhihu.com/p/83552985
оригинал:Говоря о механизме и принципе блокировки mysql - интерпретация блокировки - mysql - персональный сайт Чжоу ЦзюняМодификация и обновление только в исходном тексте.Если в тексте есть какие-либо неуместности, пожалуйста, оставьте сообщение, чтобы сообщить.