title: Why do INNODB bulk inserts cause the auto increment primary key to skip values?
body: <p>I am familiar with the fact that failed inserts in InnoDB leads to gaps in an auto-incremented primary key, I believe this is unrelated (it's not obvious to me).  The problem that I am having is that I will insert 5 records into a table with an auto incremented primary key which cause the primary key to have a gap from 5 to 8.  Here is a script to repeat the problem.</p>
  
  <pre><code>DROP TABLE IF EXISTS `test_table`;
  CREATE TABLE `test_table` (
  `A` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `B` int(10) unsigned NOT NULL,
  PRIMARY KEY (`A`)
  ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  
  insert into `test_table` (`B`)
  SELECT 1
  UNION
  SELECT 2
  UNION
  SELECT 3
  UNION
  SELECT 4
  UNION
  SELECT 5;
  
  insert into `test_table` (`B`)
  SELECT 6
  UNION
  SELECT 7
  UNION
  SELECT 8
  UNION
  SELECT 9
  UNION
  SELECT 10;
  
  SELECT * FROM `test_table`;
  </code></pre>
  
  <p>I would expect both columns A and B to be the same in this case, but at the beginning of the second insert A will be 8, and B will be 6.  Does anyone know why this gap occurs?</p>
  
  <p>I've looked at the documentation here: <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html</a> but these lock modes do not seem related to this gap (I could be missing something).</p>