Расположение точек знаний блокировки MySql, вызванное тупиком одновременной вставки | Бычье новогоднее эссе

Точки знаний о блокировке MySql, вызванные тупиковой блокировкой одновременной вставки

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

I. Общая блокировка MySql

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

0. Классификация блокировки

Наиболее распространенные блокировки, о которых мы говорим, можно разделить на разделяемые блокировки (S) и эксклюзивные блокировки (X).В движке MySQL innodb для решения проблемы фантомного чтения вводятся блокировки пробелов и блокировки следующего ключа; также является блокировкой намерения, такой как блокировка намерения вставки

В этой статье в основном будут представлены следующие типы замков

Блокировка строки (блокировка записи): обратите внимание, что это блокировка индекса (поэтому, если индекса нет, последняя блокировка строки приведет к блокировке всей таблицы)
Общая блокировка (S Lock): также называемая блокировкой чтения, общие блокировки не будут блокировать друг друга (как следует из названия).
Эксклюзивная блокировка (X Lock): также называемая блокировкой записи, монопольная блокировка может удерживаться только одним сеансом (или транзакцией?) за раз.
Gap lock: для промежутков между индексами
Блокировка следующего ключа (блокировка следующего ключа): может быть просто понята как блокировка строки + блокировка пробела

Хотя основные определения нескольких видов блокировок были представлены выше, когда возникает блокировка строки, как получить общую блокировку и в каких сценариях возникает эксклюзивная блокировка?gap lock/next key lockКак решить фантомное чтение?

Все нижеследующее основано на движке innodb mysql5.7.22, поясняется уровень изоляции rr

1 Общие блокировки и эксклюзивные блокировки

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

Различие между общими блокировками и эксклюзивными блокировками

sql	Пример	иллюстрировать
`select ... where`	`select * from table limit 1`	На основе MVCC, чтение снимков, без блокировки
`select ... for update`	`select * from table where id=1 for update`	эксклюзивный замок
`select ... lock in share mode`	`select * from table where id=1 lock in share mode`	общий замок
`update ... where`	`update table set xx=xx where id=1`	эксклюзивный замок
`delete ... where`	`delete table where id=1`	эксклюзивный замок

2. Различайте блокировки строк, блокировки таблицы, блокировки промежутка и блокировки следующего ключа.

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

Без индекса добавление блокировки S/X в конечном итоге заблокирует всю таблицу (почему? Потому что блокировка предназначена для индекса)
Блокировка идентифицированных записей на основе первичного ключа/уникального ключа: блокировка строки
Обычный запрос индекса или диапазона: блокировка промежутка / блокировка следующей клавиши

Самая большая разница между блокировками строк и блокировками пробелов заключается в следующем:

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

3. Пример демонстрации

Глядя на два приведенных выше описания, я, естественно, хочу проанализировать в реальном случае, какой эффект блокировки будет иметь другой SQL?

Добавить блокировку X к определенной записи в таблице, это только блокировка строки?
Что делать, если X-блокировка добавлена к нескольким подтвержденным записям в таблице?
Добавить блокировку X к несуществующей записи в таблице, будет ли блокировка? Если это гэп-лок, то как определяется интервал?
Как определить диапазон гэп-блокировки, сгенерированный путем добавления X-блокировки к диапазону?

Прежде чем анализировать приведенные выше случаи, мы должны сначала иметь представление о том, что блокировки предназначены для индексов, что очень и очень важно.

Во-вторых, для разных индексов нам нужно тестировать их отдельно (по сути, уникальные индексы и обычные индексы)

3.1 Подготовка стола

Затем мы разрабатываем наши тестовые примеры для четырех описанных выше сценариев.Во-первых, мы готовим три таблицы.

Нет индексной таблицы TN
Таблица уникальных индексов TU
Таблица общего индекса TI

Соответствующая структура таблицы и данные инициализации следующие:

