Un-committed database transactions and auto-increment columns

Question

I encountered some curious behavior today and was wondering if it is expected or standard. We are using Hibernate against MySQL5. During the course of coding I forgot to close a transaction, I presume others can relate.

When I finally closed the transaction, ran the code and checked the table, I noticed the following. All the times I mistakenly ran my code without closing the transaction, which therefore did not result in actual rows being inserted, nevertheless incremented the auto-increment surrogate primary key value, so that I have a gap (i.e. no rows with id field value of 751 to 762).

Is this expected or standard behavior? Might it vary depending on the database? And/or does Hibernate's own transaction abstraction have some possible effect on this?

Answer 1

Yes that's expected.

If you think about it: what else can the database do? If you increment the column and then use that as a foreign key in other inserts within the same transaction and while you're doing that someone else commits then they can't use your value. You'll get a gap.

Sequences in databases like Oracle work much the same way. Once a particular value is requested, whether or not it's then committed doesn't matter. It'll never be reused. And sequences are loosely not absolutely ordered too.

Answer 2

It's pretty much expected behaviour. With out it the db would have to wait for each transaction that has inserted a record to complete before assigning a new id to the next insert.

Answer 3

Yes, this is expected behaviour. This documentation explains it very well.

Beginning with 5.1.22, there are actually three different lock modes that control how concurrent transactions get auto-increment values. But all three will cause gaps for rolled-back transactions (auto-increment values used by the rolled-back transaction will be thrown away).