Почему я становлюсь тупиком в MySQL

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

Вот таблица:

CREATE TABLE `users_roles` (
  `role_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Затем я запускаю эти 2 запроса в каждом потоке, причем каждый поток имеет другое значение для user_id.

BEGIN;
DELETE FROM `users_roles` WHERE user_id = X;
INSERT INTO `users_roles` VALUES (7, X, NOW()); -- DEADLOCK ON THIS QUERY
COMMIT;

Следует отметить, что user_id X никогда существует в базе данных при вызове оператора DELETE. Бит кода, который запускает эти запросы, используется для создания нового пользователя. Однако функция позволяет мне изменять учетную запись пользователя, и, таким образом, удаляет существующие роли из старой команды пользователей.

Итак, когда достаточное количество этих запросов выполняется параллельно, я начинаю получать взаимоблокировки. Заблокированная часть статуса InnoDB показывает это после каждого тупика.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-05-09 16:02:20 7fbc99e5f700

*** (1) TRANSACTION:
TRANSACTION 6241424274, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 6
MySQL thread id 3772090, OS thread handle 0x7fbc1f451700, query id 4010665755 10.0.141.36 1403_users update

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1331 page no 10665 n bits 192 index `PRIMARY` of table `users_data`.`users_roles` trx id 6241424274 lock_mode X insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 6241424275, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 6
MySQL thread id 3770297, OS thread handle 0x7fbc99e5f700, query id 4010665767 10.0.137.28 1403_users update
INSERT INTO users_roles(role_id, user_id, created) values(5, 102228093, NOW()) ON DUPLICATE KEY UPDATE user_id=user_id

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1331 page no 10665 n bits 192 index `PRIMARY` of table `users_data`.`users_roles` trx id 6241424275 lock_mode X

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1331 page no 10665 n bits 192 index `PRIMARY` of table `users_data`.`users_roles` trx id 6241424275 lock_mode X insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

Что касается отладки или экспериментов с попыткой найти, где проблема на самом деле, я смог избавиться от всех взаимоблокировок, удалив инструкции DELETE из кода. Хотя это и исправить проблему, я хотел бы ее понять.

Я понимаю, как MySQL обрабатывает блокировки пробелов. Я понимаю, что они работают в этой проблеме, потому что строки не существуют, когда я делаю инструкцию DELETE. Я не понимаю, почему обе транзакции в статусе innodb генерируются из одного и того же кода, но только одна из них, транзакция (2) имеет исключительную блокировку. Как будто транзакция (1) даже не пытается получить эту исключительную блокировку (без намерения вставки).

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

Edit:

Я смог воспроизвести это с определенным порядком команд.

Вот таблица:

CREATE TABLE `a` (
  `id` tinyint(3) unsigned NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Вот запросы. Откройте 4 терминала для mysql и выполните запросы в этом порядке.

session 1: BEGIN;
session 2: BEGIN;
session 3: BEGIN;
session 4: BEGIN;
session 1: DELETE FROM `a` WHERE `id` = 5;
session 2: DELETE FROM `a` WHERE `id` = 10;
session 3: DELETE FROM `a` WHERE `id` = 7;
session 4: DELETE FROM `a` WHERE `id` = 12;
session 1: INSERT INTO `a` VALUES (5, 1);
session 2: INSERT INTO `a` VALUES (10, 1); -- deadlock here
session 3: INSERT INTO `a` VALUES (7, 1); -- deadlock here
session 4: INSERT INTO `a` VALUES (12, 1); -- deadlock here

Вот статус InnoDB, непосредственно предшествующий любой из вставок.

------------
TRANSACTIONS
------------
Trx id counter 11396965
Purge done for trx n:o < 11396913 undo n:o < 0 state: running but idle
History list length 1248
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 11396962, ACTIVE 9 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3425, OS thread handle 0x7fcd14197700, query id 29686 localhost dev cleaning up
TABLE LOCK table `matthew`.`a` trx id 11396962 lock mode IX
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396962 lock_mode X
---TRANSACTION 11396961, ACTIVE 10 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3426, OS thread handle 0x7fccda225700, query id 29673 localhost dev cleaning up
TABLE LOCK table `matthew`.`a` trx id 11396961 lock mode IX
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396961 lock_mode X
---TRANSACTION 11396960, ACTIVE 11 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3391, OS thread handle 0x7fccd4d7f700, query id 29672 localhost dev cleaning up
TABLE LOCK table `matthew`.`a` trx id 11396960 lock mode IX
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396960 lock_mode X
---TRANSACTION 11396959, ACTIVE 13 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3392, OS thread handle 0x7fccd4bf9700, query id 29671 localhost dev cleaning up
TABLE LOCK table `matthew`.`a` trx id 11396959 lock mode IX
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396959 lock_mode X

После того, как первая вставка была вызвана, терминал для сеанса 1 зависает при блокировке. Статус InnoDB показывает:

---TRANSACTION 11396959, ACTIVE 841 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 3392, OS thread handle 0x7fccd4bf9700, query id 30234 localhost dev update
------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396959 lock_mode X insert intention waiting
------------------
TABLE LOCK table `matthew`.`a` trx id 11396959 lock mode IX
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396959 lock_mode X
RECORD LOCKS space id 7291 page no 3 n bits 80 index `PRIMARY` of table `matthew`.`a` trx id 11396959 lock_mode X insert intention waiting

Ответы

Ответ 1

Я считаю, что нашел проблему.

Из http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html:

Перед вставкой строки задан тип блокировки замка, называемый блокировкой зазора вставки. Эта блокировка сигнализирует о намерении вставить таким образом, что несколько транзакций, вставляемых в один и тот же промежуток индекса, не должны ждать друг друга, если они не вставляются в одну и ту же позицию в промежутке. Предположим, что есть индексные записи со значениями 4 и 7. Отдельные транзакции, которые пытаются вставить значения 5 и 6, блокируют разрыв между 4 и 7 с блокировками вставки намерений до получения исключительной блокировки на вставленной строке, но не блокировать друг друга, потому что строки не конфликтуют.

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

Вот что это значит для моего случая.

Удаляет, поскольку они не затрагивают строк, все получили общую блокировку (режим IX) в разрыве конца таблицы. После того, как вставка была выполнена, общий замок по-прежнему удерживался всеми потоками, и намерение вставки ожидало освобождения этой общей блокировки.

Решение не должно выполняться параллельно:

  • Удалите строки, которые вы хотите вставить, когда строк там нет.
  • Вставьте строки

Итак, статус двигателя InnoDB был просто неправильным. Он не смог показать, что каждая из транзакций имела один и тот же замок. Он не смог показать, что каждый замок был lock_mode IX, а не X. Он не смог показать, что в каждом потоке также есть блокировка намерений вставки, ожидающая получения. В целом это был довольно впечатляющий сбой SHOW ENGINE INNODB STATUS;.