Listener was going down due to unique constraint violated error
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.
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()
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.
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.
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.