autoincrement column issue in MYSQL We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is generated after 9 instead of 10). 

DROP TEMPORARY TABLE IF EXISTS tt_temp;

CREATE TEMPORARY TABLE tt_temp
   (
      id BIGINT   AUTO_INCREMENT PRIMARY KEY ,  
      value VARCHAR(2)
   )  AUTO_INCREMENT = 1;

   INSERT INTO tt_temp (value) select ('a')  union all select  ('b')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c');
SELECT * from tt_temp;

INSERT INTO tt_temp (value) select ('d')  union all select ('b')  union all select ('c');

SELECT * from tt_temp;


will somebody help me on this.
The actual thing is the variable "innodb_autoinc_lock_mode" which control the behavior for auto_inc lock during generation of auto generated numbers. It can be set to three values which are 0 ,1 or 2. 

The catch is the behavior while setting this, if you set it to 0 which ensures that gap is not produced it held the table level lock and parallel inserts are disallowed while if you set it to 1 or 2 it ensure the lock is not held at table level and it is recommended to be set to 2 as it is fastest and allows parallel insert.

Just to add there may be gaps if there are some deleted rows and auto increment ensures the uniqueness not sequence. 

Hope it helps