0

I have table named - College and Student

Student and College Data

I want to generate unique Enrollment number for the student at the time of registration

In single College there are multiple registrations are possible at the same time For example College - ABC have many persons who can register student

My logic for generate Enrollment id is YY_College-Pk_Last-five-digit-increment

YY_COLFK_DDDDD

At the registration time of student I will first fire Max query like

    select Max(Enrollment_No) from student where College_Fk=101

And get last Enrollment_No and split last five digit and increment by 1 and insert it

When there is a chance to submit two students' data at the same time there is chance of generating single Enrollment_No for two students

How to manage this problem

Panchotiya Vipul
  • 1,226
  • 5
  • 17
  • 42
  • 1
    Use transactions? – chrylis -cautiouslyoptimistic- Mar 25 '19 at 04:36
  • First like chrylis suggested use transaction. Begin Transaction, Commit Transaction. Because in between the table will be lock and the other guy will not receive the number and wait until the lock is clear. So when the time that person get the number it is the next number. Second you can consider of document control number table combine with your transaction. Always pickup from a small document control number table instead of max() from a large size table for performance. get the number from document control number then update it back with increment of 1, so next person get it new number. – AntKoki Mar 25 '19 at 08:23
  • I am current using Hibernate and its give me error like this https://stackoverflow.com/questions/14520243/could-not-commit-hibernate-transaction-nested-exception-is-org-hibernate-transa – Panchotiya Vipul Mar 25 '19 at 08:29

2 Answers2

1

On the Java side of things you could draw some inspiration from concepts such as UUIDs (see https://www.baeldung.com/java-uuid for example).

But as you are using a database, you should rather use the capabilities of that part, see How to generate unique id in MySQL? for some examples.

In other words: the database is your single source of truth. It offers you the ability to have IDs that are guaranteed to be unique!

GhostCat
  • 137,827
  • 25
  • 176
  • 248
0

if you want to add two student data at same time then u must be using insert statement twice, so for each data you have to yo

kapil
  • 1
  • 1