Как проанализировать проблему взаимоблокировки MySQL

MySQL

Ярлыки: Статьи официального аккаунта

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

Готов к работе

Для плавного развития истории нам нужно построить таблицу:

CREATE TABLE hero (
    id INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (id),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

Мы создали кластеризованный индекс для столбца id главной таблицы и вторичный индекс для столбца name. Эта таблица героев в основном используется для хранения некоторых героев периода Троецарствия.Мы вставляем в таблицу некоторые записи:

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

Теперь данные в таблице выглядят так:

mysql> SELECT * FROM hero;
+----+------------+---------+
| id | name       | country |
+----+------------+---------+
|  1 | l刘备      | 蜀      |
|  3 | z诸葛亮    | 蜀      |
|  8 | c曹操      | 魏      |
| 15 | x荀彧      | 魏      |
| 20 | s孙权      | 吴      |
+----+------------+---------+
5 rows in set (0.00 sec)

Приготовления сделаны.

Создать тупиковую ситуацию

Сначала мы создаем тупиковую ситуацию, вSession AиSession BВыполните две транзакции соответственно, детали следующие:

image_1ddpitcafvd9147kqen1clhipc9.png-124.9kB

Давайте проанализируем:

  • Как видно из шага 3,Session Aсделки вheroтабличный кластеризованный индексidЗапись со значением 1 добавляется сX型正经记录锁.

  • Из шага 4 видно, чтоSession Bпара транзакций вheroтабличный кластеризованный индексidЗапись со значением 3 добавляетX型正经记录锁.

  • Из шага 5 видно, чтоSession Aсделка вheroтабличный кластеризованный индексidЗаписи со значением 3 также добавляютX型正经记录锁, но так же, как в шаге ④Session BКонфликт блокировок, добавленный транзакцией вSession AВойдите в состояние блокировки, ожидая получения блокировки.

  • Как видно из шага 6,Session Bбизнес вheroтабличный кластеризованный индексidЗапись со значением 1 добавляется сX型正经记录锁, но так же, как в шаге ③Session AКонфликт блокировок, добавленный транзакцией вSession AиSession BТранзакция в цикле ожидает блокировки другой стороны, возникает взаимоблокировка,MySQLМеханизм обнаружения взаимоблокировок сервера обнаруживает это, поэтому выбирает транзакцию для отката и отправляет сообщение клиенту:

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

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

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

дизайнInnoDBДядя подарил намSHOW ENGINE INNODB STATUSКоманда для просмотра некоторой информации о состоянии механизма хранения InnoDB, включая ситуацию блокировки, когда произошла последняя взаимоблокировка системы. Когда возникает взаимоблокировка в приведенном выше примере, мы запускаем эту команду:

mysql> SHOW ENGINE INNODB STATUS\G
...省略了好多其他信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-20 13:39:19 0x70000697e000
*** (1) TRANSACTION:
TRANSACTION 30477, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics
select * from hero where id = 3 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d0011d; asc        ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

*** (2) TRANSACTION:
TRANSACTION 30478, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d0011d; asc        ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007517; asc     u ;;
 2: len 7; hex 80000001d00110; asc        ;;
 3: len 7; hex 6ce58898e5a487; asc l      ;;
 4: len 3; hex e89c80; asc    ;;

*** WE ROLL BACK TRANSACTION (2)
------------
...省略了好多其他信息

Нас интересует только самая последняя информация о тупиковой ситуации, поэтому мы помещаемLATEST DETECTED DEADLOCKЭта часть представлена ​​отдельно для анализа. Давайте построчно посмотрим, что означает журнал взаимоблокировок этого вывода:

  • Сначала посмотрите на первое предложение:

    2019-06-20 13:39:19 0x70000697e000

    Смысл этого предложения в том, что время возникновения взаимоблокировки: 2019-06-20 13:39:19, за которым следует строка шестнадцатеричных чисел.0x70000697e000Указывает идентификатор потока, выделенного операционной системой для текущего сеанса.

  • Затем идет информация о первой транзакции, когда возникает взаимоблокировка:

    *** (1) TRANSACTION:

# 为事务分配的id为30477，事务处于ACTIVE状态已经10秒了，事务现在正在做的操作就是：“starting index read”
TRANSACTION 30477, ACTIVE 10 sec starting index read

# 此事务使用了1个表，为1个表上了锁（此处不是说为该表加了表锁，只要不是进行一致性读的表，都需要加锁，具体怎么加锁请看加锁语句分析或者小册章节）
mysql tables in use 1, locked 1

# 此事务处于LOCK WAIT状态，拥有3个锁结构（2个行锁结构，1个表级别X型意向锁结构，锁结构在小册中重点介绍过），heap size是为了存储锁结构而申请的内存大小（我们可以忽略），其中有2个行锁的结构
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)

# 本事务所在线程的id是2（MySQL自己命名的线程id），该线程在操作系统级别的id就是那一长串数字，当前查询的id为46（MySQL内部使用，可以忽略），还有用户名主机信息
MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics

