Obj-Delete produce ERROR
The system designers and developers had built a small number of activities to add/update/delete instance data on a class associated with a secondary database (Data-Admin-DB-Name).
However, while they were able to run the Add activity without any observable problem, when they attempted to run the delete activity this was failing with the error below.
2014-11-24 14:48:29,896 [ WebContainer : 9] [ STANDARD] [ MyApp:01.01.01] (myActivity.MyRuleset.Action) ERROR myserver [email protected] - Error in Obj-Delete
com.pega.pegarules.pub.database.ConnectionException: Database-General Problem deleting an instance from the database 0 enlist: caught Exception
SQL: delete from EXTERNAL_DATA_TABLE where pzInsKey = ?
Caused by SQL Problems.
Problem #1, SQLState null, Error code 0: java.sql.SQLException: enlist: caught Exception
com.ibm.ws.Transaction.IllegalResourceIn2PCTransactionException: Illegal attempt to enlist multiple 1PC XAResources
... 74 more
The underlying application server was using container management in order to commit the SQL operations.
For the Add activity, as this was only updated information in the secondary database schema this was able to handle the transaction appropriately.
However, when performing the Delete activity, this was building performing SQL operations across both schemas. In the secondary schema this was going to delete the matching row from the class table, and in the Pega RULES schema this will add a row to pr_sys_locks to ensure other users are not trying to modify that information while it is being deleted.
In this instance as the underlying data sources weren't using an XA driver the application server was reporting the failure when attempting to manage the container update across two different data sources.
There are potentially two different approaches that can be used to avoid the error when running the activity which will update data on both schemas.
1. Change the database driver to use XA.
This will allow for the container to perform a single managed transaction across more than one data source.
2. Disable the secondary database from being container manager.
While still using a non-XA driver you may wish to choose to disable the secondary data source from being enlisted into the container managed transaction.