I want to generate the next enroll_no. It is incremented by group for each institute_id and every new institute has to start enroll_no at 1. When an institute has multiple branches then the problem happens.

If we fetch MAX(enroll_no) and then increment 1 and insert and at the same time branch 2 fetches the same MAX(enroll_no), then after incrementing they both send same enroll_no to the server. This creates a duplicate enroll_no for the institute_id.

I need them to be exclusive and possibly use a lock there but i don't know how to go about that.

stu enrollment table

1 Answer 1


I'm making assumptions here based on the available data. I do not know your table name so I'm going to assume it's called institute_enroll_table for now. I also called your insert variables by fake names myid and my_instutute_id since I don't know what your current insert looks like.

The easiest way to fix this is going to be to do the MAX(enroll_no) in your INSERT query rather than do it as a separate query. The second INSERT will wait on the first and therefore will get a different MAX. The COALESCE makes the value 1 when there is no record found.

INSERT INTO institute_enroll_table(id, institute_id, enroll_no)
SELECT myid, my_institute_id, 
        (SELECT MAX(enroll_no)+1 FROM institute_enroll_table ie WHERE 
     ,1)AS enroll_no

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.