title: Unusual MySQL table locking issues
body: <p>We all know that InnoDB updates are row-locking and MyISAM ones are table-locking, but how do you explain this?  Here are the results of a SHOW FULL PROCESSLIST when the problem is in the middle of occurring:</p>
  
  <pre><code>╔═════════╦════════╦═════════╦════════════════════════════════╗
  ║ Command ║ Time   ║ State   ║ Info                           ║
  ╠═════════╬════════╬═════════╬════════════════════════════════╣
  ║ Query   ║ 0      ║ NULL    ║ SHOW FULL PROCESSLIST          ║
  ║ Query   ║ 121    ║ end     ║ UPDATE [InnoDB table]          ║
  ║ Query   ║ 121    ║ update  ║ INSERT INTO [MyISAM table]     ║
  ║ Query   ║ 121    ║ Locked  ║ INSERT INTO [MyISAM table]     ║
  ║ Query   ║ 120    ║ Locked  ║ INSERT INTO [MyISAM table]     ║
  ║ Query   ║ 120    ║ Locked  ║ INSERT INTO [MyISAM table]     ║
  ╚═════════╩════════╩═════════╩════════════════════════════════╝
  </code></pre>
  
  <p>I've left some of the columns out to save space. The above all occurs in one database, and refers to a single InnoDB table and a single MyISAM table. There were many other rows below this similar to the bottom three, but again I've left them out.</p>
  
  <p>The problem is started by the UPDATE on the InnoDB table, which then seems to lock the MyISAM table. The database as a whole isn't locked since there were hundreds of SELECT queries occurring each second and they weren't getting stuck. Note that the state of the first INSERT is 'update', which is presumably what is then causing the subsequent inserts to be locked, but that then begs the question: what is the INSERT updating? The auto-increment? Table indexes? Presumably. But why would such an update be blocked by the UPDATE of a different table? See here for details on what the states mean:</p>
  
  <p><a href="http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html</a></p>
  
  <p>Here's what I've figured out so far:</p>
  
  <ul>
  <li>There's nothing wrong with the queries - the UPDATE is appropriately indexed, and both the INSERT and UPDATE queries normally take &lt; 100 ms to execute, but as you can see this one is taking over 2 minutes.</li>
  <li>Almost all the queries executed on the database are SELECTs and INSERTs - UPDATEs are the exception.</li>
  <li>It's definitely the UPDATE that causes the problem, in that it's semi-replicable - if I execute a similar query, sometimes it recreates the problem, sometimes it executes quickly as normal.</li>
  <li>It's only this particular InnoDB table that seems to be a problem, in that the other InnoDB tables in the database update without a problem, but then it is largest - it contains ~ 1 million rows and is ~ 40 MB in size.</li>
  <li>The UPDATE is replicated over several servers, but the long execution time only seems to occur on those with significant load.</li>
  <li>I've checked the CPU load and free RAM on the server while the problem is occurring and they're both fine.</li>
  </ul>
  
  <p>Server specs:</p>
  
  <ul>
  <li>Debian Squeeze</li>
  <li>MySQL 5.1</li>
  <li>8 cores</li>
  <li>32 GB RAM</li>
  </ul>
  
  <p>Before you ask, here's some of the [mysqld] config settings:</p>
  
  <pre><code>skip-external-locking
  innodb_file_per_table
  innodb_flush_method     = O_DIRECT
  innodb_buffer_pool_size = 512M
  key_buffer              = 1500M
  read_rnd_buffer_size    = 512k
  table_open_cache        = 4096
  tmp_table_size          = 256M
  max_heap_table_size     = 256M
  concurrent_insert       = 2
  max_allowed_packet      = 16M
  thread_stack            = 192K
  thread_cache_size       = 8
  max_connections         = 2000
  open_files_limit        = 60000
  query_cache_limit       = 1M
  query_cache_size        = 512M
  </code></pre>
  
  <p>After extensive googling I'm still at loss, so any help would be gratefully received!</p>
  
  <blockquote>
    <p>Added</p>
  </blockquote>
  
  <p>Create syntax for the InnoDB table:</p>
  
  <pre><code>CREATE TABLE `tablename` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `column1` bigint(20) unsigned NOT NULL,
    `column2` int(10) unsigned NOT NULL,
    `column3` int(10) unsigned NOT NULL,
    `column4` int(10) unsigned NOT NULL,
    `column5` int(10) unsigned DEFAULT NULL,
    `column6` enum('yes','no') NOT NULL DEFAULT 'no',
    PRIMARY KEY (`id`),
    UNIQUE KEY (`column1`),
    KEY (`column2`),
    KEY (`column5`)
  ) ENGINE=InnoDB AUTO_INCREMENT=993266 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
  </code></pre>