В любое время используйте SHOW ENGINE INNODB STATUS для определения причины последнего тупика. Это может помочь настроить приложение для предотвращения тупиков.

Если частые предупреждения о тупиках вызывают беспокойство, соберите более подробные данные отладки, включив переменную innodb_print_all_deadlocks. Информация о каждом тупике, а не только о последнем, записывается в MySQL. Отключите этот параметр после завершения отладки.

Всегда будьте готовы повторно выполнить транзакцию, если она завершилась неудачно из-за тупика. Тупики не опасны. Просто повторите попытку.

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

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

Если вы используете SELECT ... FOR UPDATE или SELECT ... FOR SHARE, попробуйте использовать более низкий уровень изоляции, например, READ COMMITTED.

При модификации нескольких таблиц в рамках одной транзакции или различных наборов строк в одной таблице, выполняйте эти операции в согласованном порядке каждый раз. Тогда транзакции образуют хорошо определённые очереди и не будут образовывать тупики. Например, организуйте операции с базой данных в функции в вашем приложении или вызывайте хранимые процедуры вместо программирования нескольких похожих последовательностей INSERT, UPDATE и DELETE операторов в разных местах.

Добавьте хорошо подобранные индексы в ваши таблицы, чтобы запросы сканировали меньше записей индекса и устанавливали меньше блокировок. Используйте EXPLAIN SELECT, чтобы определить, какие индексы MySQL считает наиболее подходящими для ваших запросов.

Используйте меньше блокировок. Если вы можете позволить SELECT возвращать данные из старой копии, не добавляйте в него клаузу FOR UPDATE или FOR SHARE. Использование уровня изоляции READ COMMITTED здесь хорошо, потому что каждый последовательный чтение в рамках одной транзакции читает из своей новой копии.

Если ничего не помогает, сериализуйте транзакции с помощью блокировок на уровне таблиц. Правильный способ использования LOCK TABLES с транзакционными таблицами, такими как InnoDB таблицы, заключается в том, чтобы начать транзакцию с помощью SET autocommit = 0 (а не START TRANSACTION), а затем выполнить LOCK TABLES, и не вызывать UNLOCK TABLES, пока явно не зафиксируете транзакцию. Например, если вам нужно записать в таблицу t1 и прочитать из таблицы t2, можно сделать так:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

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

Другой способ сериализации транзакций — создание вспомогательной таблицы «семафор», содержащей всего одну строку. Пусть каждая транзакция обновит эту строку перед доступом к другим таблицам. Таким образом, все транзакции будут происходить последовательно. Обратите внимание, что алгоритм обнаружения тупиков в InnoDB в этом случае также работает, так как блокирующая сериализация — это блокировка на уровне строки. При использовании блокировок MySQL на уровне таблиц для разрешения тупиков необходимо использовать метод таймаута.