Why does MySQL increment the auto_increment value when there's a constraint violation?

Question

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns.

I noticed that when I try to add a record that violates the constraint, the auto_increment value is still incremented. This seems weird to me, because it just means the value is getting higher while that isn't necessary.

I'm not asking for a way to work around this, but I'm just curious as to why this would be. Is this something that is useful in some cases?

I'm using Mysql 5.5.38-0ubuntu0.14.04.1.

• Vérace: Think of auto_increment like one of those numbering ticket machines that you pull a ticket from when you go into a government office or similar. They don't care what you do with that ticket - if they call out your number and you're there - fine (you're OK with that). But if you've gone for a coffee or a cigarette and your number is called and you miss out - that's your hard luck. The analogy to what you want is that the number should somehow be recycled or that the machine should know in advance that you're a coffee fanatic/heavy smoker - it's not set up that way.
• Rick James: Also happens for INSERT IGNORE, REPLACE, and essentially any other flavor of "insert".

Answer 1

Because the value is incremented outside the scope of the transaction. This is the way you want it to work, so that separate transactions can happily insert rows or roll back without waiting for each other.

If you care about gaps or want different behavior, stop using the built in and roll your own...

• user24821: Thank you, that clears things up. I'm not really concerned with gaps, I just came across this and found it odd behaviour.