Support Article

ORA-00001: unique constraint Errors when monitoring

SA-14749

Summary



One of Pega monitoring tools is piled up with DuplicateKeyExceptions. The "ORA-00001: unique constraint" errors occur when monitoring through Dynatrace third party tool. This kind of exceptions occur when the application web servers are down. User requires to know why the application is getting these kind of alarms. 

Error Messages



ORA-00001: unique constraint (PEGA.PC_ASSIGN_WORKLIST_PK) violated
ORA-00001: unique constraint (PEGA.PR_SYS_LOCKS_PK) violated
ORA-00001: unique constraint (PEGA.UHG_ICARE_WORK_IN) violated
ORA-00001: unique constraint (PEGA.PR_PORTAL_STATE_PK) violated
ORA-00001: unique constraint (PEGA.PR4_RULE_PK) violated


Steps to Reproduce



None.

Root Cause



User monitoring PRPC DB calls on Third Party tool Dynatrace. In the tracer they see huge number of ORA-0001: unique constraint errors.


Resolution



Here’s the explanation for the reported behavior: 

In designing the database access techniques for the PRPC product, the developers had the issue where an in-memory item had been updated and required to be written to the PegaRULES database. 

One standard technique for this situation would be to Select the row from the database and use the resulting sqlcode to determine if an Insert or an Update needed to be done. 

However, to minimize database access where possible, a different technique is chosen:

Try to Insert the row and if the Insert fails due to a duplicate key condition, then issue an Update for the existing row. 
This second technique leads to only one sql statement being issued for every case where the Insert succeeds, reducing the required sql statement processing by some, often significant, percent. 

These failed Insert attempts are handled programmatically in the PRPC code that manages the database.
 
They are not sent back to the application. And normally, they do not produce an exception on the logs. 
 
One can see this exception if third party tools is used, or if DEBUG is
enabled.
 
With this errors  there will not be any performance impact or system crash. Internally it improves the system performance.
 

 
Suggest Edit

Published January 31, 2016 - Updated October 8, 2020


0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.