We have procedure called add_payments , this proc inserts the payments for the customer into the dynamic table customer_payments. Each time the proc is run it runs for a set of 100 accounts , all accounts will share the same transact id.
The problem we are facing is when we run two copies of the same proc simultaneously , we are getting duplicate copies with the same transact id and accounts payment info.
In the proc before running , we pick the maximum transact id , then insert the payments for the customers. In this case all the customers would have the same transactid , but the row varies by the customer info.
I tried with the lock table option , but we have a commit after each record that is processed, hence this is not helping me.
Can anyone suggest a solution for this problem.