Alex Rivera | Logout

MySQL InnoDB hangs on waiting for table-level locks

Asked 2011-05-31T10:02:36.503
10

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',23710872)

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

java mysql locking innodb
Edit
Report

2 Answers

0

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?

answered 2011-07-09T23:10:59.697
0

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.

answered 2011-07-10T00:46:42.797

Your Answer