Mysql – exclusive lock thesql

lockingMySQLphpmyadmin

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.

Best Answer

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

Related Solutions

Sql-server – All-or-none exclusive lock on 2 SQL tables

No, locks are acquired one-after-the-other because the number of locks is generally unpredictable. It might change with row count. This could work differently, but it doesn't.

Your locking technique basically doesn't work. The best workaround I can think of is to write a retry-loop which tries to acquire both locks and retries on deadlock.

MySQL – Insert Without Auto-Commit

A skipped id can happen in several situation. Keep in mind that an INSERT generally starts by allocating all the ids that it may need. Here's a few examples where AUTO_INCREMENT ids are 'burned':

BEGIN; INSERT; ROLLBACK;

INSERT IGNORE -- and some of them are ignored due to dup key

REPLACE -- DELETE, then INSERT

The one I dislike is:

SET autocommit = 0;
INSERT
forget to ever do the COMMIT

For that reason, I avoid autocommit=0.

In a multi-master (including Galera) setup, with auto_increment_increment > 1, the INSERTs on one master skip values.

Best Answer

Related Solutions

Sql-server – All-or-none exclusive lock on 2 SQL tables

MySQL – Insert Without Auto-Commit

Related Question