Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Listener was going down due to unique constraint violated error

SA-700

Summary



Users have JMS MDBlisteners running on two nodes both pointing to the same Queue. After 
few minutes, user has noticed that JMS MDBlisteners were going down. 

Error Messages



2014-07-11 16:22:45,853 [ResourceAdapter : 22] [ STANDARD] [ DemoRuleSet:01.01] (WithErrorHandling.Work_.Action) FATAL JMS|JMSServicePackage|JMSClass|DemoService|A121B70CC5FE79458E5DAF14BF76EB4DE - .pyCommitError: ** Database save failed: Tried an insert, then tried an update.
2014-07-11 16:22:45,856 [ResourceAdapter : 22] [ STANDARD] [DemoRuleSet:01.01] (ECMListener.ABC_Int_XYZ_.Action) INFO JMS|JMSServicePackage|JMSClass|DemoService|A121B70CC5FE79458E5DAF14BF76EB4DE - Failed to commit changes to database in Step 11
2014-07-11 16:22:46,387 [ResourceAdapter : 21] [ STANDARD] [DemoRuleSet:01.01] (WithErrorHandling.Work_.Action) ERROR JMS|JMSServicePackage|JMSClass|DemoService|A57FC206E32671B3F6EF24FF48133CAB7 - Error in commit()
com.pega.pegarules.pub.database.DatabaseException: Database-Saver-InsertUpdatefail
From: (A57FC206E32671B3F6EF24FF48133CAB7: DemoService)
SQL: update BATCHNOTIFICATION_TEST set PXCREATEDATETIME = ? , PXCREATEOPERATOR = ? , PXCREATEOPNAME = ? , PXCREATESYSTEMID = ? , PXINSNAME = ? , PXOBJCLASS = ? , PXUPDATEDATETIME = ? , PXUPDATEOPNAME = ? , PXUPDATESYSTEMID = ? , PYLABEL = ? , pzPVStream = ? where pzInsKey = ?
SQL Inserts: <2014-07-11 16:22:43.567> <<null>> <<null>> <pega> <20140101T000000.000 GMT> <ABC-Int-XYZ-Notification-BatchNotificationMessage> <<null>> <<null>> <<null>> <<null>> <<stream>> <ABC-INT-XYZ-NOTIFICATION-BATCHNOTIFICATIONMESSAGE 20140101T000000.000 GMT>
 
Caused by SQL Problems.
Problem #1, SQLState 23000, Error code 1: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (DEMO_WORK.SYS_C0012345) violated
Problem #2, SQLState 23000, Error code 1: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DEMO_WORK.SYS_ C0012345) violated

Steps to Reproduce



Create a 
MDBlistener with service activity calling Obj-Save method for processing records in database having primary key as pxCreateDateTime which does not uniquely identify the row in the table.
Keep the messages in the Queue and restart the MDBlisteners for processing the messages.

Root Cause



Multiple requester sessions were getting created at the same time causing exception in the service activity. Users have Obj-Save method in the service activity and the Primary Key for the table was set as pxCreateDateTime.  After one requestor creates a row in the database, rest of the listener activities tries to create same record, since the pxCreateDateTime was the primary key, rest all activities will fail, leading the listener to down.

Resolution



Suggested user to update the Primary Key definition to include one or more properties that uniquely identify an entity as per their requirement which will help resolve the primary key violation error. This approach has resolved the issue with JMS MDBlisteners.
 

 
Suggest Edit

Published March 21, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

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

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us