предисловие
Возникает взаимоблокировка, как ее устранить и устранить? В этой статье мы обсудим этот вопрос с вами
- Подготовьте среду данных
- Моделирование тупиковой ситуации
- Анализ журналов взаимоблокировок
- Анализ результатов взаимоблокировки
Подготовка окружающей среды
Уровень изоляции базы данных:
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
Автокоммит отключен:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
Структура таблицы:
//id是自增主键，name是非唯一索引，balance普通字段
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Данные в таблице:
Имитация параллелизма
Откройте два терминала для имитации параллелизма транзакций.Последовательность выполнения и экспериментальные явления следующие:
1) Транзакция A выполняет операцию обновления, и обновление прошло успешно.
mysql> update account set balance =1000 where name ='Wei';
Query OK, 1 row affected (0.01 sec)
2) Транзакция B выполняет операцию обновления, и обновление прошло успешно.
mysql> update account set balance =1000 where name ='Eason';
Query OK, 1 row affected (0.01 sec)
3) Транзакция A выполняет операцию вставки и блокируется~
mysql> insert into account values(null,'Jay',100);
select * from information_schema.innodb_locks;Проверьте состояние блокировки:
4) Транзакция B выполняет операцию вставки, и вставка прошла успешно, в то время как вставка транзакции A меняется с блокировки на ошибку взаимоблокировки.
mysql> insert into account values(null,'Yan',100);
Query OK, 1 row affected (0.01 sec)
введение замка
Прежде чем анализировать журнал взаимоблокировок, давайте представим блокировку, ха-ха~
Общие блокировки и эксклюзивные блокировки
InnoDB реализует стандартные блокировки на уровне строк, включая два типа: разделяемые блокировки (называемые блокировками s) и эксклюзивные блокировки (называемые блокировками x).
- Общая блокировка (S-блокировка): позволяет блокирующей транзакции читать строку.
- Эксклюзивная блокировка (блокировка X): позволяет блокирующей транзакции обновлять или удалять строку.
Если транзакция T1 удерживает блокировку s на строке r, когда другая транзакция T2 запрашивает блокировку на r, она сделает следующее:
- T2 запрашивает немедленное предоставление блокировки s, и в результате T1 и T2 удерживают блокировку s на строке r.
- Запрос T2 x блокировка не может быть предоставлена немедленно
Если T1 удерживает блокировку x для r, то запрос T2 на блокировки x и s для r не может быть разрешен немедленно, и T2 должен ждать, пока T1 освободит блокировку x, потому что блокировка X несовместима ни с какими блокировками.
блокировка намерения
- Намеренная общая блокировка (блокировка IS): транзакция хочет получить общую блокировку определенных строк в таблице.
- Преднамеренная монопольная блокировка (блокировка IX): транзакция хочет получить монопольную блокировку определенных строк в таблице.
Например: после того, как транзакция 1 добавит блокировку S в таблицу 1, транзакция 2 должна добавить блокировку IX для изменения записи строки.Из-за несовместимости ей нужно дождаться освобождения блокировки S. Если транзакция 1 добавит блокировку Блокировка IS для таблицы 1, блокировка IX, добавленная транзакцией 2, совместима с блокировкой IS, и ею можно управлять, что реализует более точную блокировку.
Совместимость блокировок в механизме хранения InnoDB следующая:
Блокировка записи
- Блокировки записей — это простейшие блокировки строк.заблокировать только одну строку. как:
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE - блокировка записивсегда добавляется в индексДа, даже если у таблицы нет индекса, InnoDB неявно создаст индекс и будет использовать этот индекс для обеспечения блокировки записей.
- Будет блокировать другие транзакции для вставки, обновления, удаления
Запись данных транзакции блокировки (ключевые слова:lock_mode X locks rec but not gap), записано следующим образом:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
Гэп-замки
- Гэп-блокировка — это блокировка, помещаемая между двумя индексами, либо перед первым индексом, либо после последнего индекса.
- Использование блокировок пробелов блокирует интервал, а не только каждую часть данных в этом интервале.
- Гэп-блокировки только предотвращают вставку других транзакций в гэп, они не мешают другим транзакциям получить гэп-блокировки на том же гэпе, так что гэп x блокировка и гэп s блокировка имеют одинаковый эффект.
Данные о транзакциях для гэп-локов (ключевые слова:gap before rec), записано следующим образом:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 576569; asc Wei;;
1: len 4; hex 80000002; asc ;;
Next-Key Locks
- Блокировка следующего ключа представляет собой комбинацию блокировки записи и блокировки промежутка, которая относится к блокировке, добавленной к записи, и промежутку перед этой записью.
Вставить блокировку намерения
- Блокировка намерения вставки — это своего рода блокировка промежутка, устанавливаемая перед операцией вставки строки записей.Эта блокировка снимает сигнал режима вставки, то есть когда в один и тот же промежуток индекса вставляется несколько транзакций, если они не вставлены в одинаковое положение в промежутке они не вставятся.нужно ждать друг друга.
- Предположим, что есть значения индекса 4 и 7, и несколько разных транзакций готовы вставить 5 и 6. Каждая блокировка использует блокировку намерения вставки для блокировки промежутка между 4 и 7 перед получением монопольной блокировки для вставленной строки, но не не блокировать.Другая сторона не конфликтует из-за вставленной строки.
Данные транзакции аналогичны следующему:
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...
Матрица совместимости режимов блокировки (горизонтальные блокировки сохранены, вертикальные блокировки запрошены):
Как прочитать журнал взаимоблокировок?
show engine innodb status
Можно использоватьshow engine innodb status, проверьте последний журнал взаимоблокировок ha~, после выполнения журнал взаимоблокировок выглядит следующим образом:
2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 576569; asc Wei;;
1: len 4; hex 80000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 576569; asc Wei;;
1: len 4; hex 80000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
Как мы можем проанализировать приведенный выше журнал взаимоблокировок?
первая часть
1) Найдите ключевое слово ТРАНЗАКЦИЯ, транзакция 38048.
2) Просмотр выполняемого SQL
insert into account values(null,'Jay',100)
3) Ожидание снятия блокировки (ОЖИДАНИЕ ПРЕДОСТАВЛЕНИЯ ЭТОЙ БЛОКИРОВКИ), намерение вставить эксклюзивную блокировку (lock_mode X блокирует промежуток перед ожиданием намерения вставки записи), общий индекс (idx_name), физическая запись (PHYSICAL RECORD), интервал промежутка (неизвестно, Вэй);
Вторая часть
1) Найдите ключевое слово ТРАНЗАКЦИЯ, транзакция 38049.
insert into account values(null,'Yan',100)
3) Удерживает блокировку (HOLDS THE LOCK), блокировку пробела (lock_mode X блокирует пробел перед записью), обычный индекс (индекс idx_name), физическую запись (физическая запись), интервал (неизвестно, Wei);
4) Ожидание освобождения блокировки (ожидание предоставления этой блокировки), намерение вставить блокировку (lock_mode X ожидание намерения вставки), общий индекс (index idx_name), физическая запись (физическая запись), интервал промежутка (неизвестно, +∞);
5) Откатывается транзакция 1 (откатываем транзакцию 1);
Просмотр результатов журнала
- Эксклюзивная блокировка намерения вставки, которую ожидает транзакция A (транзакция A является транзакцией 1 журнала, в соответствии с оператором вставки, который должен быть установлен), в руках транзакции B ~
- Транзакция B удерживает гэп-блокировку и ожидает монопольной блокировки с целью вставки.
Здесь у некоторых друзей может бытьпутать,
- Какой замок удерживает транзакция А? Логи вообще не видны. Какую эксклюзивную блокировку намерения вставки он хочет использовать?
- Какую конкретную гэп-блокировку использовала транзакция B? Почему также требуется блокировка намерения вставки?
- Как формируется тупиковый цикл? В настоящее время журнал не может видеть образование бесконечного цикла?
Мы подробно проанализируем волну в следующем разделе, один за другим~
Анализ взаимоблокировок
Четыре элемента тупиковой мертвой петли
- Условие взаимного исключения: относится к исключительному использованию выделенных ресурсов процессом, то есть ресурс занят только одним процессом в течение определенного периода времени. Если в это время есть другие процессы, запрашивающие ресурсы, запрашивающая сторона может только ждать, пока процесс, занимающий ресурсы, не будет израсходован и освобожден.
- Условия запроса и удержания: относится к процессу, который удерживал хотя бы один ресурс, но сделал новый запрос ресурса, и ресурс был занят другими процессами.В это время запрашивающий процесс заблокирован, но он сохраняет другие ресурсы. что он получил.
- Условие отсутствия депривации: относится к ресурсу, который был получен процессом, не может быть лишен, пока он не будет использован, и может быть освобожден только сам по себе, когда он будет израсходован.
- Условие ожидания цикла: при возникновении взаимоблокировки должна быть цепочка циклов процесс-ресурс, то есть P0 в наборе процессов {P0, P1, P2, ···, Pn} ожидает ресурса, занятого P1; P1 ожидает ресурсов, занятых P2, ..., Pn ожидает ресурсов, уже занятых P0.
Какой замок удерживает транзакция А? Какую эксклюзивную блокировку намерения вставки он хочет использовать?
Для удобства записи в примерах W используется для обозначения Wei, J — для Jay, а E — для Eason~.
Давайте сначала проанализируем ситуацию блокировки оператора обновления в транзакции A~
update account set balance =1000 where name ='Wei';
Блокировка пробела:
- Оператор Update добавит блокировку пробела в левом интервале и блокировку пробела в правом интервале к имени неуникального индекса (поскольку в таблице есть только одна запись с name='Wei', поэтому нет промежуточный зазор ~), то есть (E, W) и (W, +∞)
- Почему существуют гэп-замки? Поскольку это уровень изоляции базы данных RR, он используется для решения проблемы фантомного чтения~
блокировка записи
- Поскольку имя является индексом, оператор обновления обязательно добавит блокировку записи W.
Блокировка следующего ключа
- Блокировка следующего ключа = блокировка записи + блокировка промежутка, поэтому оператор обновления имеет блокировку следующего ключа (E, W]
Подводя итог, после того, как транзакция A выполнит оператор обновления, она будет удерживать блокировку:
- Блокировка следующей клавиши: (E, W]
- Gap Lock: (W, +∞)
Давайте проанализируем ситуацию блокировки оператора вставки в волне транзакции А.
insert into account values(null,'Jay',100);
Блокировка пробела:
- Поскольку Jay (J находится между E и W), необходимо запросить блокировку пробела с помощью (E, W)
Вставить блокировку намерения
- Блокировка намерения вставки — это блокировка пробела, устанавливаемая перед вставкой строки записей.Эта блокировка освобождает сигнал режима вставки, то есть транзакции A необходимо вставить блокировку намерения (E, W)
следовательно, после выполнения оператора update и оператора вставки транзакции A он содержит(E, W] Блокировка следующей клавиши,(W, +∞) Блокировка зазора, хочу получитьЭксклюзивная блокировка с намерением вставки для (E,W), ожидающая блокировка совпадает с журналом взаимоблокировок, ха-ха~
Какой гэп-блокировкой владеет транзакция B? Почему также требуется блокировка намерения вставки?
Таким же образом проанализируем волну транзакции B, анализ блокировки оператора обновления:
update account set balance =1000 where name ='Eason';
Блокировка пробела:
- Оператор Update добавит блокировку пробела в левом интервале и блокировку пробела в правом интервале к имени неуникального индекса (поскольку в таблице есть только одна запись с именем = 'Eason', поэтому нет промежуточный зазор~), то есть (-∞ , E) и (E, W)
блокировка записи
- Поскольку имя является индексом, оператор обновления обязательно добавит блокировку записи E.
Блокировка следующего ключа
- Блокировка следующего ключа = блокировка записи + блокировка промежутка, поэтому оператор Update имеет блокировку следующего ключа (-∞, E]
Подводя итог, после того, как транзакция B выполнит оператор обновления, она будет удерживать блокировку:
- Блокировка следующей клавиши: (-∞, E]
- Блокировка пробела: (E, W)
Давайте проанализируем ситуацию блокировки оператора вставки в волне B
insert into account values(null,'Yan',100);
Блокировка пробела:
- Поскольку Ян (Y после W), необходимо запросить блокировку пробела с (W, +∞)
Вставить блокировку намерения
- Блокировка намерения вставки — это своего рода блокировка промежутка, устанавливаемая перед вставкой операции записи строки.Эта блокировка освобождает сигнал режима вставки, то есть транзакции A необходимо вставить блокировку намерения (W,+∞)
так, после выполнения оператора обновления и оператора вставки транзакции B он содержит(-∞, E] Блокировка следующей клавиши,(E, W) Блокировка зазора, хочу получить(W, +∞) блокировка пробела, то есть блокировка исключительного намерения вставки, ситуация с блокировкой и журнал взаимоблокировок также верны~
Восстановление правды в тупике
Далее, давайте вместе восстановим правду о тупиковой ситуации~ ха-ха~
- Транзакция A завершает выполнение оператора Update Wei, удерживает блокировку следующего ключа (E, W), блокировку промежутка (W, +∞), и вставка выполнена успешно~
- Транзакция B выполняет оператор Update Eason, удерживает блокировку следующего ключа (-∞, E] и блокировку промежутка (E, W), и вставка выполняется успешно~
- Когда транзакция A выполняет оператор Insert Jay, ей требуется блокировка намерения вставки (E, W), но (E, W) находится в руках транзакции B, поэтому она застряла в сердце~
- Когда транзакция B выполняет оператор Insert Yan, ей требуется блокировка намерения вставки (W,+∞), но (W,+∞) находится в руках транзакции A, поэтому она также зависла.
- Транзакция A удерживает блокировку Gap (W, +∞), ожидая блокировки намерения вставки (E, W), а транзакция B удерживает блокировку Gap (E, W), ожидая намерения вставки (W, W). +∞) блокировка, поэтому формируется замкнутый цикл взаимоблокировки~ (блокировка промежутка и блокировка намерения вставки будут конфликтовать, вы можете увидеть матрицу совместимости режима блокировки, представленную Back Lock~)
- После того, как транзакции A и B образуют тупиковый замкнутый цикл, из-за базового механизма Innodb он позволит одной из транзакций отказаться от ресурсов, а другие транзакции будут выполнены успешно, поэтому вы, наконец, видите, что транзакция B успешно вставлена. , но транзакция A Inserting показывает, что обнаружена взаимоблокировка ~
Суммировать
Наконец, как мы должны анализировать проблему взаимоблокировки?
- Моделирование сценария взаимоблокировки
- показать статус движка innodb, просмотреть журнал взаимоблокировок
- Найти тупиковый SQL
- Анализ блокировки SQL,Вы можете проверить это на официальном сайте
- Анализ журналов взаимоблокировок (какие блокировки удерживаются, какие блокировки ожидают)
- Ознакомьтесь с матрицей совместимости режима блокировки, матрицей совместимости блокировок в механизме хранения InnoDB.
Личный публичный аккаунт
- Если вы считаете, что это хорошо написано, пожалуйста, поставьте лайк + подпишитесь, спасибо~
- Если что-то не так, укажите на это, большое спасибо.
- В то же время, я очень жду, что мои друзья обратят внимание на мой официальный аккаунт, и позже я постепенно представлю более качественные галантерейные товары~ хи хи