Как разрешить тупик в MySQL из-за аудита спящего режима?

При параллельном выполнении нескольких транзакций в большинстве случаев я захожу в тупик:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-04 06:19:12 0x2b01917c7700
*** (1) TRANSACTION:
TRANSACTION 14470484, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 4
MySQL thread id 69372, OS thread handle 47285779531520, query id 10366178979 172.31.19.11 master updating
update `VerificationActionLog_AUD` set `REVEND`=427956 where `id`=138136 and `REV`<> 427956 and `REVEND` is null
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470484 lock_mode X waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** (2) TRANSACTION:
TRANSACTION 14470485, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4
MySQL thread id 69395, OS thread handle 47285735814912, query id 10366178981 172.31.19.11 master updating
update `VerificationActionLog_AUD` set `REVEND`=427957 where `id`=138137 and `REV`<> 427957 and `REVEND` is null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470485 lock_mode X locks rec but not gap
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470485 lock_mode X waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** WE ROLL BACK TRANSACTION (2)

Я пытаюсь понять, что объясняют эти утверждения. Насколько я понимаю, транзакция 2 удерживает блокировку первичного индекса _2 _._ 3_. В то же время транзакция 2 также ожидает такой же блокировки. Как это возможно, что одна транзакция удерживается и ожидает одной и той же блокировки?

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


person Rajat Goel    schedule 04.09.2019    source источник
comment
Из операторов UPDATE ясно, что вы используете ValidityAuditStrategy и обновляете столбец REVEND более старой записи при создании новой строки аудита. У вас не должно возникать взаимоблокировок, если вы не пытаетесь выполнить какое-либо поведение вложенной транзакции для одного и того же объекта. Трудно сказать, не видя вашего java-кода и обработки транзакций.   -  person Naros    schedule 04.09.2019
comment
@Naros Спасибо за ваш комментарий. Хотя все еще не могу решить эту проблему, но теперь я немного лучше понимаю. У меня несколько вопросов. Есть ли в MySQL вложенные транзакции? Мы вручную запускаем и фиксируем транзакции. Я не думаю, что есть какая-то часть, где мы начинаем транзакцию внутри уже запущенной транзакции. Но мы делаем промывку между транзакциями. Может ли ручная очистка вызвать вложенную транзакцию?   -  person Rajat Goel    schedule 05.09.2019
comment
Предоставьте SHOW CREATE TABLE VerificationActionLog_AUD и несколько подсказок о том, что еще находится в той же транзакции. Кроме того, были ли они в «конце» таблицы?   -  person Rick James    schedule 06.09.2019
comment
Объясните, что вы имеете в виду под смывом.   -  person Rick James    schedule 06.09.2019
comment
Мы используем спящий режим. Очень хорошее объяснение дается в stackoverflow.com/questions/3220336/. Также проверьте ответ 2   -  person Rajat Goel    schedule 06.09.2019
comment
Пока flush происходит как часть текущей связанной транзакции, это не должно иметь никакого влияния. Строка, страница данных или таблица будут заблокированы одним и тем же сеансом, поэтому вы не увидите тупиковой ситуации. Что-то еще должно происходить, чтобы вызвать это, но без кода мало что кто-то может сделать, чтобы пролить свет на вашу проблему.   -  person Naros    schedule 09.09.2019


Ответы (1)


Это происходит из-за блокировки Gap. Блокировка промежутка - это блокировка промежутка между индексными записями или блокировка промежутка перед первой или после последней индексной записи.

Скажем, у вас есть смежные id, 1 и 2. Когда процедура выполняется одновременно из 2 разных сеансов, каждый из них устанавливает блокировку пробела на двух индексных записях (со значениями id 1 и 2 - может быть, также 0, 4,5, но давайте для простоты предположим всего 2), и каждый из них должен ждать, пока другой снимет блокировку для выполнения вставки.

Блокировки промежутков в InnoDB являются «чисто ингибирующими», что означает, что они только останавливают вставку других транзакций в промежуток. Они не препятствуют тому, чтобы разные транзакции блокировали один и тот же разрыв. Таким образом, X-образная защелка имеет тот же эффект, что и S-образная защелка *. "

Решение:

Блокировку зазора можно отключить явно. Это происходит, если вы измените уровень изоляции транзакции на READ COMMITTED или включите системную переменную innodb_locks_unsafe_for_binlog (которая теперь устарела). В этих обстоятельствах блокировка пропусков отключена для поиска и сканирования индекса и используется только для проверки ограничений внешнего ключа и проверки дублированного ключа.

Ссылки:

person Rajat Goel    schedule 09.09.2019
comment
А для изменения уровня isloation в спящем режиме используйте свойство hibernate.connection.isolation - person Rajat Goel; 09.09.2019
comment
Рад, что вы нашли проблему. - person Naros; 09.09.2019