0

I have set my ID column to auto-increment but because my username column is UNIQUE, when a user tries to enter a username that has already been taken it rejects the whole row but when the next person enter information with valid information, because the previous one got rejected, the ID count with go 1,3,4.

Is there a way of stopping id from skipping a number through SQL?

sdf
  • 3
  • 1
  • What backend language are you using? – Lewis Browne Apr 27 '18 at 07:49
  • Why bother? An id is just an id, gaps don't matter. – jarlh Apr 27 '18 at 07:49
  • @jarlh Sometimes it does matter. It was asked by one of my client. In that case I created an stored procedure to handle auto increment by myself. – Sandeep Apr 27 '18 at 07:52
  • 1
    It's *expensive* to provide a sequence with no gaps since you can't hand out any additional values for it until you know the current one has been consumed (or its related transaction has rolled back). That why most database systems don't promise to provide such a thing. If you treat it as a *blob* that happens to fit in a numeric column, you'll be okay. If you attribute *meaning* to the numeric value, you'll probably go wrong sooner or later. – Damien_The_Unbeliever Apr 27 '18 at 07:58
  • 2
    @Sandeep no, it doesn't matter. Even if your client asks you to, it doesn't matter, client had no clue or you used the wrong tool for the task. If you need sequential numbers, do not rely on `auto_increment`. `auto_increment` produces UNIQUE numbers in a highly concurrent environment using sequential algorithm. Its job is not to produce sequential numbers, it never was. Forcing it to do so will incur penalties, huge penalties. Just make peace with these gaps and your life will be fine. – N.B. Apr 27 '18 at 07:59
  • 'Is there a way of stopping id from skipping a number through SQL?' Actually no the auto_increment happens before validation. – P.Salmon Apr 27 '18 at 08:00
  • it seems the expected behavior. one of the workaround is first check if there is duplicate username, if not ,insert. but this requires sequential access. – yaoweijq Apr 27 '18 at 08:05
  • There is no way to have an auto_increment that can't skip numbers. You can make your own sequence generation scheme, but it has a scalability cost. Unless there is a very strong case (like legal requirements), you should probably not attempt to enforce sequential identifiers. – Vatev Apr 27 '18 at 09:02
  • Possible duplicate of [How to set initial value and auto increment in MySQL?](https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql) – Alexander May 12 '18 at 05:00

1 Answers1

0

Is there a way? Well, yes there is. It basically involves writing the update as:

  • Lock the table so no threads can even attempt an insert or update.
  • Find the maximum id in the table and add 1.
  • Insert a new row with the new id.
  • Unlock the table.

In general, databases don't let you lock the entire table. So, you have implement additional mechanisms to handle this -- basically, user a user-implemented semaphore (you can Wikipedia that).

This results in lots of additional, error-prone code, but it can be done. It causes inserts to be much slower, because you are locking threads out of the table for a long time. There is a really good reason why databases don't do this.

Do the gaps have any affect on the performance of the database? Pretty much no effect at all. I suppose there are potential issues with running out of ids, but that issue is rather remove.

If the only cause of the gaps is invalid data, then you can minimize the occurrence by checking the data using a select before doing an insert. This does not "fix" the problem, because two independent threads could still attempt to insert the same data at the same time. In addition, some databases allocate blocks of numbers under some circumstances. I'm not sure if MySQL ever does this.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786