Блог

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

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

 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

ЧТО?

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

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

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

Во-первых, взаимоблокировки не зависят от явной блокировки. Для возникновения взаимоблокировки не требуется ТАБЛИЦА БЛОКИРОВКИ MySQL или использование режимов изоляции транзакций, отличных от стандартных. У вас все еще могут быть взаимоблокировки, если вы никогда не используете явную транзакцию.

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

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

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

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

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

Выбранные не будут блокироваться (при условии, что вы используете режим изоляции по умолчанию и не используете ДЛЯ ОБНОВЛЕНИЯ), поэтому они не могут быть причиной.

ПОКАЗАТЬ ДВИЖКУ СТАТУС INNODB — ваш друг. Это даст вам кучу (по общему признанию, очень запутанной) информации о взаимоблокировках, в частности, о самой последней.

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

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

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

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

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

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