auto increment primary leaving gaps in counting

Question

I have got a table with auto increment primary key. This table is meant to store millions of records and I don't need to delete anything for now. The problem is, when new rows are getting inserted, because of some error, the auto increment key is leaving some gaps in the auto increment ids.. For example, after 5, the next id is 8, leaving the gap of 6 and 7. Result of this is when I count the rows, it results 28000, but the max id is 58000. What can be the reason? I am not deleting anything. And how can I fix this issue.

P.S. I am using insert ignore while inserting records so that it doesn't give error when I try to insert duplicate entry in unique column.

Answer 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)

Answer 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