CREATE TABLE `tn` (
  `id` int(11) unsigned NOT NULL,
  `uid` int(11) unsigned NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `tu` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_uid` (`uid`)
) ENGINE=InnoDB;

CREATE TABLE `ti` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `u_uid` (`uid`)
) ENGINE=InnoDB;


INSERT INTO `tn` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `tu` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `ti` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);

3.2 Точное совпадение

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

Пример	TN	TU	TI
`select * from tx where uid=20 for update`	заблокировать полную таблицу	uid блокировки строки = 20	uid блокировки строки = 20, uid блокировки пробела = [10, 30)

Обратите внимание на приведенный выше вывод, легко понять блокировку всей таблицы, когда нет индекса, а обычный индексTIТот факт, что по-прежнему существует блокировка пробела [10, 30), немного выходит за рамки нашего воображения;

Далее мы проверяем

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

精确命中

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

подобноuid = 20, две записи до и после(1, 10), (10, 30)

блокировка промежутка: диапазон [10, 30)
поэтому не могу вставить uid=[10,30)
Обратите внимание, что на uid=10 есть гэп-лок, но записи не вставляются, а вот с добавлением X-лока проблем нет (если интересно, можете протестировать)

3.3 Точный запрос не совпадает

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

Пример	TN	TU	TI
`select * from tx where uid=25 for update`	заблокировать полную таблицу	`gap lock uid=(20,30)`	`gap lock uid=(20, 30)`

Измеренный случай выглядит следующим образом (TN опущен, нет необходимости тестирования для блокировки всей таблицы)

Основной процесс нарисован не будет, приведенный выше рисунок описан в тексте.

精确命中

Из приведенного выше теста также видно, чтоuid=30Не блокируется, здесь только добавляется блокировка пробела в интервале uid=(20, 30)

Уникальный индекс имеет ту же производительность, что и обычный индекс, и блокирует блокировку намерения вставки (это будет обсуждаться позже).

3.4 Запрос диапазона

Когда мы блокируем интервал, ситуация блокировки выглядит следующим образом

Пример	TN	TU	TI
`select * from tx where uid>15 and uid<25 for update`	заблокировать полную таблицу	`next key lock uid=(10,30]`	`next key lock uid=(10, 30]`

Проще говоря, когда делается запрос диапазона, добавьтеnext key lock, по условиям нашего запроса находим крайний левый и крайний правый интервалы записи

подобноuid > 15 and uid < 25, найденные записи(1, 10), (10, 30)

гэп замок есть(10, 30)
next key lockБлокировка строки будет добавлена справа, т.е.uid=30Добавить X-замок
Поэтому дляuid=30Запись блокировки будет заблокирована (но для блокировки UID = 28, 29 плюс X не заблокированы, вставлены, заинтересованные партнеры могут измерять его)

Примечание: При добавлении блокировки x к диапазону записи, которых больше нет в этом диапазоне, могут быть заблокированы. Если вы не будете осторожны, это может привести к взаимоблокировке.

3.5 Резюме

На уровне изоляции RR мы обычно считаем, что операторы, которые могут генерировать блокировки:

SELECT ... FOR UPDATE: Х замок
SELECT ... LOCK IN SHARE MODE: S-замок
update/delete: Х замок

показатель	Сцены	блокировка области
нет индекса	Блокировка S / X	заблокировать полную таблицу
уникальный индекс	точное совпадение и нажмите	блокировка строки
уникальный индекс	точное совпадение, мисс	gap lock
уникальный индекс	запрос диапазона	Блокировка следующей клавиши (интервал для предыдущей записи следующей записи, левая закрыта и правая открыта) Блокировка правой строки записи

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

Выше описано, какие блокировки будут генерироваться в разных сценариях, но после прочтения возникнет вопрос: когда другие сессии конкурируют за блокировки строк, можно следовать правилам блокировок X/S, но этот GAP LOCK кажется быть действительным только для вставок. Есть ли какая-либо другая специальная логика для вставки, кроме добавления блокировки X?

4.1 Вставить блокировку намерения

Блокировка намерения вставки на самом деле является специальной блокировкой промежутка, но она не блокирует другие блокировки. Предполагая, что есть индексные записи со значениями 4 и 7, две транзакции, пытающиеся вставить значения 5 и 6, блокируют разрыв блокировкой намерения вставки до получения эксклюзивной блокировки на вставленную строку, то есть добавляют пробел блокировка на (4, 7), Однако эти две транзакции не будут конфликтовать друг с другом и ждать; но если в этом интервале есть блокировка с промежутком, она будет заблокирована; если несколько транзакций вставят одни и те же данные и вызовут уникальный конфликт , к повторяющейся записи индекса будет добавлена блокировка чтения.

Проще говоря, его свойства:

не блокирует никакие другие замки;
Сам он блокируется только гэп-локом

Еще один важный момент знаний:

Обычно оператор вставки, добавление блокировки строки, эксклюзивная блокировка
Перед вставкой сначала вставьте блокировку намерения, чтобы определить, можно ли ее вставить (заблокировано только блокировкой пробела)
Добавить блокировку чтения для повторяющегося индекса при вставке уникального конфликта
- Причины следующие:
- Транзакция 1 успешно вставлена, но не зафиксирована, и эксклюзивная блокировка получена, но транзакция 1 может в конечном итоге быть отменена, поэтому другие повторяющиеся транзакции вставки не должны терпеть неудачу напрямую.В это время вместо этого они применяют блокировку чтения (вопрос: зачем менять на прочитанный замок Шерстяное полотно?)

4.2 Матрица конфликтов блокировки

Простая матрица

	Общий замок(и)	Эксклюзивный замок (X)
Общий замок(и)	совместимый	конфликт
Эксклюзивный замок (X)	конфликт	конфликт

Когда мы добавим в матрицу гэп-лок (gap lock), следующий ключевой замок (next-key lock), вставить блокировку Intention (вставить блокировку намерения), это будет намного сложнее.

Строка: Заблокировать Столбец: Заблокировано	S(not gap)	S(gap)	S(next key)	X(not gap)	X(gap)	X(next key)	Insert Intention
S(not gap)	-	-	-	конфликт	-	конфликт	-
S(gap)	-	-	-	-	-	-	конфликт
S(next-key)	-	-	-	конфликт	-	конфликт	конфликт
X(not gap)	конфликт	-	конфликт	конфликт	-	конфликт	-
X(gap)	-	-	-	-	-	-	конфликт
X(next-key)	конфликт	-	конфликт	конфликт	-	конфликт	конфликт
Insert Intention	-	конфликт	конфликт	-	конфликт	конфликт	-

иллюстрировать

не пробел: блокировка строки
gap: gap lock
следующая клавиша: пробел + блокировка строки

резюме

Для приведенной выше матрицы уясните следующие принципы, чтобы вывести приведенную выше матрицу.

gap lockБудет конфликтовать только с блокировками намерения вставки
Блокировка строки X будет конфликтовать с блокировкой строки
next key lock: блокировка строки + блокировка пробела
- В интервале блокировки вставьте конфликт;
- Блокировка строки X конфликт блокировки

II. Анализ взаимоблокировки одновременной вставки

Вышеизложенное является базовыми точками знаний.Далее, давайте рассмотрим случай, который фактически вызывает взаимоблокировку.

Одновременная вставка одной и той же записи приводит к взаимоблокировке

0. Подготовка стола

Создайте самую простую и основную таблицу для демонстрации

CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `t` (`id`) VALUES (1);

1. Проблема взаимоблокировки отката транзакции

Воспроизведение сценария:

step1:

-- session1: 
begin; insert into t values (2);

-- session2:
begin; insert into t values (2);
-- 阻塞

-- session3:
begin; insert into t values (2);
-- 阻塞

step2:

-- session1:
rollback;

Анализ причин:

Просмотр журнала взаимоблокировок

SHOW ENGINE INNODB STATUS;

step1:
- session1: Insert (id=2), добавитX + Next LockЗамок
- session2/3: вставка (id = 2), блокировка намерения вставки заблокирована и удерживается вместо этогоS + Next LockЗамок
step2:
- session1: откат, снятие блокировки X
- Сессия 2/3: Конкуренция за X-блокировки, соревнование может быть успешным, только если другая сторона освобождает S-блокировки; ожидание друг друга приводит к взаимоблокировке

2. удаление вызывает проблему взаимоблокировки

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

step1:

-- session1: 
begin; delete from t where id=1;

-- session2:
begin; insert into t values (1);
-- 阻塞

-- session3:
begin; insert into t values (1);
-- 阻塞

step2:

-- session1:
commit;

Анализ причин в основном такой же, как и предыдущий.

3. Вставьте логику блокировки

Логика блокировки для уникального индекса во вставке

Сначала выполните обнаружение конфликтов в Великобритании, если есть целевая строка, сначала добавьте S Next Key Lock в целевую строку (запись удаляется другими транзакциями в течение периода ожидания, и блокировка удаляется одновременно)
Если 1 успешно, добавьте X + вставить блокировку намерения в соответствующую строку
Если 2 успешно, вставьте запись и добавьте X + блокировку строки к записи (возможно, неявную блокировку)

По вышеизложенной логике тогда будет интересный тупиковый сценарий

step1:

-- session1
begin; delete from t where id = 1;


-- session2
begin; delete from t where id = 1;

step2:

-- session1
insert into t values(1)

Соответствующий журнал взаимоблокировок

Дополнительные сведения об этом сценарии см.Запишите процесс устранения неполадок взаимоблокировки Mysql.

4. Как избежать взаимоблокировки?

Разбивайте большие дела на маленькие
Добавьте разумный индекс и используйте индекс, чтобы избежать блокировки каждой строки и уменьшить вероятность взаимоблокировки.
Избегайте ожидания бизнес-цикла (например, добавление распределенных блокировок)
Уменьшить уровень изоляции транзакций (типа RR -> RC, конечно, это не рекомендуется)
Использование замены/дубликата при одновременной вставке также может избежать взаимоблокировки.

II. Другое

1. Серый блог:liuyueyi.github.io/hexblog

Серый личный блог, записывающий все посты блога по учебе и работе, приглашаю всех в гости

Связанные сообщения в блоге

Mysql:innodb-next-key-locks
[MySQL][Gap Lock][Next-Key Lock] Анализ
Блокировка записи MySQL (блокировка записи), блокировка пробела (блокировка пробела), проверка блокировки следующего ключа (блокировка следующего ключа)
Запишите процесс устранения неполадок взаимоблокировки Mysql.
Анализ отказов | Анализ блокировки вставки MySQL и анализа взаимоблокировок
Просмотр и анализ журнала взаимоблокировок MySQL

2. Заявление

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

QQ: серо-серый / 3302797840
Публичный аккаунт WeChat:серый блог