I'm working with a MySQL Stored Procedure, which deals with data in a CSV row. There are a lot of DB operations inside the Stored Procedure, where everything is inside a transaction. One call to the stored procedure with the data from the CSV row will handle everything needed to store the CSV row in the database. If something fails, the error is caught and the DB actions are rolled back.

As I expect a huge number of rows in the CSV file, this stored procedure is called batch-wise, in a NodeJS back-end using Promise.all(). Each batch will have, say, 5 stored procedure calls and all these 5 will be executed in parallel.

In the Stored Procedure, I need to insert a row in a table, say, myTable which have a required unique key field, say, myRequiredField.This field is not auto incremented and so I need to find the maximum/previous value of that field in that table, manually increment it and use it for the next insert.

As the calls to the Stored Procedure are made in parallel, all these running instances will get the same value for myRequiredField and when trying to insert the incremented value, the first of five operations succeed and the rest four operations fail, saying that the field is unique.

I've tried to use MySQL's SELECT ... FOR UPDATE so that I can prevent another instance from reading the field but I am not able to do it because the insertion to this table is done inside a loop. From my understanding, the lock initiated by the FOR UPDATE will unlock only when the transaction is committed. In my case, the transaction will commit only after the end of the loop. So, during the second iteration, the lock is still in place and I'm getting a deadlock.

Even though I tried to modify the code to avoid the loop, the execution is constantly falling into deadlock.

Following is the pseudo code of the relevant parts in my SP:

DEFINE i INT DEFAULT 0;
DEFINE maxValueVariable INT;

START TRANSACTION;
-- some other selects and inserts

-- sample loop
WHILE i < 5 DO

-- getting the existing max value
SELECT MAX(myRequiredField) INT maxValueVariable FROM myTable;

-- incrementing and inserting
INSERT INTO myTable(field1, field2, myRequiredField)
VALUES(value1, value2, maxValueVariable +1);

END WHILE;

COMMIT;

In the above code, as there are parallel calls, the parallel instances will get the same max value and the insertion fails with unique constraint error.

I have tried multiple ways to handle this issue. But, couldn't get it fixed. Right now, I stopped parallel calls, and it works fine, but takes 4 times the time to complete. As this is a performance critical operation, I need to solve this issue and run them in parallel.

Any help would be greatly appreciated!