Замыкание MySQL, вызванное одновременными INSERT и SELECT

  • Версия MySQL: 5.6
  • Механизм хранения: InnoDB

Тупик произошел, когда две задачи пытались select, а затем insert та же таблица. Процедура выглядит так:

          Task_1       Task_2
          ------      ------
Phase 1 | SELECT      SELECT
Phase 2 | INSERT      INSERT

SELECT count(id) from mytbl where name = 'someValue' and timestampdiff(hour, ts, now()) < 1;
INSERT mytbl (id, name, ts) values ('newId', 'anotherValue', now());

Журнал тупика следующий (с некоторыми деталями усечен):

------------------------
LATEST DETECTED DEADLOCK
------------------------
151225  8:22:17
*** (1) TRANSACTION:
TRANSACTION 0 746402, ACTIVE 0 sec, process no 4690, OS thread id 140411390486272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3909, query id 31751474 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`mytbl` trx id 0 746402 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3906, query id 31751477 10.20.36.38 mydb update
INSERT INTO mytbl  -- truncated
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Вопросы

  • В соответствии с руководством MySQL простой оператор select использует считывание моментальных снимков, которое не требует блокировки S. Оператор insert требует блокировки X для одной строки для вставки. Тогда почему Task_2 удерживал блокировку S и приводил к тупику?

Изменить

Результат SHOW CREATE TABLE следующий:

| task_content | CREATE TABLE `mytbl` (
`id` bigint(20) NOT NULL,
`ts` timestamp NULL DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Ответы

Ответ 1

Если ваш текущий уровень изоляции repeatable read или сильнее, чтобы иметь возможность повторить тот же результат для select count(id) ... в транзакции, MySQL должен заблокировать весь первичный ключ (или часть другого ключа, используемого WHERE состояние). Затем вы изменяете ключ, вставляя новое значение. Но одновременные транзакции изменяют состояние ключа, который уже был замечен. Оба могут начинаться с одного и того же состояния ключа, а затем ждать, пока другой не будет завершен без изменений, чтобы он применил свои собственные изменения.

Ответ 2

где name = 'someValue' и timestampdiff (hour, ts, now()) < 1;

Это довольно неэффективно. Пусть очистит это, чтобы ускорить работу, чтобы уменьшить вероятность тупика.

timestampdiff(hour, ts, now()) < 1 скрывает любой индекс с помощью ts; переписать его на

ts < NOW() - INTERVAL 1 HOUR

Ваши усеченные неожиданными способами; мой говорит "ts старше 1 часа назад", который, как я подозреваю, вы хотели.

Теперь мы можем успешно индексировать ts. Но пусть нести его дальше, используя "составной" индекс:

INDEX(name, ts)

Это позволит эффективно использовать обе части предложения WHERE для поиска строк (строк).

Вы говорите COUNT(id) - это означает, что вам нужно избегать NULLs в id. Возможно, это не проблема, и вы могли бы просто сказать COUNT(*).

Те, кто должен сделать SELECT быстрее. Теперь давайте выясним, почему SELECT и INSERT имеют какое-то отношение друг к другу. Являются ли они в одной транзакции? Или у вас автокоммит выключен, но забыл сказать COMMIT? Пожалуйста, покажите нам всю транзакцию, плюс SHOW CREATE TABLE.

Ответ 3

В статье здесь приведено исчерпывающее объяснение блокировок и уровней изоляции.

Спасибо @newtover за подсказку об уровне изоляции. Мое резюме статьи и ответ на мой вопрос таковы:

Уровень изоляции по умолчанию в InnoDB - Repeatable Read, который блокирует индекс (не блокируя таблицу данных) до конца транзакции.

В моих обстоятельствах единственный индекс PRIMARY, который бесполезен в моем запросе SELECT (может быть проверен explain select...). В результате записи all в индексе PRIMARY были заблокированы. Когда TXN_2 ждал блокировку X для определенной записи, запись была заблокирована блокировкой S, сохраненной TXN_1. Аналогично, TXN_1 ждал блокировку X на другой записи, но запись также была заблокирована блокировкой S, сохраненной сама по себе. A произошел "один S два X" .

В отличие от этого, после того, как я создал индекс name в столбце name, индекс name будет использоваться в операторе SELECT (может быть проверен explain select ...), поэтому блокировки будут выдается по индексу name вместо PRIMARY. Что еще более важно, оператор SELECT выдавал только S-блокировку на запись, равную someValue, а не все записи индекса name. Кроме того, блокировка IX и блокировка X, требуемые INSERT, будут выдаваться по индексу PRIMARY. Конфликты между блокировкой S и блокировкой IX, блокировка X будут решены.

Индекс на столбце name не только ускорил запрос, но еще более существенно предотвратил блокировку всех записей индекса.

Ответ 4

Напишите свой запрос в транзакции BEGIN и END. Надеюсь, этого не произойдет.

Подробнее: здесь

Ответ 5

Письменная часть запроса кажется правильной и, безусловно, ее НЕ источником вашей проблемы. Я предполагаю, что ваши задачи выполняются чередованием, и в начале каждая задача запускает транзакцию. Вы ничего не сказали о том, как выполнять эти задачи и каково значение первичного ключа при каждом выполнении каждого из них? Вы можете изменить поле первичного ключа на AUTO_INCREMENT или убедиться, что первичный ключ, который используется вашими задачами, действительно уникален.
Если это не помогло, альтернативное (но не предлагаемое) решение защитит ваш код вызова процедуры на верхнем уровне с помощью mutext.