Введение

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

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

Далее будет объяснено одно за другим в соответствии с приведенным выше рисунком.

Во-вторых, глобальные блокировки, блокировки на уровне таблицы, блокировки на уровне страницы, блокировки на уровне строки.

1. Глобальная блокировка

(1) Концепция

Глобальная блокировка — это блокировка всего экземпляра базы данных.

(2) Сценарии применения

Полное логическое резервное копирование базы данных (mysqldump)

(3) Метод реализации

MySQL предоставляет способ добавить глобальную блокировку чтения, команда Flush table with read lock (FTWRL).

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

точка риска:

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

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

Решение:

mysqldump использует параметр --single-transaction, чтобы запустить транзакцию, чтобы обеспечить согласованное представление. Благодаря поддержке MVCC данные могут нормально обновляться во время этого процесса.

2. Блокировки на уровне таблицы

(1) Концепция

Блокируется вся таблица текущей операции.Наиболее часто используемые MyISAM и InnoDB поддерживают блокировку на уровне таблицы.

В MySQL существует два типа блокировок на уровне таблицы: одна — это блокировка таблицы, а другая — блокировка метаданных (MDL).

(2) Метод реализации

Блокировка таблицы: блокировка таблиц ... чтение/запись;

Например, если используется команда блокировки чтения t1 и записи t2, операторы других потоков, записывающих t1 и читающих и записывающих t2, будут заблокированы. В то же время поток A может выполнять только операции чтения t1 и чтения и записи t2 до выполнения таблиц разблокировки. Даже запись t1 не разрешена, и, естественно, вы не можете получить доступ к другим таблицам до разблокировки таблиц.

Блокировка метаданных: MDL не нужно использовать явно. Он будет автоматически добавлен при доступе к таблице. MDL был введен в MySQL 5.5. При добавлении, удалении, изменении и запросе таблицы добавляется блокировка чтения MDL. хотите внести структурные изменения в таблицу, добавьте блокировку записи MDL.

(3) Точки риска

Ссылаться на:блог woo woo woo.cn на.com/can/afraid/1106…

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

1. sessionA:

begin;

select* from t limit 1;

2. sessionB:

select* from t limit 1;

3. sessionC:

altertable t add f int;

#заблокирует ли mdl

4. sessionD:

select* from t limit 1;

Мы видим, что сначала запускается сеанс A, а затем к таблице t добавляется блокировка чтения MDL. Поскольку сеанс B также нуждается в блокировке чтения MDL, он может выполняться в обычном режиме.

После этого сеанс C будет заблокирован, поскольку блокировка чтения MDL сеанса A не снята, а сеансу C требуется блокировка записи MDL, поэтому его можно только заблокировать.

Не имеет значения, если заблокирован только сам сеанс C, но все последующие запросы на применение новой блокировки чтения MDL для таблицы t также будут заблокированы сеансом C. Я сказал раньше,所有对表的增删改查操作都需要先申请MDL 读锁，而这时读锁没有释放，对表alter ，产生了mdl写锁，把表t锁住了，这时候就对表t完全不可读写了.

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

Блокировка MDL в транзакции применяется в начале выполнения оператора, но не будет снята сразу после завершения оператора, а будет снята после фиксации всей транзакции.
**注 ：一般行锁都有锁超时时间。但是MDL锁没有超时时间的限制，只要事务没有提交就会一直锁注。**

(4) Решение

Прежде всего, нам нужно решить длинную транзакцию, если транзакция не зафиксирована, она всегда будет занимать блокировку MDL. В таблице innodb_trx библиотеки MySQL information_schema вы можете найти текущую выполняемую транзакцию. Если в таблице, в которую вы хотите внести изменения DDL, выполняется длинная транзакция, сначала рассмотрите возможность приостановки DDL или уничтожения длинной транзакции. Вот почему изменения ddl необходимо вносить в периоды низкой пиковой нагрузки.

3. Блокировка уровня страницы

(1) Концепция

Блокировка на уровне страницы — это блокировка в MySQL, степень детализации блокировки которой находится между блокировками на уровне строк и блокировками на уровне таблицы. Блокировки на уровне таблицы работают быстро, но имеют много конфликтов, а блокировки на уровне строк имеют меньше конфликтов, но работают медленно. Таким образом, применяется компрометирующая блокировка на уровне страницы, одновременно блокирующая смежный набор записей. Механизм BDB поддерживает блокировку на уровне страницы.

