Resolution:
Description:
============
SQLException ORA-00001: Unique Constraint (BE2_RICARICASIM.I_WORKITEMSU) violated.

Environment:
============
TIBCO BusinessEvents 5.1.1
All Operating Systems

Symptoms:
=========
After configuring the BackingStore, the following exception is thrown when running the TIBCO BusinessEvents Engine.

16:22:14[rkPool.Thread.2] Post RTC error
java.lang.RuntimeException: Action failed even after 10 attempts. Giving up.
    at com.tibco.cep.runtime.service.cluster.txn.RtcTransactionManager.runActions(RtcTransactionManager.java:316)
    at com.tibco.cep.runtime.service.cluster.txn.RtcTransactionManager.applyTransactionToActions(RtcTransactionManager.java:465)
    at com.tibco.cep.runtime.service.cluster.txn.RtcTransactionManager$TxnTask_Actions.run(RtcTransactionManager.java:598)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at com.tibco.cep.runtime.util.CustomBEManagedThread.run(CustomBEManagedThread.java:24)
Caused by: java.sql.SQLException: ORA-00001: unique constraint (BE2_RICARICASIM.I_WORKITEMSU) violated
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)




Cause:
============
An Update or Insert statement attempted to insert a duplicate key. Either the unique key restriction needs to be removed or do not insert the key. An example of how an Insert statement causes the SQLException is illustrated below:

10:58:23[rkPool.Thread.2] INSERT INTO WorkItems(workKey, workQueue, scheduledTime, workStatus, work)  VALUES (prod_ricarica_sim.0.$smtimeouts.373.waiting.-23, prod_ricarica_sim.0.$smtimeouts, 1370854762763, 1, &ltblob>[com.tibco.cep.runtime.service.cluster.agent.tasks.SMTimeoutTask])

10:58:23[rkPool.Thread.2] SQLException while saving work item. Key=prod_ricarica_sim.0.$smtimeouts.373.waiting.-23, scheduler=prod_ricarica_sim.0.$smtimeouts, exception=ORA-00001: unique constraint (BE2_RICARICASIM.I_WORKITEMSU) violated

 

Resolution:
==========
- Change the logConfig to *:all in the CDD file, this will ensure all the post RTC SQL statements are printed out.
- Once we can identify the SQL statement failing with the unique constraint error from the debug logs check the specific table to check if an entry exists with same key. 
- Once we identify the DB table and BE entity validate the application logic resulting in duplicate inserts for same key, make sure you have proper locking implemented to make sure inserts or updates on same key are not allowed in parallel.
- If application logic is clean and locking is implemented properly check the schema setup of your BackingStore and either remove the unique restriction or ensure that it does not get inserted or updated into the BackingStore.

SQLException ORA-00001: Unique Constraint (BE2_RIO.I_WORKITEMSU) violated.