Why are numbers being skipped in my auto-increment primary key?

Question

Using MySQL 5.1.37

This isn't really a critical issue, one of our biz-dev people just asked about it and I didn't have a good answer. In our users table we have an auto-increment primary key. From time to time, it skips numbers and seems to be doing so at an increasing rate. My original thought was that when two concurrent signups occurred and one was invalid, that if the second signup was in progress while the first was failing, we would see skipped values since the second signup would use the next ID. But this doesn't seem that likely because of the frequency with which it happens. Any ideas?

Answer 1

I think you are on the right lines with the failed setups. It could be the result of transactions “reserving” a primary key and then the transaction being rolled back. This is of course assuming that there is no kind of delete statements being issued on the table