0

I would like to ask you a question about MySQL AUTO_INCREMENT.

I already created my own function to generate AUTO_INCREMENT when the user inserts any data input a table in MySQL. This function works very well but I am concerned about the problem which I may face in the future because it is really new to me.

This table is used to store navigation menus and it will store only 400 of rows. Is it possible or not?

0

1 Answer 1

4

Whenever a database has multiple concurrent writers, there are potential race conditions. In particular, if each writer tries to add a new row, they may all choose the same primary key if they're just doing max(...)+1, and then all but one of the inserts will fail.

When a database has an "auto increment" feature, they're not saving you the trouble of writing max(...)+1, they're saving you the trouble of doing so in a perfectly thread-safe way, which may be impossible for you to do yourself if some of the database writers are written in other languages and owned by different developers.

If you only ever have one writer at a time, then this may not cause any problems. But you shouldn't commit to the assumption that you'll never want multiple writers, so let the database do this for you.

9
  • 1
    Most likely the database can also highly optimize generating a new key that's unique without having to find the record with the highest value or comparing against the index and instead simply keep some additional value together with the table that holds the information needed to generate a new key. So even when there is only a single writer it may still be more performant (if that is relevant) Commented Aug 30, 2015 at 17:28
  • But my website back end have only ne user to control it so I think it may have not much transaction I've also think about transaction because if user try to using a lot mysql select max and +1 and try to insert again it will fail Commented Aug 30, 2015 at 17:29
  • Is it possible or not if it is a website and this table used to store only navigation (menus) and it will contain only 200rows? Commented Aug 30, 2015 at 17:30
  • 3
    @Cambodiachannel I honestly can't understand your comments. But regardless of whether you need the thread-safety and performance of the built-in version, it's also simpler, and as far as I know there is no advantage to doing it yourself, so why not use the built-in version?
    – Ixrec
    Commented Aug 30, 2015 at 17:32
  • 1
    @Cambodiachannel with only 400 rows (or even a few thousands wouldn't make much of a difference) there won't be any issue with performance. Any serious database can handle that with ease. Just nobody here really understands why exactly you would want to do it, since it seems to only create extra work without many advantages to it. (unless maybe you would have some special way how you want things to be numbered). You run the risk that your self written solution may fail in the future when additional users are added or the project grows. If this is no concern to you it may be ok. Commented Aug 30, 2015 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.