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.

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...