MySQL InnoDB hangs on waiting for table-level locks
Solution 1
Are you using MSQLDump to backup your database while it is still being accessed by your application? This could cause that behaviour.
Solution 2
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 ...
Solution 3
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.
Admin
Updated on July 20, 2022Comments
-
Admin over 1 year
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 statusWaiting 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 -
ovais.tariq over 12 yearsAndrey already has innodb_autoinc_lock_mode=2 which would make sure that no insert-like statements take any auto-inc lock,.
-
runholen about 9 yearsThis was the issue with my production base. I have just read that this may be remedied by using the options —single-transaction --quick for innodb-databases, so I will try that.