exclusive lock mysql

Question

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.

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, 
    COALESCE(
        (SELECT MAX(enroll_no)+1 FROM institute_enroll_table ie WHERE 
ie.institute_id=my_institute_id)
     ,1)AS enroll_no