Блокировки при использовании AUTO_INCREMENT столбцов в MySQL Innodb

Новость о том, что разработчики MySQL серьёзно переработали блокировки этого типа и соответственно продвинули свою СУБД по пути масштабируемости, давно уже новостью не является  (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Однако многие стабильные дистрибутивы Linux, например, Ubuntu 8.04LTS включают в себя пакет MySQL server версии 5.0, для которой проблема блокировок AUTO-INC вполне актуальна. Практический пример типичной проблемы и решения.

LAMP+CMS окружение, активный веб-магазин, десятки тысяч хитов в сутки. MySQL 5.032, обычная нагрузка на сервер ~ 300 запросов в секунду. Проблема возникала при выполнении пакетной загрузки данных о товарах PHP-скриптом: 100-200 тысяч записей, во время выполнения нагрузка на сервер ~ 1000 запросов в секунду, в логе выполнения – ошибка:

Deadlock found when trying to get lock; try restarting transaction

MySQL: блокировки и persistent connection

Поскольку в процессе работы сталкиваюсь, кроме Oracle, с более «свежими» вариантами реализации идеи СУБД, в дальнейшем буду описывать чем-то замечательные и недостаточно (imho) описанные особенности реализации серверов MySQL и MS SQL в части производительности.

Итак, MySQL 5.0.67, Innodb, нагруженный LAMP-проект с высокой OLTP-активностью: веб-сайт активно посещается, контент активно дополняется и редактируется. Периодически начали появляться ошибки типа:

MySQL Query Error: UPDATE some_stat_table SET ...
[Lock wait timeout exceeded; try restarting transaction]

как следствие – блокировка работы всего сайта вплоть до перезапуска MySQL сервера специалистами хостера.