# 本事务发生阻塞的语句
select * from hero where id = 3 for update

# 本事务当前在等待获取的锁：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

# 等待获取的表空间ID为151，页号为3，也就是表hero的PRIMAY索引中的某条记录的锁（n_bits是为了存储本页面的锁信息而分配的一串内存空间，小册中有详细介绍），该锁的类型是X型正经记录锁（rec but not gap）
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting

# 该记录在页面中的heap_no为2，具体的记录信息如下：
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

# 这是主键值
0: len 4; hex 80000003; asc     ;;

# 这是trx_id隐藏列
1: len 6; hex 000000007517; asc     u ;;

# 这是roll_pointer隐藏列
2: len 7; hex 80000001d0011d; asc        ;;

# 这是name列
3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;

# 这是country列
4: len 3; hex e89c80; asc    ;;

    Из этой информации видно, чтоSession AТранзакция сгенерировала структуру блокировки для 2 записей, но одна из них имеетX型正经记录锁(запись, но не пробел) не получен, а позиция записи, не получившей блокировку, такова: идентификатор табличного пространства — 151, номер страницы — 3, номер кучи — 2. Конечно, дизайнInnoDBДядя также подробно рассказал подробности этой записи, ее значение первичного ключа80000003, который на самом деле является форматом, используемым внутренним хранилищем InnoDB, который на самом деле представляет числа3, то есть транзакция ожидает полученияheroЗначением первичного ключа кластеризованного индекса таблицы является3этой записиX型Серьезная блокировка записи.

  • Затем идет информация о второй транзакции, когда происходит взаимоблокировка:

    Мы уже представили большую часть информации, поэтому давайте выберем самое важное:

    *** (2) TRANSACTION:
TRANSACTION 30478, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 for update

# 表示该事务获取到的锁信息
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

# 主键值为3
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000000007517; asc     u ;;
2: len 7; hex 80000001d0011d; asc        ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
4: len 3; hex e89c80; asc    ;;

# 表示该事务等待获取的锁信息
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

# 主键值为1
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000000007517; asc     u ;;
2: len 7; hex 80000001d00110; asc        ;;
3: len 7; hex 6ce58898e5a487; asc l      ;;
4: len 3; hex e89c80; asc    ;;

    Как видно из приведенного выше вывода,Session BТранзакций в извлеченныхheroЗначением первичного ключа кластеризованного индекса таблицы является3записейX型正经记录锁, ожидание полученияheroЗначением первичного ключа кластеризованного индекса таблицы является1записейX型正经记录锁(Подразумеваемый смысл такойheroЗначением первичного ключа кластеризованного индекса таблицы является1записейX型正经记录锁БылSESSION AТранзакция в фаворитах).

  • Смотрите последнюю часть:

    *** WE ROLL BACK TRANSACTION (2)

    Наконец, механизм хранения InnoDB решил откатить вторую транзакцию, котораяSession Bв этой сделке.

Идеи анализа взаимоблокировок

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

    В этом примере было найденоSESSION AЗаявление о блокировке:

    select * from hero where id = 3 for update

    SESSION BЗаявление о блокировке:

    select * from hero where id = 1 for update

    Тогда помните:Найдите другие операторы в транзакции, где эти два оператора расположены в вашем собственном бизнес-коде..

  2. Найдя все операторы в транзакции, в которой возникла взаимоблокировка, проанализируйте процесс возникновения взаимоблокировки на основе информации о блокировках, полученных транзакцией, и блокировках, находящихся в ожидании.

    Как видно из журнала взаимоблокировок,SESSION Aприобретенныйheroтабличный кластеризованный индексidзаписи со значением 1X型正经记录锁(Это на самом деле изSESSION Bполученный из ожидающей блокировки), см.SESSION AУтверждение в , как выяснилось, вызвано следующим утверждением (проанализируйте три статьи против блокировки операторов):

    select * from hero where id = 1 for update;

    иSESSION Bприобретенныйheroтабличный кластеризованный индексidзаписи со значением 3X型正经记录锁,ПроверятьSESSION BУтверждение в , как выяснилось, вызвано следующим утверждением (проанализируйте три статьи против блокировки операторов):

    select * from hero where id = 3 for update;

    тогда смотриSESSION Aожидающийheroтабличный кластеризованный индексidзаписи со значением 3X型正经记录锁, что вызвано следующим утверждением:

    select * from hero where id = 3 for update;

    тогда смотриSESSION Bожидающийheroтабличный кластеризованный индексidзаписи со значением 1X型正经记录锁, что вызвано следующим утверждением:

    select * from hero where id = 1 for update;

    Далее весь процесс формирования взаимоблокировок восстанавливается по журналу взаимоблокировок.

Не по теме

Написание статей очень утомительно, и иногда вы чувствуете, что чтение идет очень гладко, что на самом деле является результатом бесчисленных правок за ним. Если вы думаете, что это хорошо, пожалуйста, помогите переслать его.Большое спасибо~ Вот мой публичный аккаунт "Мы все маленькие лягушки".

Категории