mysql innodb表级锁,MySQL InnoDB挂起等待表级锁

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

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.

解决方案

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