title: MySQL InnoDB hangs on waiting for table-level locks
body: <p>I have a big production web-application (Glassfish 3.1 + MySQL 5.5). All tables are InnoDB. Once per several days application totally hangs. 
  <code>SHOW FULL PROCESSLIST</code> shows many simple insert or update queries on different tables but all having status </p>
  
  <blockquote>
    <p>Waiting for table level lock</p>
  </blockquote>
  
  <p>Examples:</p>
  
  <pre><code>update user&lt;br&gt;
  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)
  </code></pre>
  
  <p>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.</p>
  
  <p>My <code>my.cnf</code> has this:</p>
  
  <pre><code>innodb_flush_log_at_trx_commit = 0
  innodb_support_xa = 0
  innodb_locks_unsafe_for_binlog = 1
  innodb_autoinc_lock_mode=2
  </code></pre>
  
  <p>Binary log is off. I have no "LOCK TABLES" or other explicit locking commands at all. Transactions are <code>READ_UNCOMMITED</code>.</p>
  
  <p><code>SHOW ENGINE INNODB STATUS</code> output: 
  <a href="http://avatar-studio.ru:8080/ph/imonout.txt" rel="nofollow noreferrer">http://avatar-studio.ru:8080/ph/imonout.txt</a></p>