В любое время используйте 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 на уровне таблиц для решения тупиковых ситуаций необходимо использовать метод таймаута.