4. Блокировка на уровне строки

(1) Концепция

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

В MySQL только InnoDB поддерживает блокировки на уровне строк.Блокировки на уровне строк делятся на разделяемые блокировки и эксклюзивные блокировки.

(2) Метод реализации

В MySQL блокировки на уровне строк блокируют не записи, а индексы. Индексы делятся на индексы с первичным ключом и индексы с непервичным ключом.Если оператор SQL оперирует с индексом с первичным ключом, MySQL блокирует индекс с первичным ключом; индекс первичного ключа, а затем заблокировать индекс непервичного ключа.Связанный индекс первичного ключа. Во время операций UPDATE и DELETE MySQL не только блокирует все записи индекса, просмотренные условием WHERE, но также блокирует соседние значения ключа, что называется блокировкой следующего ключа.

(3) Настоящий бой

Давайте продемонстрируем производительность блокировок строк и выполним операции обновления в сеансах session1 и session2 соответственно, чтобы увидеть, будут ли они заблокированы.

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

Итак, если session2 выполнит операцию с id=2, будет ли она успешной?

Операция с id=2 может быть успешной.

3. Оптимистичная блокировка и пессимистическая блокировка

1. Оптимистичная блокировка

(1) Концепция

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

(2) Сценарии применения

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

(3) Метод реализации

Как правило, для реализации используется механизм записи версии данных (Version), а для реализации в таблицу базы данных добавляется числовое поле «версия». При чтении данных вместе считывайте значение поля версии и добавляйте единицу к значению версии каждый раз при обновлении данных. Когда мы отправляем обновление, сравниваем информацию о текущей версии соответствующей записи в таблице базы данных со значением версии, извлеченным в первый раз.Если номер текущей версии таблицы базы данных равен значению версии, извлеченному для первого время, они будут обновлены, в противном случае они считаются просроченными данными.

(4) Настоящий бой

Идентификатор, статус и версия в таблице заказов представляют собой идентификатор заказа, статус заказа и номер версии соответственно.

1. Запрос информации о заказе

select id,status,versionfrom order where id=#{id};

2. Пользователь успешно платит

3. Изменить статус заказа

update set status=оплата прошла успешно,version=version+1 where id=#{id} and version=#{ version};

2. Пессимистическая блокировка

(1) Концепция

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

(2) Сценарии применения

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

(3) Метод реализации

Чтобы использовать пессимистическую блокировку в MySQL, необходимо отключить автофиксацию MySQL, установить autocommit=0. Разделяемые блокировки и эксклюзивные блокировки — это разные реализации пессимистической блокировки, и обе относятся к категории пессимистической блокировки.

(4) Настоящий бой

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

1. Включите транзакцию и отключите автофиксацию

setautocommit=0;

2. Запросить информацию о продукте

selectid,name,number from goods where id=1 for update;

3. Пользователь размещает заказ и формирует заказ

4. Изменить инвентарь продукта

updateset number= number-1 where id=1;

5. Зафиксируйте транзакцию

commit;

Примечание: select...for update — это способ реализации пессимистичных блокировок, предоставляемых MySQL, который относится к эксклюзивным блокировкам.В таблице товаров данные с id 1 заблокированы текущей транзакцией, а остальные нужно выполнить select id ,название ,номер из товаров где id=1для обновления Транзакция может быть выполнена только после совершения транзакции. Таким образом, мы можем гарантировать, что текущие данные не будут изменены другими транзакциями.

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

В-четвертых, общие блокировки и монопольные блокировки

1. Общий замок

(1) Концепция

Общие блокировки, также известные как блокировки чтения или S-блокировки для краткости, когда транзакция A добавляет блокировки чтения к данным, другие транзакции могут только добавлять блокировки чтения к данным и не могут выполнять какие-либо операции модификации, то есть блокировки записи не могут быть добавлены. . Только после снятия блокировки чтения с транзакции A другие транзакции могут добавить к ней блокировки записи.

(2) Сценарии применения

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

Он подходит для операций записи, когда между двумя таблицами существует связь.В качестве примера возьмем официальную документацию MySQL.Одна таблица является дочерней, а другая – родительской.Предполагая, что столбец child_id дочерней таблицы сопоставляется с c_child_id родительской таблицы, то из бизнеса. С точки зрения, мне рискованно вставлять запись child_id=100 непосредственно в дочернюю таблицу, потому что запись c_child_id=100 может быть удалена в родительской таблице, когда я только что вставлен, поэтому существует риск несогласованности бизнес-данных. Правильный метод - выполнить выбор * из родителя, где c_child_id=100 заблокировать в режиме общего доступа при повторной вставке, заблокировать эту запись в родительской таблице, а затем выполнить вставку в дочерние (child_id) значения (100), не будет такая проблема. .

