MySQL InnoDB hangs on waiting for table-level locks

15,088

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.

Share:
15,088
Admin
Author by

Admin

Updated on July 20, 2022

Comments

  • Admin
    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 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

  • ovais.tariq
    ovais.tariq over 12 years
    Andrey already has innodb_autoinc_lock_mode=2 which would make sure that no insert-like statements take any auto-inc lock,.
  • runholen
    runholen about 9 years
    This 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.