MySQL InnoDB hangs on waiting for table-level locks

I have a big production web-application (Glassfish 3.1 + MySQL 5.5). All tables are InnoDB. Once per several days application totally hangs. SHOW FULL PROCESSLIST shows many simple insert or update queries on different tables but all having status

Waiting for table level lock

Examples:

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',2371087开发者_JAVA百科2)

Queries with the longest 'Time' are waiting for the table-level lock too. Please help to figure out why MySQL tries to get level lock and what can be locking all these tables. All articles about the InnoDB locking say this engine uses no table locking if you don't force it to do so.

My my.cnf has this:

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

Binary log is off. I have no "LOCK TABLES" or other explicit locking commands at all. Transactions are READ_UNCOMMITED.

SHOW ENGINE INNODB STATUS output: http://avatar-studio.ru:8080/ph/imonout.txt

Are you using MSQLDump to backup your database while it is still being accessed by your application? This could cause that behaviour.

I think there are some situations when MySQL does a full table lock (i.e. using auto-inc). I found a link which may help you: http://mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html

Also review java persistence code having all con's commited/rollbacked and closed. (Closing always in finally block.)

Try setting innodb_table_locks=0 in MySQL configuration. http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_table_locks

Just a few ideas ...

I see you havily use NAME_CONST in your code. Just try not to use it. You know, mysql can be sometimes buggy (I also found several bugs), so I recommend don't rely on features which are not so common / well tested. It is related to column names, so maybe it locks something? Well it should't if it affects only the result, but who knows? This is suspicious. Moreover, this is marked as a function for internal use only.

This may seem simple, but you don't have a long-running select statement that is possibly locking out updates and inserts? There's no query that's actually running and not locked?

Have you considered using MyISAM instead of InnoDB?

If you are not utilizing any transactional features, MyISAM might make more sense. Its simpler, easier to optimize, and since it doesn't have sophisticated transactional capabilities, easier to configure in your my.cnf.

Also, depending on the type of db load your app creates, MyISAM might be more appropriate. I prefer MyISAM for read-heavy applications, again, it's easier to configure and understand.

Other suggestions:

• It might be a good idea to find a way to not use NAME_CONST in your SQL. "This function was added in MySQL 5.0.12. It is for internal use only." When the documentation of an open source product says this, its probably a good idea to heed it's advise.

• By default, MySQL stores all InnoDB tables & schemas data in 1 enormous file, there could be some kind of OS level locking on that particular file that propogates to MySQL that prevents all table access. By using the innodb_file_per_table option , you may eliminate that potential issue. This also makes MySQL more space efficient.

in this case you have to create several different database table with same column each other and do not inset more then 3000 row per table, in this case if you want to enter more data into table you have to create another dynamic table(generate table using code) and insert new data into this table and access data from that table. in your condition if more and more table will have to generate then you have to create new database.

i think this tip will help you to design your database more carefully and solve error.