avoiding concurrent insert multiple same id in mysql

Question

I am facing a problem in my application. I have a table that one field name is registration_no. Before inserting a new record i increment registration_no field by 1 and then insert that incremented registration_no in that table. the problem is when some user concurrently insert data some registration_no value has been same. how can i prevent this.

Answer 1

You want to use a sequence.

Two caveats:

• The AUTO_INCREMENT feature described in the article is non-standard and may give portability issues when moving to a different database.

• If an INSERT is aborted, a number from the sequence is consumed still, so you may end up with holes in the sequence. If that is unacceptable, use an autogenerated sequence for the primary (surrogate) key, and add a separate map from that key to the "official" sequence number, enforcing uniqueness in the index of that table.

The alternative is to enforce UNIQUEness in the database, use an appropriate TRANSACTION ISOLATION LEVEL and add application logic to handle failure to INSERT.