Блог

Главная - Вопросы по программированию - Взаимоблокировка MariaDB InnoDB при выполнении многих вставок

Взаимоблокировка MariaDB InnoDB при выполнении многих вставок

#sql #mariadb #innodb #deadlock

#sql #mariadb #innodb #взаимоблокировка

Вопрос:

У меня возникают проблемы с взаимоблокировкой с MariaDB (10.0.27), если он находится под давлением. Схема базы данных в основном представляет собой иерархию, и конечный узел вызывает это:

 ------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-11-11 15:34:46 0x7fccf00e5700
*** (1) TRANSACTION:
TRANSACTION 2780, ACTIVE 27 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 211 lock struct(s), heap size 24784, 1373 row lock(s), undo log entries 1389
MySQL thread id 383, OS thread handle 140518385858304, query id 614348 172.26.0.1 mydatabase Update
insert into BARCODE (BARCODE_REC_ID, CODE, COLOR_VARIANT_ID, CREATED, EXTERNAL_RECEIPT_NUM, MODIFIED, SEASON_CODE, SEASON_CODE_EB, SEASON_DESCRIPTION, SEASON_YEAR_EB, TYPE, ID) values (5645669455, '021745228', '9404b25d87630677f68d88417ed3efc7', '2018-05-16 16:53:14', '17', '2018-05-16 16:53:14', 'HW18', null, 'Herbst/Winter 2018', 2018, 'VARIANT_RECEIPT_NUM', '5ff302d48259d09c2030e8bdc21749b8')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 41 page no 15 n bits 416 index UNI_BARCODE of table `mydatabase`.`BARCODE` trx id 2780 lock mode S waiting
Record lock, heap no 340 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000150825f21; asc     P _!;;
 1: len 1; hex 05; asc  ;;
 2: len 30; hex 623939386139366133316265616632376164396539613463363966643831; asc b998a96a31beaf27ad9e9a4c69fd81; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 2775, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
247 lock struct(s), heap size 41168, 1915 row lock(s), undo log entries 3256
MySQL thread id 391, OS thread handle 140518177527552, query id 617936 172.26.0.1 mydatabase Update
insert into BARCODE (BARCODE_REC_ID, CODE, COLOR_VARIANT_ID, CREATED, EXTERNAL_RECEIPT_NUM, MODIFIED, SEASON_CODE, SEASON_CODE_EB, SEASON_DESCRIPTION, SEASON_YEAR_EB, TYPE, ID) values (5647403803, '021631613', '053ba855feea779a8e7cbbdaa63e681b', '2019-08-08 10:07:25', '51', '2019-08-08 10:07:25', 'HW19', null, 'Herbst/Winter 2019', 2019, 'VARIANT_RECEIPT_NUM', 'dc3ce352fb06609dc9b4a1ab87d872d1')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 41 page no 15 n bits 416 index UNI_BARCODE of table `mydatabase`.`BARCODE` trx id 2775 lock_mode X locks rec but not gap
Record lock, heap no 340 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000150825f21; asc     P _!;;
 1: len 1; hex 05; asc  ;;
 2: len 30; hex 623939386139366133316265616632376164396539613463363966643831; asc b998a96a31beaf27ad9e9a4c69fd81; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 41 page no 36 n bits 416 index UNI_BARCODE of table `mydatabase`.`BARCODE` trx id 2775 lock mode S waiting
Record lock, heap no 208 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 80000001509d484a; asc     P HJ;;
 1: len 1; hex 05; asc  ;;
 2: len 30; hex 343638653564396161373030333537623666376531356666623732613735; asc 468e5d9aa700357b6f7e15ffb72a75; (total 32 bytes);

*** WE ROLL BACK TRANSACTION (1)

