2

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.

  • 2
    That's expected behaviour. Auto-generated sequences guarantee uniqueness, not being gapless. – ypercubeᵀᴹ Oct 26 '15 at 11:02
  • @ypercube this is strange enough if you do inserts till id 7 then there is no gape for 8th id's onward..can you explain that? – Nawaz Sohail Oct 26 '15 at 11:04
  • 2
    Google "gapless sequences in MySQL" - it's tricky and frequently not worth the effort. – Vérace Oct 26 '15 at 13:32
  • 1
    @NawazSohail not sure, it may not generate as many gaps as the default mode - or none at all. But it will "lock" the inserts to the table and only 1 thread will be able to insert to the table at a time - if I understand the documentation correctly. So not recommended. Deleted rows and hard coded ID values (and maybe rolled back transactions) will still produce gaps. – ypercubeᵀᴹ Oct 26 '15 at 17:44
  • 1
    To explain some gaps: Multi-row inserts, INSERT IGNORE, IODKU, etc, will preallocate the number of ids that it might need. Then it "burns" the ones id did not use due to dup-key, etc. – Rick James Dec 7 '15 at 6:43
2

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

  • Furthermore, if you delete the highest id, then restart the server (or crash), that id will be reused. This is perhaps the non-explicate case of reuse of an AUTO_INCREMENT value. – Rick James Dec 7 '15 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.