Why do holes appear in ID (AI, InnoDB)?

Question

MySQL

There is an ID field, primart key autoincrement.
I insert a line, the numbering goes sequentially 999, 1000.
I delete the last line 1000 and insert a new one and there is already a gap of 100 lines.
That is, the next one, not 1001, as logically, but 1100, and so on.
What could be causing this?

Answer 1

Gaps in an auto-increment field are usually associated with insert queries that were not executed (queries with execution errors, INSERT IGNORE, INSERT ... ON DUPLICATE KEY UPDATE). Before the request, the required number of new identifiers is reserved and the counter in the table increases, if the request has not added a row, then the numbers allocated to it disappear.