• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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.
0
Swaminathan_K
Asked:
Swaminathan_K
  • 3
  • 3
1 Solution
 
Shaju KumbalathCommented:
instead of using max(id) use sequence.
Or u can have parameter table where u can have the max value .
when ever u select from that parameter table lock it using for update of clause,
eg:
Step1:
Create table called System Prm
Create Table system_prm(table_nm varchar2(100),column_id number(15));
Step 2:
Create the following procedures

CREATE OR REPLACE PROCEDURE P_POPULATE_PARM ( TABLE_NM SYSTEM_PRM.TABLE_NAME%TYPE)
IS
BEGIN
INSERT
INTO SYSTEM_PRM (TABLE_NAME)
VALUES (TABLE_NM);
END;
/
 
 

CREATE OR REPLACE PROCEDURE getnewid (
table_nm IN VARCHAR2,
new_id OUT NUMBER
)
IS
BEGIN
BEGIN
SELECT (NVL (column_id, 0) + 1)
INTO new_id
FROM system_prm
WHERE UPPER (table_name) = UPPER (table_nm)
FOR UPDATE OF column_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_populate_parm (UPPER (table_nm));
new_id := 1;
END;
UPDATE system_prm
SET column_id = new_id
WHERE UPPER (table_name) = UPPER (table_nm);


END IF;
 
END IF;
END;
/
 
use getnewid for getting the maxvalue
0
 
Swaminathan_KAuthor Commented:
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.
 
 
 
 
0
 
Shaju KumbalathCommented:
bUT
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Shaju KumbalathCommented:
Supoose the procedures are not running simultabeoulsy. But one after another, What is the mechanism u use to avoid duplication?
0
 
Swaminathan_KAuthor Commented:
I got it , need to add an exists clause in the dynamic table for the accountno, amount and date combination thanks.
0
 
Swaminathan_KAuthor Commented:
Thanks a lot
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now