Объясните необъяснимый тупик

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

Это очередь электронной почты:

CREATE TABLE `emails_queue` (
  `id` varchar(40) NOT NULL,
  `email_address` varchar(128) DEFAULT NULL,
  `body` text,
  `status_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('pending','inprocess','sent','discarded','failed') DEFAULT NULL,
  KEY `status` (`status`),
  KEY `status_time` (`status`,`status_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Процесс генерации, в ответ на какое-то действие пользователя, но примерно каждые 90 секунд, вставляет в таблицу, устанавливая статус на "ожидающий".

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

Каждую минуту процесс отправки захватывает все ожидающие письма. Он циклически проходит и по одному электронному адресу заставляет его статус "inprocess", пытается отправить его и, наконец, устанавливает свой статус соответственно "отправлен", "отбрасывается" (у него есть причины для принятия решения по электронной почте не должны выходить ) или "не удалось" (отклонено системой SMTP).

Заявление о настройке статуса необычно.

UPDATE emails_queue SET status=?, status_time=NOW() WHERE id=? AND status = ?

То есть, я только обновляю статус, если текущее состояние уже есть то, что я считаю. Перед этим механизмом я случайно начал два процесса отправки, и каждый из них попытается отправить тот же адрес электронной почты. Теперь, если это произойдет, один процесс успешно переместит электронное письмо с "ожидающего" на "inprocess", но второй будет обновлять нулевые строки, реализовать там проблему и пропустить эту электронную почту.

Проблема в том, что примерно за один раз в 100 обновление полностью завершается! Я получаю com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

WTH?

Это единственная таблица и только запрос, к которому это происходит, и это происходит только в процессе производства (чтобы максимально затруднить его изучение).

Единственные две вещи, которые кажутся совершенно необычными: (1) обновление столбца, участвующего в предложении WHERE, и (2) (неиспользованное) автоматическое обновление состояния_time.

Я ищу любые предложения или диагностические методы.

Ответы

Ответ 1

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

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

Тупики могут даже произойти, если все ваши транзакции просто вставляют одну строку.

Тупик может произойти, если у вас

  • Более одного соединения с базой данных (очевидно)
  • Любая операция, которая внутренне включает в себя несколько блокировок.

Что не очевидно, так это то, что большую часть времени вставка или обновление одной строки включает в себя несколько блокировок. Причина этого в том, что вторичные индексы также необходимо блокировать во время вставок/обновлений.

SELECT не будут блокироваться (если вы используете режим изоляции по умолчанию и не используете FOR UPDATE), поэтому они не могут быть причиной.

SHOW ENGINE INNODB STATUS - ваш друг. Это даст вам кучу (по общему признанию, очень запутанной) информации о тупиках, в частности, самой последней.

  • Вы не можете полностью устранить взаимоблокировки, они будут продолжать происходить в производстве (даже на тестовых системах, если они будут правильно их устранять).
  • Цель для очень малого количества тупиков. Если 1% ваших взаимоблокировок транзакций, возможно, слишком много.
  • Рассмотрите возможность изменения уровня изоляции транзакций ваших транзакций на чтение, если ВЫ ПОЛНОСТЬЮ ПОНИМАЕТЕ ПОСЛЕДСТВИЯ.
  • убедитесь, что ваше программное обеспечение правильно обрабатывает взаимоблокировки.

Ответ 2

С некоторыми серверами базы данных установлены настройки по умолчанию для блокировки. Обычный по умолчанию он использует блокировки (по крайней мере, в системах, которые я использовал). Я не уверен, что это верно для mysql, но я считаю, что это так.

У вас есть индекс в таблице emails_queue? Тип индекса может изменить способ блокировки. В одном случае я имел дело с отсутствием кластеризованного индекса на столе, используйте блокировку страниц вместо блокировки строк. Я прямо сказал ему использовать блокировку строк и он молча изменил его. Блокировка страницы может стать причиной блокировки. Попробуйте проверить этот индекс.

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

Ответ 3

Вы не описали объем транзакций в своем описании. Если каждый описанный вами процесс пытается сделать все в рамках одной транзакции, то, безусловно, существует вероятность тупика в этой системе.

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