(3) Метод реализации

выберите … заблокировать в режиме общего доступа

(4) Настоящий бой

session1 удерживает общую блокировку и не фиксируется. Запрос сеанса2 не затрагивается, но операция обновления будет заблокирована до истечения времени ожидания.

2. Эксклюзивный замок

(1) Концепция

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

По умолчанию механизм MySQL InnoDB автоматически добавляет эксклюзивные блокировки к данным, участвующим в обновлении, удалении и вставке, а оператор select по умолчанию не добавляет никаких типов блокировки.

(2) Сценарии применения

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

(3) Метод реализации

выберите … для обновления

(4) Настоящий бой

Запрос эксклюзивной блокировки Session1. Session2 также выполняет запросы монопольной блокировки и будет заблокирован.

Пять, намеренная совместная блокировка и намеренная монопольная блокировка.

1. Концепция

Преднамеренные блокировки — это блокировки таблиц.Чтобы согласовать взаимосвязь между блокировками строк и блокировками таблиц, мультигранулярные блокировки (блокировки таблицы и блокировки строк) могут сосуществовать.

2. Функция

Когда транзакция A имеет блокировку строки, MySQL автоматически добавит блокировку намерения к таблице.Если транзакция B хочет применить блокировку записи для всей таблицы, ей не нужно проходить каждую строку, чтобы определить, есть ли блокировка строки , а напрямую определить, есть ли блокировка намерений. Повышенная производительность.

3. Совместимость и взаимное исключение блокировок намерений

4. Практическое примечание: эксклюзивность здесь / Общие блокировки относятся к блокировкам таблицы! ! ! Блокировки намерения не используются совместно с блокировками на уровне строк. / Эксклюзивный блокирующий мьютекс! ! !

Session1 получил монопольную блокировку строки и не зафиксировал:

select*from goods where id=1 for update;

На данный момент в таблице товаров есть две блокировки: преднамеренная эксклюзивная блокировка таблицы товаров и эксклюзивная блокировка строки данных с идентификатором 1.

session2 хочет получить общую блокировку таблицы товаров:

LOCK TABLES goods READ;

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

Шесть, замок с зазором, замок с ключом, замок с записью

• концепция

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

• блокировка записи

Блокировки записей блокируют записи, блокировки записей также называют блокировками строк, например:

select *from goods where **`id`=**1 for update;

Он добавит блокировку записи к записи с id=1, чтобы предотвратить вставку, обновление и удаление строки с id=1 другими транзакциями.

• гэп замок

Гэп-блокировки основаны на неуникальных индексах, которые блокируют диапазон записей индекса. Использование блокировок пробелов блокирует интервал, а не только каждую часть данных в этом интервале.

select* from goods where id between 1 and 10 for update;

То есть все строки в интервале (1, 10) будут заблокированы, и вставка всех строк с id 2, 3, 4, 5, 6, 7, 8 и 9 будет заблокирована, но 1 и 10 будут заблокированы. быть заблокирована Строка записи не заблокирована.

• Про ключевой замок

Блокировка близости ключа представляет собой комбинацию блокировки записи и блокировки промежутка.Его диапазон блокировки включает как индексную запись, так и индексный интервал, который является левым открывающим и правым закрывающим интервалом. Основная цель блокировки клавиш — избежать фантомного чтения. Если уровень изоляции транзакции будет понижен до RC, блокировка ключа также не удастся.

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

Скрытые ключевые замки в таблице товаров: (-∞, 96], (96, 99], (99, +∞]

В то время как session1 выполняет операцию обновления для столбца с номером 96, он также получает блокировки ключей в двух интервалах (-∞, 96], (96, 99].

Наконец, мы можем знать, что когда UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE выполняется для строки записи в соответствии с неуникальным индексом, InnoDB получит блокировку ключа строки записи Формула: блокировка левого промежутка + запись замок + замок правого зазора.

То есть после того, как session1 выполнит приведенный выше SQL, окончательный интервал заблокированной записи будет равен (-∞, 99).

Приглашаем всех обратить внимание на публичный аккаунт WeChat: CodingTao