Swaminathan_K
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
bUT
Supoose the procedures are not running simultabeoulsy. But one after another, What is the mechanism u use to avoid duplication?
ASKER
I got it , need to add an exists clause in the dynamic table for the accountno, amount and date combination thanks.
ASKER
Thanks a lot
ASKER
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.