Как устранить взаимоблокировку Mysql: вставить в повторяющуюся взаимоблокировку процесс анализа устранения неполадок

предисловие

Обнаружена проблема взаимоблокировки Mysql, как мы должны устранять неполадки и анализировать? Ранее в Интернете была вставка о проблеме дублирования тупиковой блокировки. Основываясь на этой проблеме тупиковой ситуации, в этой статье мы поделимся процессом устранения неполадок и анализа, надеясь помочь всем.

восстановление тупиковой ситуации

Структура таблицы:

CREATE TABLE `song_rank` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `songId` int(11) NOT NULL,
 `weight` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `songId_idx` (`songId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Уровень изоляции:

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

Версия базы данных:

+------------+
| @@version  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

Отключить автокоммит:

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (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)

Данные в таблице:

mysql> select * from song_rank;
+----+--------+--------+
| id | songId | weight |
+----+--------+--------+
|  1 |     10 |     30 |
|  2 |     20 |     30 |
+----+--------+--------+
2 rows in set (0.01 sec)

Причина взаимоблокировки:

В параллельной среде выполнение вставки в... при обновлении двойного ключа... приводит к взаимоблокировке

Воспроизведение моделирования тупика:

Первая транзакция выполняется:

mysql> begin;    //第一步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(15,100) on duplicate key update  weight=weight+1;  //第二步
Query OK, 1 row affected (0.00 sec)

mysql> rollback;   //第七步
Query OK, 0 rows affected (0.00 sec)

Выполнение транзакции 2:

mysql> begin;   //第三步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1;  //   第四步
Query OK, 1 row affected (40.83 sec)

Выполняется третья транзакция:

mysql> begin;  //第五步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1; //第六步

Транзакция 1, транзакция 2, транзакция 3 выполняются:

шаг	бизнес один	дело два	дело три
первый шаг	begin;
второй шаг	insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; (Query OK, 1 row affected (0.00 sec) )
третий шаг		begin;
четвертый шаг		вставить в song_rank(songId,weight) значения (16 100) при обновлении повторяющегося ключа weight=weight+1;//заблокировано
пятый шаг			begin;
Шаг 6			Вставка в значения SONG_RANK (PONGID, ESS) (18 100) на дублируемое обновление ключа = вес + 1; // заблокирован
Шаг 7	rollback;
результат		Query OK, 1 row affected (40.83 sec)	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Возник тупик:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Исследование и анализ тупиков

Что нам делать, когда мы сталкиваемся с проблемами взаимоблокировки? в несколько шагов

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

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

show engine innodb status;

Журнал вставки при дублировании проблемы взаимоблокировки выглядит следующим образом:

*** (1) TRANSACTION:
TRANSACTION 27540, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root update
insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27540 lock_mode X 
locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 27539, ACTIVE 41 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 6976, query id 580 localhost ::1 root update
insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X 
locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X 
locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

2. Проанализируйте журнал взаимоблокировок

Как анализировать логи дедлоков, поделитесь своими мыслями

• Журнал взаимоблокировок делится на транзакцию 1, транзакцию 2 делится
• Узнайте, какой SQL заблокирован
• Узнайте, какие блокировки удерживает транзакция и какие блокировки ждут
• Анализ блокировки SQL

1 Анализ журнала транзакций

Из журнала мы видим, что SQL, выполняемый транзакцией 1, выглядит следующим образом:

insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1

Этот оператор ожидает монопольной блокировки с намерением вставки индекса songId_idx:

lock_mode X locks gap before rec insert intention waiting

Анализ журнала транзакции 2

Из журнала мы видим, что SQL, выполняемый транзакцией 2, выглядит следующим образом:

insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1

Этот оператор содержит блокировку пробела для индекса songId_idx:

lock_mode X locks gap before rec

Этот оператор ожидает монопольной блокировки с намерением вставки индекса songId_idx:

lock_mode X locks gap before rec insert intention waiting

Дополнительные концепции, связанные с блокировкой (прилагаются):

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

Карта разума типа блокировки InnoDB:

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

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

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 несовместима ни с какими блокировками.

2. Блокировка намерения

• Намеренная общая блокировка (блокировка IS): транзакция хочет получить общую блокировку определенных строк в таблице.
• Интерактивный эксклюзивный замок (IX Lock): транзакция хочет получить эксклюзивный замок на определенных рядах в таблице

Например: после того, как транзакция 1 добавит блокировку S в таблицу 1, транзакция 2 должна добавить блокировку IX для изменения записи строки.Из-за несовместимости ей нужно дождаться освобождения блокировки S. Если транзакция 1 добавит блокировку Блокировка IS для таблицы 1, блокировка IX, добавленная транзакцией 2, совместима с блокировкой IS, и ею можно управлять, что реализует более точную блокировку.

Совместимость блокировок в механизме хранения InnoDB следующая:

совместимость	IS	IX	S	X
IS	совместимый	совместимый	совместимый	несовместимый
IX	совместимый	совместимый	несовместимый	несовместимый
S	совместимый	несовместимый	совместимый	несовместимый
X	несовместимый	несовместимый	несовместимый	несовместимый

3. Блокировка записи

• Блокировки записей — это простейшие блокировки строк.заблокировать только одну строку. как: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        ;;

4. Зафиксируйте зазор (Gap Locks)

• Гэп-блокировка — это блокировка, помещаемая между двумя индексами, либо перед первым индексом, либо после последнего индекса.
• Использование блокировок пробелов блокирует интервал, а не только каждую часть данных в этом интервале.
• Гэп-блокировки только предотвращают вставку других транзакций в гэп, они не мешают другим транзакциям получить гэп-блокировки на том же гэпе, так что гэп x блокировка и гэп s блокировка имеют одинаковый эффект.

5.Next-Key Locks

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

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

• Блокировка намерения вставки — это своего рода блокировка промежутка, устанавливаемая перед операцией вставки строки записей.Эта блокировка снимает сигнал режима вставки, то есть когда в один и тот же промежуток индекса вставляется несколько транзакций, если они не вставлены в одинаковое положение в промежутке они не вставятся.нужно ждать друг друга.
• Предположим, что есть значения индекса 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  ;;...

Анализ блокировки SQL:

Проанализировав журнал взаимоблокировок, мы можем найти SQL, где произошла взаимоблокировка, и связанные с ними ожидающие блокировки. Затем мы выполняем анализ блокировок для соответствующего SQL. Фактически проблема решена.

Хорошо, вернемся к соответствующему SQL, вставим в song_rank(songId,weight) значения (16 100) при обновлении дубликата ключа weight=weight+1. Какая блокировка добавлена ​​в процесс исполнения?Официальный документ механизма блокировки

вставить стратегию блокировки:

Оператор вставки добавит эксклюзивную блокировку записи к вставленной записи. Перед добавлением блокировки записи будет добавлена ​​блокировка GAP, называемая блокировкой намерения вставки. Если возникает конфликт уникальных ключей, блокировка общей записи (S )Lock.

(Анализ блокировки SQL очень важен, вот статья, которую я вам рекомендую, она очень хорошая,Путь к решению взаимоблокировок — анализ блокировок общих операторов SQL)

вставка при проверке дублирующего ключа

Чтобы проверить вставку на дублирующуюся ситуацию с блокировкой ключей, давайте возьмем транзакцию 1 и 2 демонстрации выше, чтобы пройти через процесс. Транзакция 1:

mysql> begin;    //第一步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(15,100) on duplicate key
update  weight=weight+1;  //第二步
Query OK, 1 row affected (0.00 sec)

Службы 2 (открыть другое окно):

mysql> begin;   //第三步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(16,100) on duplicate key 
update  weight=weight+1;  //   第四步

использоватьshow engine innodb statusПросмотрите информацию о текущем запросе на блокировку, как показано на рисунке:

В наличии есть картинки:

Транзакция 2 содержит:Блокировка IX (блокировка таблицы), блокировка промежутка x, блокировка намерения вставки (ожидание блокировки промежутка транзакции 1)

Следовательно, эти три блокировки будут удерживаться во время выполнения вставки в дубликат.

Анализ причины блокировки

Возвращаясь к сцене моделирования случая взаимоблокировки (транзакции 1, 2, 3) и сцене журнала взаимоблокировки, представленной в начале этой статьи,

Блокировка транзакции 1 после инцидента:

Блокировка транзакции 2 после инцидента:

Путь восстановления после инцидента:

1. Сначала выполните транзакцию 1, чтобы выполнить:begin; insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1;Блокировка GAP (10, 20), INSERT INTENTION LOCK (вставка блокировки намерения)

2. Далее, транзакция 2 выполняется:begin; insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1;получит гэп-лок (10,20), и в то же времяждатьВставьте блокировку намерения транзакции 1.

3. Затем выполняется транзакция 3:begin; insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1;получит гэп-лок (10,20), и в то же времяждатьВставьте блокировку намерения транзакции 1.

4. Наконец,Транзакция 1 откатывается, освобождая блокировку намерения вставки, в результате чего транзакции 2 и 3 одновременно удерживают блокировки промежутка, ожидая блокировки намерения вставки, и формируется взаимоблокировка.!

Таблица совместимости режимов блокировки(пейзаж — блокировка удерживается, портрет — запрашиваемая блокировка):

совместимость	Gap	Insert Intention	Record	Next-Key
Gap	совместимый	совместимый	совместимый	совместимый
Insert Intention	конфликт	совместимый	совместимый	конфликт
Record	совместимый	совместимый	конфликт	конфликт
Next-Key	совместимый	совместимый	конфликт	конфликт

Это ошибка MySql5.7

Как избежать вставки в дублирующем тупике

1. Измените вставку на дубликат на вставку

   try{
       insert();
   }catch(DuplicateKeyException e){
       update();
   }

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

2. Изменить версию MySql

Поскольку это ошибка MySql5.7, рассмотрите возможность изменения версии Mysql.

3. Минимизируйте использование уникального индекса.

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

Резюме этой статьи (важно)

В этой статье представлена ​​ошибка взаимоблокировки MySql5.7. как мы должны идтиУстранение взаимоблокировокШерстяная ткань?

• 1. показать статус движка innodb, просмотреть журнал взаимоблокировок
• 2. Найдите мёртвую блокировку SQL
• 3. Анализ блокировки SQL
• 4. Проанализируйте журнал взаимоблокировок (какие блокировки удерживаются и какие блокировки ожидают)
• 5. Знаком с матрицей совместимости режима блокировки, матрицей совместимости блокировки в механизме хранения InnoDB.

Ссылка и спасибо

• Кровавый случай, вызванный Вставкой на дубликате
• Прочитайте исходный код MySQL и посмотрите процесс блокировки INSERT.
• Путь в тупик — общие типы блокировок
• Mysql InnoDB Lock - Официальные документы
• Механизм блокировки MySQL - блокировка записи, блокировка пробела, блокировка ключа
• Путь к решению взаимоблокировок — анализ блокировок общих операторов SQL
• "Инсайдер технологии MySQL"

Личный публичный аккаунт

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