Ставьте лайк и смотрите снова, вырабатывайте привычку, ищите в паблике [дайм тек] Обратите внимание на более оригинальные технические статьи. эта статьяGitHub org_hejianhui/JavaStudyБыл включен, есть моя серия статей.
предисловие
- Базовая структура данных и алгоритм индекса MySQL
- Принципы оптимизации производительности MySQL — часть 1
- Оптимизация производительности MySQL — практика 1
- Оптимизация производительности MySQL — практика 2
- Блокировка MySQL и уровень изоляции транзакций
Ранее мы говорили о базовых структурах данных и алгоритмах базы данных MySQL, а также о некотором содержании оптимизации производительности MySQL. И последняя часть говорила о блокировке строк MySQL и уровне изоляции транзакций. В этой статье речь пойдет о типе замка и принципе блокировки.
Сначала разделите блокировку mysql:
- По степени детализации блокировок: блокировки строк, блокировки таблиц, блокировки страниц
- По использованию блокировок: разделяемые блокировки, эксклюзивные блокировки (реализация пессимистичных блокировок)
- Имеются в виду также два типа замков: пессимистичные замки и оптимистичные замки.
- В InnoDB существует несколько типов блокировок на уровне строк: блокировка записи, блокировка промежутка, блокировка следующего ключа.
- Блокировка записи: блокирует записи индекса.
- Гэп-замок: Гэп-замок
- Блокировка следующей клавиши: Блокировка записи + Блокировка промежутка
блокировка стола
Блокировки на уровне таблицы являются наиболее детализированными блокировками среди блокировок MySQL, что указывает на то, что текущая операция блокирует всю таблицу.Меньше накладных расходов на ресурсы, чем при блокировке строк, отсутствие взаимоблокировок, но высокая вероятность конфликтов блокировок.. Поддерживаемые большинством движков mysql, MyISAM и InnoDB поддерживают блокировки на уровне таблиц, но InnoDB по умолчанию использует блокировки на уровне строк.
Блокировки таблиц реализованы сервером MySQL.Как правило, вся таблица блокируется при выполнении операторов DDL, таких как ALTER TABLE и других операций. При выполнении оператора SQL вы также можете явно указать блокировку таблицы.
Блокировка таблицы использует технологию одноразовой блокировки, то есть используйте команду блокировки в начале сеанса, чтобы заблокировать все таблицы, которые необходимо использовать позже. доступ, а не к другим таблицам, пока все блокировки таблиц не будут сняты с помощью разблокировки таблиц в конце.
В дополнение к использованию таблиц разблокировки для явного снятия блокировок выполнение оператора блокировки таблицы, когда сеанс удерживает другие блокировки таблиц, снимает блокировки, ранее удерживаемые сеансом; когда сеанс удерживает другие блокировки таблиц, выполнение стартовой транзакции или начало открытия транзакции также снимет предыдущие блокировки.
Использование общей блокировки:
LOCK TABLE table_name [ AS alias_name ] READ
Эксклюзивное использование блокировки:
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE
Разблокировать использование:
unlock tables;
блокировка строки
Блокировка на уровне строки — это наиболее детальная блокировка в Mysql, что означает, что блокируется только строка текущей операции.Блокировки на уровне строк могут значительно уменьшить количество конфликтов в операциях базы данных. Уровень детализации блокировок наименьший, но затраты на блокировку также самые большие. Может возникнуть тупиковая ситуация.Блокировки на уровне строки делятся на разделяемые блокировки и эксклюзивные блокировки в зависимости от их использования.
Реализация блокировки строк в разных механизмах хранения отличается.Если в дальнейшем нет специального описания, блокировки строк относятся к блокировкам строк, реализованным InnoDB.
Прежде чем понять принцип блокировки InnoDB, вам необходимо иметь определенное представление о структуре его хранилища. InnoDB — это кластеризованный индекс, то есть конечные узлы дерева B+ хранят как индекс первичного ключа, так и строки данных. Листовые узлы вторичного индекса InnoDB хранят значение первичного ключа, поэтому при запросе данных через вторичный индекс вам необходимо использовать соответствующий первичный ключ для повторного запроса в кластеризованном индексе. Подробные сведения об индексах MySQL можно просмотреть по адресу«Индекс MySQL, лежащий в основе структуры данных и алгоритма».
В следующем примере выполнение двух SQL-запросов используется для объяснения принципа блокировки InnoDB для однострочных данных.
update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
Первый SQL использует индекс первичного ключа для запроса, вам нужно только добавить блокировку записи для индекса первичного ключа id = 49;
Второй SQL использует вторичный индекс для запроса, сначала добавьте блокировку записи для имени индекса = Tom, а затем используйте вторичный индекс InnoDB для повторного запроса на основе индекса первичного ключа, поэтому вам также необходимо использовать id = 49 первичный key index Добавьте к нему блокировку записи, как показано выше.
То есть, чтобы использовать индекс первичного ключа, вам нужно добавить блокировку, а чтобы использовать вторичный индекс, вам нужно добавить блокировку к вторичному индексу и индексу первичного ключа.
Я понимаю принцип блокировки обновления одной строки данных в соответствии с индексом.Что делать, если операция обновления включает несколько строк, как в следующем сценарии выполнения SQL.
update user set age = 10 where id > 49;
Снятие блокировки в этом сценарии более сложное, и есть много способов его оптимизации, я пока ничего об этом не знаю, пожалуйста, оставьте сообщение ниже для тех, кто это знает.
блокировка страницы
Блокировка на уровне страницы — это блокировка в MySQL, степень детализации блокировки которой находится между блокировками на уровне строк и блокировками на уровне таблицы. Блокировки на уровне таблицы работают быстро, но возникают много конфликтов, а блокировки на уровне строк немногочисленны, но медленны. Так что возьмите компромиссный уровень страницы, заблокируйте соседние группы записей за раз. BDB поддерживает блокировки на уровне страниц.
Общий замок/эксклюзивный замок
Поделиться блокировкой
Общие блокировки, также известные как блокировки чтения, — это блокировки, создаваемые операциями чтения. Другие пользователи могут читать данные одновременно, но ни одна транзакция не может вносить изменения в данные (получать эксклюзивную блокировку данных), пока не будут сняты все общие блокировки.
Если транзакция T добавляет общую блокировку к данным A, другие транзакции могут добавлять только общие блокировки к A, но не эксклюзивные блокировки. Транзакции, которым предоставлена общая блокировка, могут только читать данные и не могут изменять данные.
Применение
SELECT ... LOCK IN SHARE MODE;
добавить после запросаLOCK IN SHARE MODE, Mysql добавит разделяемую блокировку к каждой строке в результатах запроса. Если ни один другой поток не использует монопольную блокировку какой-либо строки в наборе результатов запроса, он может успешно применить общую блокировку, в противном случае он будет заблокирован. Другие потоки также могут читать таблицу, используя общую блокировку, и эти потоки читают одну и ту же версию данных.
Эксклюзивный замок (eXclusive Lock)
Эксклюзивная блокировка также называется блокировкой записи.Если транзакция T добавляет монопольную блокировку к данным A, другие транзакции не могут добавлять блокировку любого типа к A. Транзакции, которым предоставлены эксклюзивные блокировки, могут как читать, так и изменять данные.
Применение
SELECT ... FOR UPDATE;
добавить после запросаFOR UPDATE, Mysql добавит монопольную блокировку к каждой строке результата запроса. Если ни один другой поток не использует монопольную блокировку какой-либо строки в результирующем наборе запроса, он может успешно применить монопольную блокировку, иначе он будет заблокирован.
оптимистическая блокировка и пессимистическая блокировка
Введенная в механизм блокировки базы данных задача управления параллелизмом в системе управления базами данных (СУБД) заключается в том, чтобы гарантировать, что изоляция и единство транзакции и единство базы данных не нарушаются при одновременном доступе нескольких транзакций к одним и тем же данным. в базе данных.
Оптимистический контроль параллелизма (оптимистическая блокировка) и пессимистический контроль параллелизма (пессимистическая блокировка) являются основными техническими средствами, используемыми для контроля параллелизма.
Будь то пессимистическая блокировка или оптимистическая блокировка, это концепция, определяемая людьми, и ее можно рассматривать как разновидность мысли. На самом деле, это не только концепция оптимистической блокировки и пессимистической блокировки в системах реляционных баз данных, таких как memcache, hibernate, tair и т. д., все они имеют схожие концепции.
Для разных бизнес-сценариев следует выбирать разные методы управления параллелизмом. Поэтому не следует понимать оптимистичный контроль параллелизма и пессимистический контроль параллелизма как концепции СУБД, не говоря уже о том, чтобы путать их с механизмом блокировки (блокировка строки, блокировка таблицы, эксклюзивная блокировка, общая блокировка), предоставляемым в данных. Фактически в СУБД пессимистическая блокировка реализуется с использованием механизма блокировки, предоставляемого самой базой данных.
пессимистический замок
В системах управления реляционными базами данных пессимистический контроль параллелизма (также известный как «пессимистическая блокировка», пессимистический контроль параллелизма, сокращенно «PCC») является методом управления параллелизмом. Это предотвращает транзакцию от изменения данных таким образом, который влияет на других пользователей. Если операция, выполняемая транзакцией, применяет блокировку к строке данных, только когда транзакция снимает блокировку, другие транзакции могут выполнять операции, конфликтующие с блокировкой. Пессимистический контроль параллелизма в основном используется в средах с высокой конкуренцией данных и где стоимость использования блокировок для защиты данных в случае конфликта параллелизма меньше, чем стоимость отката транзакции.
Пессимистическая блокировка, как следует из ее названия, относится к консервативному отношению (пессимистичному) к тому, что данные изменяются внешним миром (в том числе другими текущими транзакциями в системе, а также транзакциями из внешних систем), поэтому в течение всего процесса обработки данных , Данные заблокированы. Реализация пессимистических блокировок часто зависит от механизма блокировки, предоставляемого базой данных (только механизм блокировки, предоставляемый уровнем базы данных, может действительно гарантировать исключительность доступа к данным. В противном случае, даже если механизм блокировки реализован в этой системе, он не может быть реализован). гарантируется, что внешняя система не будет изменена.данные)
Специфический процесс пессимистической блокировки
- Перед изменением какой-либо записи попробуйте добавить к записи монопольную блокировку;
- Если блокировка не удалась, указывая на то, что запись модифицируется, текущему запросу, возможно, придется ждать или выдать исключение. Конкретный способ реагирования определяется разработчиком в соответствии с реальными потребностями;
- Если блокировка прошла успешно, запись можно изменить, и транзакция будет разблокирована после ее завершения.
- В то же время, если есть другие операции, которые изменяют запись или добавляют эксклюзивную блокировку, она будет ждать, пока мы разблокируем ее, или вызовет исключение напрямую.
Преимущества и недостатки пессимистической блокировки
Пессимистическая блокировка фактически использует стратегию «сначала взять блокировку, а затем получить к ней доступ», что гарантирует безопасность обработки данных, но с точки зрения эффективности возникают дополнительные накладные расходы из-за дополнительного механизма блокировки, и увеличивается вероятность взаимоблокировки. . И это уменьшает параллелизм: когда у одной вещи есть строка данных, другие вещи должны ждать фиксации транзакции, прежде чем работать с этой строкой данных.
оптимистическая блокировка
В системах управления реляционными базами данных оптимистичный контроль параллелизма (также известный как «оптимистическая блокировка», Optimistic Concurrency Control, сокращенно «OCC») — это метод управления параллелизмом. Предполагается, что многопользовательские параллельные транзакции не будут влиять друг на друга во время обработки, и каждая транзакция может обрабатывать часть данных, затронутых друг другом, без генерации блокировок. Перед фиксацией обновления данных каждая транзакция проверяет, не изменили ли данные другие транзакции после того, как транзакция прочитала данные. Если у других транзакций есть обновления, фиксирующая транзакция будет отменена.
Оптимистическая блокировка По сравнению с пессимистической блокировкой оптимистическая блокировка предполагает, что данные в целом не будут вызывать конфликтов, поэтому при отправке и обновлении данных конфликт данных будет формально обнаружен.Если обнаружен конфликт, то пусть пользователь возвращает неверную информацию. , пусть пользователь решает, что делать.
По сравнению с пессимистической блокировкой оптимистическая блокировка не использует механизм блокировки, предоставляемый базой данных при обработке базы данных. Общий способ реализации оптимистической блокировки — запись версии данных.
Версия данных, идентификатор версии, добавленный к данным. При чтении данных значение идентификатора версии считывается вместе, и идентификатор версии обновляется каждый раз при обновлении данных. Когда мы отправляем обновление, сравниваем текущую информацию о версии соответствующей записи в таблице базы данных с идентификатором версии, извлеченным в первый раз.Если номер текущей версии таблицы базы данных равен значению идентификатора версии, извлеченному для в первый раз обновите его. , в противном случае он считается просроченным.
Преимущества и недостатки оптимистической блокировки
Оптимистичный контроль параллелизма считает, что вероятность гонки данных между транзакциями относительно мала, поэтому делайте это максимально напрямую и не блокируйтесь до момента отправки, так не будет блокировок и взаимоблокировок. Однако, если вы сделаете это напрямую, вы можете столкнуться с неожиданными результатами. Например, две транзакции читают строку в базе данных и выполняют обратную запись в базу данных после изменения. В этот раз вы столкнетесь с проблемой.
Намеренная совместная блокировка/намеренная эксклюзивная блокировка
Хотя блокировки таблиц и строк имеют разные диапазоны блокировки, они будут конфликтовать друг с другом. Поэтому, когда вы хотите добавить блокировку таблицы, вы должны сначала просмотреть все записи в таблице, чтобы определить, существует ли монопольная блокировка. Очевидно, что этот метод обходной проверки неэффективен, поскольку MySQL вводит блокировки по намерениям для обнаружения конфликтов между блокировками таблиц и строк.
Блокировки намерения также являются блокировками уровня таблицы и также могут быть разделены на блокировки намерения чтения (блокировки IS) и блокировки намерения записи (блокировки IX). Когда транзакции необходимо добавить блокировку чтения или записи к записи, она должна сначала добавить блокировку намерения к таблице. Таким образом, очень просто определить, есть ли блокировка записи в таблице, просто проверьте, есть ли преднамеренная блокировка в таблице ниже.
Нет конфликта между блокировками по намерению и блокировками таблиц AUTO_INC. Он будет блокировать только блокировки чтения или записи на уровне таблицы. Кроме того, блокировки по намерениям не будут конфликтовать с блокировками строк. Конфликт блокировок строк.
Блокировки намерений автоматически добавляются InnoDB и не требуют вмешательства пользователя.
Для вставки, обновления, удаления InnoDB автоматически добавит эксклюзивную блокировку (X) к задействованным данным;
Для общих операторов Select InnoDB не будет добавлять никаких блокировок, и транзакции могут отображаться с общими блокировками или эксклюзивными блокировками с помощью следующих операторов.
Общая блокировка намерения
Преднамеренная общая блокировка (IS): указывает, что транзакция готова добавить общую блокировку к строке данных, то есть строка данных должна сначала получить блокировку IS таблицы, прежде чем добавлять общую блокировку.
Преднамеренная эксклюзивная блокировка (Exclusive Lock)
Преднамеренная монопольная блокировка (IX): аналогично предыдущему, это означает, что транзакция собирается добавить монопольную блокировку к строке данных, указывая, что транзакция должна получить блокировку IX таблицы перед добавлением монопольной блокировки к строке данных. .
Блокировка записи
Блокировки записей — это самые простые блокировки строк, и о них особо нечего сказать. Блокировка в описанном выше принципе блокировки InnoDB — это блокировка записи, которая блокирует только запись с id = 49 или name = 'Tom'.
Когда оператор SQL не может использовать индекс, будет выполнено полное сканирование таблицы.В это время MySQL добавит блокировки записей ко всем строкам данных во всей таблице, а затем отфильтрует по уровню сервера MySQL. Однако при фильтрации на уровне сервера MySQL, если будет обнаружено, что условие WHERE не выполняется, блокировка соответствующей записи будет снята. Это гарантирует, что в конце будет удержана только блокировка записи, удовлетворяющей условиям, но операция блокировки каждой записи не может быть пропущена.
Поэтому операцию обновления необходимо выполнять по индексу, если индекса нет, то это не только будет потреблять много ресурсов блокировок, увеличивать накладные расходы базы данных, но и сильно снижать параллельную производительность базы данных.
Гэп-лок
Когда мы используем условия диапазона вместо условий равенства для извлечения данных и запроса общих или эксклюзивных блокировок, InnoDB заблокирует записи индекса существующих записей данных, которые соответствуют условиям; для записей, значения ключей которых находятся в диапазоне условий, но не существуют , InnoDB также заблокирует этот «пробел», и этот механизм блокировки является так называемой блокировкой промежутка.
Гэп-блокировки — это промежутки в записях индекса блокировки, или диапазон перед первой записью индекса, или диапазон после последней записи индекса.
Единственная функция блокировки промежутка в InnoDB состоит в том, чтобы предотвратить вставку других транзакций, чтобы предотвратить возникновение фантомных чтений, поэтому блокировка промежутка не различает разделяемые блокировки и монопольные блокировки.
Чтобы отключить гэп-блокировки, уровень изоляции можно понизить дочтение зафиксировано, или открыть параметрinnodb_locks_unsafe_for_binlog
show variables like 'innodb_locks_unsafe_for_binlog';
innodb_locks_unsafe_for_binlog: значение по умолчанию — OFF, то есть блокировка зазора включена. Поскольку этот параметр доступен только для чтения, если вы хотите отключить блокировку промежутка времени, вам необходимо изменить файл my.cnf (Windows — это my.ini) и перезапустить.
# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1
Случай 1: блокировка пробела для уникального индекса
тестовая среда:
MySQL5.7, InnoDB, уровень изоляции по умолчанию (RR)
Пример таблицы:
CREATE TABLE `my_gap` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`name` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `my_gap` VALUES ('1', '张三');
INSERT INTO `my_gap` VALUES ('5', '李四');
INSERT INTO `my_gap` VALUES ('7', '王五');
INSERT INTO `my_gap` VALUES ('11', '赵六');
Перед тестированием давайте посмотрим на скрытые гэпы, существующие в таблице my_gap:
- (-infinity, 1]
- (1, 5]
- (5, 7]
- (7, 11]
- (11, +infinity]
Используйте только блокировки записей (блокировки строк), блокировки пробелов создаваться не будут.
/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 name = '杰伦' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行
/* 事务3插入一条 name = '学友' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
В приведенном выше случае, поскольку первичный ключ является уникальным индексом, и для запроса используется только один индекс, и только одна запись заблокирована, только однаid = 5блокировка данных плюс блокировка записи (блокировка строки) без блокировки промежутка.
сгенерировать блокировку гэпа
Чтобы восстановить инициализированные 4 записи, продолжайте выполнять следующие тесты для столбца уникального индекса id:
/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行
/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行
/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞
/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞
/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞
/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞
/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
Как видно из вышеизложенного, два интервала (5,7] и (7,11] не могут вставлять данные, а другие интервалы могут нормально вставлять данные. Следовательно, можно сделать вывод, что:Когда мы заблокируем интервал (5,7), два интервала (5,7] и (7,11] будут заблокированы..
Восстановите инициализированные 4 записи, давайте проверим, что произойдет, если заблокировать несуществующие данные?
/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 id = 3，name = '小张' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞
/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞
/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行
/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
Как видно из вышеприведенного, когда определенная запись указана для запроса, если запись не существует, будет сгенерирована блокировка пробела.
В заключение
- Для оператора блокировки, который указывает запрос для определенной записи, если запись не существует, будут сгенерированы блокировка записи (блокировка строки) и блокировка пробела; если запись существует, будет только блокировка записи (блокировка строки). генерируется;
- Гэп-блокировки генерируются для операторов запроса, которые находят диапазон.
Случай 2: Gap Locks для нормальных индексов
Пример таблицы:** id является первичным ключом, а по номеру устанавливается общий индекс. **
# 注意：number 不是唯一值
CREATE TABLE `my_gap1` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`number` int(1) NOT NULL COMMENT '数字',
PRIMARY KEY (`id`),
KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `my_gap1` VALUES (1, 1);
INSERT INTO `my_gap1` VALUES (5, 3);
INSERT INTO `my_gap1` VALUES (7, 8);
INSERT INTO `my_gap1` VALUES (11, 12);
Перед тестированием взглянем на скрытый пробел в числовом индексе таблицы my_gap1:
- (-infinity, 1]
- (1, 3]
- (3, 8]
- (8, 12]
- (12, +infinity]
Тест 1
Мы выполняем следующие транзакции (транзакция 1 фиксируется последней), выполняя следующие операторы соответственно:
/* 开启事务1 */
BEGIN;
/* 查询 number = 3 的数据并加记录锁 */
SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行
/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞
/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞
/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞
/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行
/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行
/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行
/* 提交事务1 */
COMMIT;
Мы обнаружим, что некоторые операторы могут выполняться нормально, а некоторые блокируются. Просмотрите данные в таблице:Здесь видно, что в промежутке числа (1,8) оператор вставки заблокирован, а оператор, не входящий в этот диапазон, выполняется нормально, что связано с блокировкой промежутка.
Тест 2
Выполним следующие тесты, где данные восстанавливаются до инициализации
/* 开启事务1 */
BEGIN;
/* 查询 number = 3 的数据并加记录锁 */
SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
/* 事务1插入一条 id = 2， number = 1 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞
/* 事务2插入一条 id = 3， number = 2 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞
/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞
/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行
/* 事务5插入一条 id = 9， number = 9 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行
/* 事务6插入一条 id = 10， number = 12 的数据 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行
/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞
/* 提交事务1 */
COMMIT;
просмотреть данные в таблице;Вот странное явление:
- Транзакция 3 добавляет данные с id=6, number=8, заблокирована;
- Транзакция 4 добавляет данные с id=8, number=8 и выполняется нормально;
- Транзакция 7 изменяет данные id = 11, number = 12 на операцию id = 11, number = 5, которая заблокирована.
почему это? Давайте посмотрим на картинку ниже:Как видно из библиотеки на рисунке, когда число одинаковое, оно будет отсортировано по id первичного ключа
- id = 6, число = 8, добавленное транзакцией 3, эти данные находятся в интервале (3,8), поэтому он будет заблокирован;
- id = 8, число = 8, добавленное транзакцией 4, эти данные находятся в интервале (8,12), поэтому блокироваться не будет;
- Оператор модификации транзакции 7 эквивалентен вставке фрагмента данных в интервал (3,8), поэтому он также блокируется.
В заключение
- В обычных индексированных столбцахНезависимо от типа запроса, пока блокировка добавлена, будет сгенерирована блокировка пробела, которая отличается от уникального индекса.;
- В общем индексе и уникальном индексе при анализе пробелов в данных строки данных сначала сортируются по общему общему индексу, а затем сортируются по уникальному индексу.
Блокировки следующего ключа
Блокировка рядом с ключом представляет собой комбинацию блокировки записи (блокировки строки) и блокировки пробела. Диапазон блокировки включает как записи индекса, так и диапазоны индексов. Это относится к блокировке, примененной к записи, и пробелу перед этой записью. Предполагая, что индекс содержит значения 15, 18, 20, 30, 49, 50, возможные блокировки Next-key следующие:
(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)
Обычно мы используем этот интервал «лево-открыто-право-закрыто» для представления блокировки «Следующий ключ», где круглые скобки указывают, что запись не включена, а квадратные скобки указывают, что запись включена. Первые четыре являются блокировками Next-key, а последняя — блокировкой пробела. Подобно блокировкам промежутка, на уровне изоляции RC нет блокировок следующего ключа, а есть только уровень изоляции RR. В предыдущем примере, если id является не первичным ключом, а вторичным индексом и не является уникальным индексом, то этот SQL добавит следующую блокировку следующего ключа на уровне изоляции RR (30, 49](49, 50). )
В этот момент, если вы вставите запись с id = 31, она будет заблокирована. Причина, по которой пробел до и после id = 49 заблокирован, по-прежнему заключается в решении проблемы фантомного чтения.Поскольку id не является уникальным индексом, может быть несколько записей с id = 49, чтобы предотвратить вставку другой записи с id = 49.
Уведомление: Основная цель блокировки клавиш — избежать фантомного чтения. Если уровень изоляции транзакций понижен до RC, блокировка ключа также не удастся.
Вставьте замки намерения
Блокировка намерения вставки — это особый вид блокировки промежутка (сокращение отII GAP) указывает на намерение вставить, и эта блокировка доступна только во время INSERT. Обратите внимание, что хотя эта блокировка также называется блокировкой по намерению, это два совершенно разных понятия по сравнению с блокировкой по намерению на уровне таблицы, представленной выше, поэтому не путайте ее.
Блокировки с целью вставки и блокировки с целью вставки не конфликтуют друг с другом, поэтому несколько транзакций могут одновременно вставлять записи разных индексов в один и тот же промежуток. Например, в примере, если есть две транзакции между id = 30 и id = 49, не проблема вставить id = 32 и id = 33 одновременно, хотя обе транзакции будут между id = 30 и id = 50 Плюс вставить блокировку намерения, но без конфликта.
Вставка блокировок намерения будет конфликтовать только с блокировками промежутка или блокировками следующего ключа.Как упоминалось выше, единственная функция блокировок промежутка состоит в том, чтобы предотвратить фантомное чтение от вставки записей другими транзакциями.Именно потому, что блокировки вставки намерения необходимо добавлять при выполнении INSERT, в то время как блокировка намерения вставки конфликтует с блокировкой промежутка, предотвращая выполнение операции вставки.
Роль блокировки намерения вставки:
- Чтобы вызвать ожидание. Поскольку зазор уже имеет замок, он должен быть заблокирован при вставке, а функция блокировки намерения вставки имеет функцию блокировки;
- Блокировка намерения вставки — это особый тип блокировки пробела.Поскольку это своего рода блокировка пробела, почему бы не использовать блокировку пробела напрямую? Gap-блокировки не являются взаимоисключающими напрямую. Не может блокировать или вызывать ожидание, что приведет к фантомному чтению.
- Почему бы не использовать блокировки записей (блокировки строк) или блокировки ключей? Если применяется блокировка записи или блокировка клавиш, блокировки клавиш могут быть взаимоисключающими, что влияет на параллелизм вставки.
Автоматические замки
Блокировка AUTO_INC, также известная как блокировка с самоинкрементом (обычно сокращенно блокировка AI), представляет собой блокировку таблицы, которая возникает, когда в таблице есть столбец с автоинкрементом (AUTO_INCREMENT). Когда во вставленной таблице есть столбец с автоинкрементом, база данных должна автоматически генерировать автоинкремент.Сначала она добавит блокировку таблицы AUTO_INC к таблице, чтобы заблокировать операции вставки других транзакций, чтобы гарантировать, что сгенерированный автоинкремент должен быть уникальным. Блокировки AUTO_INC имеют следующие характеристики:
- Блокировки AUTO_INC несовместимы друг с другом, то есть для одной и той же таблицы одновременно допускается только одна автоинкрементная блокировка;
- Как только самоувеличение будет выделено, оно будет + 1. Если транзакция откатится, самоувеличение не будет уменьшено, поэтому самоувеличение может быть прервано.
операция автоматического увеличения
использоватьAUTO_INCREMENTФункция реализует операцию самоинкремента, и степень самоинкремента передается черезauto_increment_offsetа такжеauto_increment_incrementЭти 2 параметра контролируются:
- auto_increment_offset указывает начальный номер
- auto_increment_increment указывает диапазон регулировки (то есть каждый раз добавляется n чисел, 2 означает +2 каждый раз)
Последнее вставленное число можно получить с помощью функции 314219
select 314219;
Автоматическая блокировка
Прежде всего, вставки можно условно разделить на три категории:
- простая вставка, такая как вставка в значения t (имя) («тест»)
- массовая вставка, например, загрузить данные | вставить в... выбрать.... из....
- смешанная вставка, такая как вставка в значения t(id,name)(1,'a'),(null,'b'),(5,'c');
Если есть поле автоинкремента, MySQL будет поддерживать блокировку автоинкремента, а параметр, связанный с блокировкой автоинкремента (добавлен после версии 5.1.22)innodb_autoinc_lock_mode, можно установить 3 значения:
- 0 : традиционный (блокировки таблицы генерируются каждый раз)
- 1: Последовательный (будет создана облегченная блокировка, простая вставка получит пакетную блокировку, чтобы обеспечить непрерывную вставку)
- 2: с чередованием (таблица не блокируется, обрабатывается по одной, с максимальной степенью параллелизма)
Все механизмы MyISam являются традиционными, и блокировки таблиц выполняются каждый раз. Однако механизм InnoDB будет генерировать разные блокировки в зависимости от параметров, значение по умолчанию — 1: Последовательно.
show variables like 'innodb_autoinc_lock_mode';
traditonal
innodb_autoinc_lock_modeКогда он равен 0, это традиционный уровень. Самоувеличивающаяся блокировка находится на уровне блокировки таблицы и должна быть снята после выполнения или отката текущего SQL.В случае высокого уровня параллелизма можно предположить, что конкуренция за самовозрастающие блокировки будет относительно большой.
- Он обеспечивает возможность обратной совместимости
- В этом режиме все операторы вставки ("insert like") получают блокировку auto_inc на уровне таблицы в начале оператора и снимают блокировку в конце оператора. Обратите внимание, что мы говорим об уровне операторов, а не уровне транзакций, транзакция может содержать один или несколько операторов;
- Он может обеспечить предсказуемость, непрерывность и повторяемость присваивания значений, что гарантирует, что оператор вставки может генерировать то же значение, что и мастер, когда он реплицируется на ведомый (это обеспечивает безопасность репликации на основе операторов);
- Поскольку в этом режиме блокировка auto_inc удерживается до конца оператора, это влияет на одновременные вставки.
consecutive
Когда innodb_autoinc_lock_mode равен 1, это последовательный уровень. Это означает, что если это один SQL вставки, блокировка может быть получена немедленно и снята немедленно, не дожидаясь завершения выполнения текущего SQL (если только не существует сеанс, который уже получил самоинкрементную блокировку в других транзакциях). . Также, когда SQL является пакетной вставкой SQL, напримерinsert into ... select ...,load data , replace ... select ...В настоящее время это все еще блокировка на уровне таблицы, которую можно понимать как вырождающуюся к тому факту, что она должна дождаться выполнения текущего SQL, прежде чем освободить его. Можно считать, что при значении 1 относительно легкая блокировка не повлияет на репликацию.Единственным недостатком является то, что самонаращивание не обязательно является полностью непрерывным..
- В этом режиме оптимизирована простая вставка.Поскольку количество значений, вставленных простой вставкой за один раз, можно определить сразу, MySQL может генерировать несколько последовательных значений за раз для этого оператора вставки. В целом это также безопасно для репликации (гарантирует безопасность репликации на основе операторов);
- Этот режим также является режимом MySQL по умолчанию.Преимущество этого режима заключается в том, что блокировка auto_inc не сохраняется до конца оператора, и блокировку можно снять заранее, пока оператор получает соответствующее значение.
interleaved
Когда innodb_autoinc_lock_mode равен 2, это уровень чередования. Все типы вставки SQL могут получать блокировки и немедленно освобождать их, что является наиболее эффективным. Но это создаст новую проблему: когда binlog_format является оператором, репликация не может быть гарантирована безопасной, потому что пакетные вставки, такие какinsert ... select ...В этом случае оператор также может сразу получить большое количество самоувеличивающихся значений id без блокировки всей таблицы, и подчиненный неизбежно вызовет путаницу при воспроизведении этого SQL.
- Поскольку в этом режиме нет блокировки auto_inc, производительность в этом режиме наилучшая, но также есть проблема, что значения auto_incremant, которые он получает для одного и того же оператора, могут не быть последовательными.
Если ваш двоичный формат файла смешанный|строка, то любое из этих трех значений для вас безопасно для копирования.
Поскольку mysql рекомендует установить двоичный формат для строки, лучше установить innodb_autoinc_lock_mode=2, когда binlog_format не является оператором, что может обеспечить лучшую производительность.
Суммировать
Особенности замков InnoDB
- При запросе без условий индекса InnoDB использует блокировки таблиц!
- Поскольку блокировка строк в MySQL блокирует индексы, а не записи, поэтому, несмотря на доступ к записям разных строк, если используется один и тот же ключ индекса, возникнут конфликты блокировок.
- Когда таблица имеет несколько индексов, разные транзакции могут использовать разные индексы для блокировки разных строк.Кроме того, независимо от того, используете ли вы индекс первичного ключа, уникальный индекс или общий индекс, InnoDB будет использовать блокировки строк для блокировки данных.
- Даже если в условии используется поле индекса, MySQL определяет, следует ли использовать индекс для извлечения данных, оценивая стоимость различных планов выполнения.Если MySQL считает, что полное сканирование таблицы более эффективно, например, для некоторых небольших таблиц , индексы не будут использоваться, и в этом случае InnoDB будет использовать блокировки таблиц вместо блокировок строк. Поэтому при анализе конфликтов блокировок не забудьте проверить план выполнения SQL (представление объяснения), чтобы убедиться, что индекс действительно используется.
режим блокировки
Режимы блокировки: блокировка намерения чтения, блокировка намерения записи, блокировка чтения, блокировка записи и блокировка автоинкремента (auto_inc).
Матрица совместимости для разных блокировок режима
| IS | IX | S | X | AI | |
|---|---|---|---|---|---|
| IS | совместимый | совместимый | совместимый | совместимый | |
| IX | совместимый | совместимый | совместимый | ||
| S | совместимый | совместимый | |||
| X | |||||
| AI | совместимый | совместимый |
Подводя итог следующим пунктам:
- Блокировки намерения не конфликтуют друг с другом;
- Блокировки S совместимы только с блокировками S/IS и конфликтуют с другими блокировками;
- Блокировка X конфликтует со всеми другими блокировками;
- Блокировки AI совместимы только с блокировками намерения;
тип замка
По степени детализации замок можно разделить наблокировка столаа такжеблокировка строки, блокировка строки может быть дополнительно подразделена в соответствии с различными сценариями, чтобыNext-Key Lock,Гэп-лок,Блокировка записиа такжеВставить блокировку намерения GAP.
Различные блокировки блокируются в разных положениях.Например, блокировки записи блокируют только соответствующие записи, в то время как блокировки промежутка блокируют промежуток между записями и записями, а блокировка следующего ключа блокирует запись и промежуток перед записью. Диапазон запирания различных типов замков примерно такой, как показано на рисунке ниже.
Матрица совместимости для разных типов замков
| RECORD | GAP | NEXT-KEY | II GAP | |
|---|---|---|---|---|
| RECORD | совместимый | совместимый | ||
| GAP | совместимый | совместимый | совместимый | совместимый |
| NEXT-KEY | совместимый | совместимый | ||
| II GAP | совместимый | совместимый |
Среди них первая строка представляет существующую блокировку, а первый столбец представляет блокировку, которую нужно добавить. Блокировки намерения вставки являются особыми, поэтому сначала мы суммируем блокировки намерения вставки следующим образом:
- Вставка блокировок намерений не влияет на другие транзакции и любые другие блокировки. Другими словами, транзакция получила блокировку намерения вставки, которая не влияет на другие транзакции;
- Блокировки намерения вставки конфликтуют с блокировками промежутка и блокировками следующего ключа. То есть транзакция, которая хочет получить блокировку намерения вставки, будет заблокирована, если другая транзакция уже добавила блокировку промежутка или блокировку следующего ключа.
Правила для других типов замков проще:
- Гэп-блокировки не конфликтуют с другими блокировками (за исключением блокировок намерения вставки);
- Блокировка записи и конфликт блокировки записи, блокировка следующей клавиши и конфликт блокировки следующей клавиши, блокировка записи и конфликт блокировки следующей клавиши;
Статья постоянно обновляется, можно поискать на официальном аккаунте »дайм тек"Прочитайте впервые эту статью GitHuborg_hejianhui/JavaStudyОн был записан, добро пожаловать в Star.