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

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

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

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

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

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

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

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

Используйте меньше блокировок. Если вы можете позволить себе разрешить SELECT возвращать данные из старой копии, не добавляйте к нему клаузу FOR UPDATE или LOCK IN SHARE MODE. Использование уровня изоляции 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 для разрешения тупиков необходимо использовать метод таймаута.