Вот как выглядит моя листовая таблица:

 CREATE TABLE BARCODE (
  ID CHAR(32) NOT NULL,
  COLOR_VARIANT_ID CHAR(32) NOT NULL,
  BARCODE_REC_ID BIGINT NOT NULL,
  TYPE ENUM('UPCA', 'UPCE', 'EAN13', 'EAN8', 'VARIANT_RECEIPT_NUM') NOT NULL,
  CODE VARCHAR(17) NOT NULL,
  CREATED TIMESTAMP NOT NULL,
  MODIFIED TIMESTAMP NOT NULL,
  EXTERNAL_RECEIPT_NUM INT NOT NULL,
  SEASON_CODE VARCHAR(10) NULL,
  SEASON_DESCRIPTION VARCHAR(60) NULL,
  SEASON_CODE_EB ENUM('CODE_1', 'CODE_5', 'CODE_0') NULL,
  SEASON_YEAR_EB INT NULL,

  PRIMARY KEY PRI_BARCODE (ID),
  INDEX COLOR_VAR_ID (COLOR_VARIANT_ID),
  UNIQUE KEY UNI_BARCODE (BARCODE_REC_ID, TYPE)
)

Я могу воспроизвести взаимоблокировку с помощью набора входных данных, который создает давление на систему. С этим я поиграл, и как только я удалю уникальный индекс UNI_BARCODE , взаимоблокировок не будет. Кроме того, мои данные каким-то образом не пересекаются, поскольку мы используем группы потребителей из ActiveMQ для принудительной обработки обновлений по порядку для одних и тех же продуктов. Таким образом, по-видимому, уникальный индекс является единственной перекрывающейся вещью, которая вызывает проблемы.

Я потратил некоторое время на это, пытаясь исправить это, но не смог этого сделать: (Я был бы очень признателен за любые идеи по этому поводу 🙂 Заранее спасибо!

Комментарии:

1. Это CHAR(32) MD5? Если это так, имейте в виду, что они приводят к неэффективности в больших таблицах из-за частых скачков.

2. Да, это md5. Я предполагаю, что I использовался, поскольку длина постоянна. Можете ли вы объяснить, что вы подразумеваете под «много прыгать»? 🙂

3. При AUTO_INCREMENT этом «следующая» строка, которую вы вставляете, получает следующий более высокий номер. Если это так PRIMARY KEY , это означает, что следующая строка будет вставлена «рядом» с предыдущей строкой. Напротив, если PK является MD5, он отличается от предыдущего MD5, поэтому он вставляется в какое-то другое место в таблице. Затем…

4. Если вы извлекаете строки из одночасового промежутка времени, auto_inc поместил бы их «рядом» друг с другом, тем самым улучшив работу кэширования данных. Но для MD5 каждая строка вряд ли будет «рядом» с любой из других строк, которые вы извлекаете, тем самым перемещаясь по данным. Это делает кэширование менее эффективным.

5. Итак, в принципе, неплохо использовать значение автоматического увеличения в качестве PK и сохранить md5 в отдельном столбце. Спасибо за информацию 🙂

Ответ №1:

Да, я часто замечал это на своем рабочем месте.

Таблица InnoDB, содержащая как a PRIMARY KEY , так и вторичный UNIQUE KEY , имеет большую вероятность вызвать взаимоблокировку. Пример такого случая четко описан в этом отчете об ошибке: https://bugs.mysql.com/bug.php?id=86812

Похоже, что при блокировке на уровне строк InnoDB существует условие гонки. Я предполагаю, что блокировки уникального индекса и блокировки кластеризованного индекса (первичного ключа) не приобретаются атомарно.

Это влияет как на MySQL, так и на MariaDB, поскольку они оба используют InnoDB. Это влияет на многие версии InnoDB, поэтому обновление не поможет. Это влияет как на уровни изоляции транзакций с фиксацией ЧТЕНИЯ, так и на ПОВТОРЯЕМОЕ ЧТЕНИЕ, поэтому это не поможет изменить это.

Что я посоветовал разработчикам на моем рабочем месте, так это то, что у вас есть три альтернативы:

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

  2. Пессимистическая блокировка. Используйте LOCK TABLES BARCODE WRITE; перед попыткой вставки. И, конечно UNLOCK TABLES; , сразу после этого, чтобы разрешить одновременные сеансы, чтобы получить свою очередь.

  3. Перепроектируйте таблицу так, чтобы в ней не было как первичного, так и уникального ключа. Например, удалите id столбец, чтобы уникальный ключ on (BARCODE_REC_ID, TYPE ) стал кластеризованным индексом.