ORA-00001: unique constraint Errors when monitoring
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.
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
User monitoring PRPC DB calls on Third Party tool Dynatrace. In the tracer they see huge number of ORA-0001: unique constraint errors.
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.
0% found this useful