Link to home
Start Free TrialLog in
Avatar of Swaminathan_K
Swaminathan_KFlag for India

asked on

Need to handle the Unique Id

Hi ,
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.
ASKER CERTIFIED SOLUTION
Avatar of Shaju Kumbalath
Shaju Kumbalath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Swaminathan_K

ASKER

This solves the probel of transact Id , but , what abt the duplicate data that gets inserted each time we run the proc simultaneously.
I mean , by the above solution, i will have unique Id , but the currsor that fetches the data for the procedure to process will duplicate the entries with unique transact id's
copy proc 1 runs at 10:10:01
By the above solution ,
the transct Id is unique by using a sequence number, Now this copy will have a transact id say 101 , account_no=101 , payment =30
 
Another the copy proc1 runs at 10:10:02
By the above solution ,
The transact id is unique by using a sequence number , Now this copy will have a transact id 102 , account_no=101 , payment=30
Now Iam habing two copies of the same data but with unique Id's , Is there a way to avoid this.
 
 
 
 
Supoose the procedures are not running simultabeoulsy. But one after another, What is the mechanism u use to avoid duplication?
I got it , need to add an exists clause in the dynamic table for the accountno, amount and date combination thanks.
Thanks a lot