mysql auto-increment; incorrect number sequence after deleting some rows [duplicate]

Solution 1:

This is by design and will always happen.

Why?

Let's take 2 overlapping transaction that are doing INSERTs

• Transaction 1 does an INSERT, gets the value (let's say 42), does more work
• Transaction 2 does an INSERT, gets the value 43, does more work

Then

• Transaction 1 fails. Rolls back. 42 stays unused
• Transaction 2 completes with 43

If consecutive values were guaranteed, every transaction would have to happen one after the other. Not very scalable.

Also see Do Inserted Records Always Receive Contiguous Identity Values (SQL Server but same principle applies)

Solution 2:

You can create a trigger to handle the auto increment as:

CREATE DEFINER=`root`@`localhost` TRIGGER `mytable_before_insert` BEFORE INSERT ON `mytable` FOR EACH ROW 
BEGIN
  SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM mytable);;
END

Solution 3:

This is a problem in the InnoDB, the storage engine of MySQL.

It really isn't a problem as when you check the docs on “AUTO_INCREMENT Handling in InnoDB” it basically says InnoDB uses a special table to do the auto increments at startup

And the query it uses is something like

SELECT MAX(ai_col) FROM t FOR UPDATE;

This improves concurrency without really having an affect on your data.

To not have this use MyISAM instead of InnoDB as storage engine

Solution 4:

Perhaps (I haven't tested this) a solution is to set innodb_autoinc_lock_mode to 0. According to http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html this might make things a bit slower (if you perform inserts of multiple rows in a single query) but should remove gaps.