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.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.