MySQL InnoDB зависает при ожидании блокировок на уровне таблицы

У меня есть большое производственное веб-приложение (Glassfish 3.1 + MySQL 5.5). Все таблицы - InnoDB. Один раз в течение нескольких дней приложение полностью зависает. SHOW FULL PROCESSLIST показывает много простых запросов вставки или обновления для разных таблиц, но все имеющие статус

Ожидание блокировки уровня таблицы

Примеры:

update user<br>
set user.hasnewmessages = NAME_CONST('in_flag',_binary'\0' COLLATE 'binary')
where user.id = NAME_CONST('in_uid',66381)

insert into exchanges_itempacks
set packid = NAME_CONST('in_packId',332149), type = NAME_CONST('in_type',1), itemid = NAME_CONST('in_itemId',23710872)

Запросы с самым длинным "временем" ждут блокировки на уровне таблицы. Пожалуйста, помогите выяснить, почему MySQL пытается получить блокировку уровня и что может блокировать все эти таблицы. Все статьи о блокировке InnoDB говорят, что этот движок не использует блокировку таблицы, если вы не заставляете это делать.

Мой my.cnf имеет следующее:

innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2

Двоичный журнал отключен. У меня нет "LOCK TABLES" или других явных команд блокировки вообще. Транзакции READ_UNCOMMITED.

SHOW ENGINE INNODB STATUS вывод: http://avatar-studio.ru:8080/ph/imonout.txt

Ответ 1

Используете ли вы MSQLDump для резервного копирования своей базы данных, пока она еще доступна вашему приложению? Это может привести к такому поведению.

Ответ 2

Я думаю, что есть некоторые ситуации, когда MySQL выполняет полную блокировку таблицы (т.е. использует auto-inc). Я нашел ссылку, которая может вам помочь: http://mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html

Также просмотрите код сохранения Java, который имеет все завершенные/отмененные и закрытые. (Закрытие всегда в блоке finally.)

Попробуйте установить innodb_table_locks=0 в конфигурации MySQL. http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_table_locks

Несколько идей...

Ответ 3

Я вижу, что вы havily используете NAME_CONST в вашем коде. Просто постарайтесь не использовать его. Вы знаете, mysql может быть иногда ошибочным (я также нашел несколько ошибок), поэтому я рекомендую не полагаться на функции, которые не так распространены/хорошо протестированы. Это связано с именами столбцов, поэтому, возможно, это что-то блокирует? Ну, это не так, если это влияет только на результат, но кто знает? Это подозрительно. Более того, это обозначается как функция только для внутреннего использования.

Ответ 4

Это может показаться простым, но у вас нет долгого оператора select, который, возможно, блокирует обновления и вставки? Нет запроса, который действительно работает и не заблокирован?

Ответ 5

Рассматривали ли вы использование MyISAM вместо InnoDB?

Если вы не используете какие-либо транзакционные функции, MyISAM может иметь больше смысла. Его проще, проще оптимизировать, и поскольку он не имеет сложных транзакционных возможностей, проще настроить в my.cnf.

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

Другие предложения:

Это может быть хорошей идеей найти способ не использовать NAME_CONST в вашем SQL. "Эта функция была добавлена в MySQL 5.0.12. Она предназначена только для внутреннего использования." Когда документация на продукт с открытым исходным кодом говорит об этом, вероятно, это хорошая идея, чтобы прислушаться к его рекомендациям.
По умолчанию MySQL хранит все таблицы и схемы InnoDB в 1 огромном файле, может быть какая-то блокировка уровня ОС в этом конкретном файле, который поддерживает MySQL, который предотвращает всю таблицу доступ. Используя параметр innodb_file_per_table, вы можете устранить эту потенциальную проблему. Это также делает MySQL более экономичным.

Ответ 6

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

Я думаю, что этот совет поможет вам более тщательно сконструировать вашу базу данных и решить проблему.