Deadlock в MySQL: анализ engine.log

320

30 марта 2017, 19:55

Как понять причину deadlock'а - а именно, какие транзакции захватили какие блокировки?

Есть файл engine.log со следующим deadlock'ом:

------------------------
LATEST DETECTED DEADLOCK
------------------------
170327 11:09:53
*** (1) TRANSACTION:
TRANSACTION 4 2719072253, ACTIVE 5 sec, OS thread id 26215 starting index read
...
INSERT INTO... (1-ая транзакция)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 36025889 n bits 96 index `PRIMARY` of table `mydb`.`mytable` trx id 4 2719072253 lock mode S locks rec but not gap waiting
...
*** (2) TRANSACTION:
TRANSACTION 4 2719072205, ACTIVE 35 sec, OS thread id 25564 starting index read, thread declared inside MyISAM 485
UPDATE ... (2-ая транзакция)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 36025889 n bits 96 index `PRIMARY` of table `mydb`.`mytable` trx id 4 2719072205 lock_mode X locks rec but not gap
Record lock, heap no 27 PHYSICAL RECORD: n_fields 72; compact format; info bits 0
...
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 0 page no 42767646 n bits 120 index `PRIMARY` of table `mydb`.`mytable` trx id 4 2719072205 lock_mode X locks rec but not gap waiting
...
 *** WE ROLL BACK TRANSACTION (1)

Моё видение того, что описано в логах:

1. У транзакции №2 изначально есть одна блокировка (при этом по логам неясно, какого она типа):

*** (2) HOLDS THE LOCK(S)

RECORD LOCKS space id 0 page no 36025889 n bits 96 index PRIMARY of table mydb.mytable trx id 4 2719072205 lock_mode X locks rec but not gap

Record lock, heap no 27 PHYSICAL RECORD: n_fields 72; compact format; info bits 0

и работа продолжается;

2. Транзакция №1 пытается получить блокировку типа S:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS ... trx id 4 2719072253 lock mode S locks rec but not gap waiting

и начинает ждать, когда Транзакция №2 освободит свою блокировку;

3. Транзакция №2 пытается получить блокировку типа X:

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS ... trx id 4 2719072205 lock_mode X locks rec but not gap waiting

и начинает ждать, когда Транзакция №1 получит блокировку типа S и освободит её.

Правильно ли я понимаю логи, или моя интерпретация неверная?

И, так как, судя по документации:

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
A request by T2 for an X lock cannot be granted immediately.

если одна транзакция владеет блокировкой типа S на строке r, то и другая транзакция может захватить эту блокировку S, получается, что изначально у транзакции №2 была блокировка X? Но тогда возникает новый вопрос: зачем второй транзакции ждать получения блокировки X (шаг 3), если она у неё уже есть (с 1-го шага)?

Answer 1

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

Пример типичного Deadlock на основе ваших запросов:

Сессия 1:

INSERT INTO table (primaryKey, value) VALUES (10);

Сессия 2:

UPDATE table SET value = 1 WHERE primaryKey > 3;

Сессия 1:

INSERT INTO table VALUES (5);

DEADLOCK!

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

Два запроса получали блокировки по-разному. Транзакция 2 просматривала индекс. Чтобы получить каждую строку для условия, указанной в UPDATE...WHERE. Естественно он заблокировал какой-то диапазон этого по условию в WHERE, но не смог заблокировать вставленную строку, так как она заблокирована транзакцией 1, и COMMIT еще не сделан. Транзакция ждет, когда блокировка на эту строку будет снята.

Далее, транзакция 1 в этот момент, просматривала индекс с новой строкой, пытаясь найти, куда ее положить. И когда она нашла свободное место, он попытался заблокировать этот диапазон и вставить ее туда, но диапазон уже заблокирован транзакцией 2 и вставить что-то новое туда не получится. Вот и DEADLOCK.

Проблема: транзакция 2 уже сканировала прошлую точку, фиксируя каждую строку по пути и интервал. Если бы этого не произошло, Транзакция 1 могла бы вставить новую строку, и не было бы никакой взаимоблокировки. Кроме того, мы можем сделать вывод, что транзакция 2 сканировала весь путь до (вновь вставленной) строки и остановилась там. Если бы этого не произошло, Deadlock'а бы небыло.

Блокировка транзакции 2 не позволяет транзакции 1 вставлять новую строку. А блокировка транзакции 1, не позволяет транзакции 2 выполнить UPDATE всех строк.

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

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 36025889 n bits 96 index `PRIMARY` of table `mydb`.`mytable` trx id 4 2719072253 lock mode S locks rec but not gap waiting