Our Mainframe team is also seeing an INDOUBT recently, which is causing a job to abend
In IBM Database 2 (Db2), an indoubt thread occurs when a participant in a two-phase commit protocol finishes the first phase but loses communication with the commit coordinator. This can happen if the transaction manager (TM) or a resource manager (RM) becomes unavailable after the prepare phase. The indoubt thread then doesn’t know whether to commit or roll back the updates it’s made.
An indoubt thread is a participant in a two-phase commit protocol that has completed the first phase of commit, and has then lost communication with the commit coordinator and does not know whether to commit or roll back the updates that have been made.
The INDOUBT is believed to be related to this datasource pooling from the MuleSoft app.
Since migrating MuleSoft apps from Mule 3 on-prem to Mule 4 RTF, we have had issues with apps that use XA transactions/Datasource pooling (example warning and error below), but we did not have these issues with the Mule 3 on-prem version of the apps. So, we are making changes to the Mule 4 RTF versions to use single resource transactions instead. We have completed this with one app and gotten rid of the warnings and errors below. However, we are having issues with the next app
For each transaction, the app should insert a record into 2 database tables (difference with this app is that the 2 tables to insert into are not on the same database (one Oracle DB and one Mainframe DB), and if any component within the try block throws an exception, neither insert should be committed (should rollback).
Within the Try block (transaction=ALWAYS_BEGIN and transaction type=LOCAL) is the 2 database insert operations (I have tried with all 3 options available for transactional action), however, the second insert throws exceptions like “operation ‘insert’ of extension ‘Database’ uses a transactional connection ‘org.mule.extension.db.internal.domain.connection.oracle.OracleDbConnection’, but the current transaction doesn’t support it and could not be bound”, or “org.mule.runtime.api.exception.DefaultMuleException: Interception execution for operation not ok”.
Code:
<flow name="pix-InsertsForEachFlow" doc:id="80d531b3-8558-4c35-9ea7-5ddc127152f6" >
<logger level="INFO" doc:name="Log inserting records" doc:id="633dc3d0-b107-4ec7-9b4a-e3a3f127d5f1" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Inserting records into databases" />
<set-variable value="#[0]" doc:name="Set targetInsertCount" doc:id="962bbd8d-a248-4a82-b896-f1c68be75247" variableName="targetInsertCount" />
<foreach doc:name="For Each" doc:id="1f8c6f36-c67c-48a8-bc11-c2741d94904d" collection="#[payload]">
<try doc:name="Try" doc:id="367e710d-a258-4f23-82b7-2aa4fa646263" >
<try doc:name="Try" doc:id="9326dcd9-1411-4350-85ce-0a49f9a27664" transactionalAction="ALWAYS_BEGIN">
<logger level="INFO" doc:name="Log inserting into DB2" doc:id="a387a2d2-3bed-458f-9e98-f27617569d2a" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Transaction numbers: #[payload.TRANSACTION_NBR]; Inserting into DB2" />
<until-successful maxRetries="${secure::pix.db2.untilsuccess.maxretry}" doc:name="Until Successful" doc:id="e43d5146-77d6-483a-b0ed-8f885c11bc1a" millisBetweenRetries="${secure::pix.db2.untilsuccess.retryinterval}">
<try doc:name="Try" doc:id="4c0a6c35-8b13-46a4-889f-d2bb86bee4e5" transactionalAction="BEGIN_OR_JOIN">
<db:insert doc:name="Insert to DB2" doc:id="23727a38-baf6-43d2-934a-b0fb31a6210b" config-ref="DB2_Configuration" target="db2Output" queryTimeout="60" transactionalAction="ALWAYS_JOIN">
<db:sql>
<![CDATA[INSERT INTO ${secure::mainframe.db2.schema}.some_DB2_table (columnA, columnB, columnC, columnD) VALUES (:columnAvalue, :columnBvalue, :columnCvalue, :columnDvalue)]]>
</db:sql>
<db:input-parameters>
<![CDATA[#[payload]]]>
</db:input-parameters>
</db:insert>
<error-handler>
<on-error-propagate enableNotifications="true" logException="false" doc:name="On Error Propagate" doc:id="e7fa3560-428d-49c7-91eb-7648e2268efb" />
</error-handler>
</try>
</until-successful>
<logger level="INFO" doc:name="Log insert to DB2 successful" doc:id="a58a024e-7258-4924-97e5-b23cfd9fc263" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Transaction number: #[payload.TRANSACTION_NBR]; Insert into DB2 successful" />
<logger level="INFO" doc:name="Log inserting into EBS" doc:id="c4764198-e908-470f-9b19-1166ec601235" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Transaction numbers: #[payload.TRANSACTION_NBR]; Inserting into EBS" />
<until-successful maxRetries="${secure::pix.oracle.untilsuccess.maxretry}" doc:name="Until Successful" doc:id="df1d66d6-e7d8-477c-a249-425d19b83874" millisBetweenRetries="${secure::pix.oracle.untilsuccess.retryinterval}">
<try doc:name="Try" doc:id="2cb1b4f4-0ec2-470a-abf4-d93ac56180e4" transactionalAction="BEGIN_OR_JOIN">
<db:insert doc:name="Insert to EBS" doc:id="21ceaa8f-994b-4026-a4d5-f90bd9261c0a" config-ref="OracleEBSDBConfig" target="ebsOutput" queryTimeout="60" transactionalAction="ALWAYS_JOIN">
<db:sql>
<![CDATA[INSERT INTO some_oracle_table (columnA, columnB, columnC, columnD) VALUES (:columnAvalue, :columnBvalue, :columnCvalue, :columnDvalue)]]>
</db:sql>
<db:input-parameters>
<![CDATA[#[payload]]]>
</db:input-parameters>
</db:insert>
<error-handler>
<on-error-propagate enableNotifications="true" logException="false" doc:name="On Error Propagate" doc:id="7a7fa8b5-bc2c-44e9-b372-2b7736cf7ae5" />
</error-handler>
</try>
</until-successful>
<logger level="INFO" doc:name="Log insert to EBS successful" doc:id="741fe6c9-a71a-442e-ab56-cf3be2a43841" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Transaction numbers: #[payload.TRANSACTION_NBR]; Insert into EBS successful" />
<ee:transform doc:name="Target insert count" doc:id="a2da2c9e-ea04-4174-aa3a-7fd05d8a3c60">
<ee:message />
<ee:variables>
<ee:set-variable resource="ebsTargetInsertCount.dwl" variableName="targetInsertCount" />
</ee:variables>
</ee:transform>
<logger level="INFO" doc:name="Log database inserts successful" doc:id="ef48d1ca-250e-434f-8f15-4391213eda1d" message="Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Inserts into databases successful" />
<error-handler>
<on-error-propagate enableNotifications="true" logException="true" doc:name="On Error Propagate" doc:id="9cce7f60-9ca3-4970-9dd3-1e1c15f04987" >
<ee:transform doc:name="Error message" doc:id="caeda553-2ed7-4303-9eb8-e935d5a0e05e">
<ee:message></ee:message>
<ee:variables>
<ee:set-variable resource="errorMessage.dwl" variableName="errorMessage" />
</ee:variables>
</ee:transform>
</on-error-propagate>
</error-handler>
</try>
<error-handler >
<on-error-continue enableNotifications="true" logException="true" doc:name="On Error Continue" doc:id="46cd206f-6c05-4b49-8060-b14c394c7d43" >
<logger level="ERROR" doc:name="Log exception" doc:id="33139cdb-49ae-4929-8ebe-7276615618e5" message='Processing #[vars.integrationJob] for #[vars.initiatedProcess] with Message Id #[vars.MSG_ID_VAR] - Exception during insert into databases, transaction number: #[payload.TRANSACTION_NBR ++ "_" ++ payload.SEQUENCE_NBR]; #[( if (error.description contains("com.ibm.db2")) (error.exception.cause.cause.cause.cause.cause.next.next) else error.description)]; Continue with next transaction' />
</on-error-continue>
</error-handler>
</try>
</foreach>
<ee:transform doc:name="Target insert count response" doc:id="767926e2-fc59-4c55-9758-479d42ae7f03">
<ee:message>
<ee:set-payload resource="targetInsertCountResponse.dwl" />
</ee:message>
</ee:transform>
</flow>
Database connectors configuration:
<db:config name="DB2_Configuration" doc:name="Database Config" doc:id="302b750d-17a1-4a06-9c9e-c977661ce963" >
<db:generic-connection url="${secure::mainframe.db2.connString}" driverClassName="com.ibm.db2.jcc.DB2Driver" user="${secure::mainframe.db2.username}" password="${secure::mainframe.db2.password}" >
<reconnection>
<reconnect frequency="30000" count="3" />
</reconnection>
</db:generic-connection>
</db:config>
<db:config name="OracleEBSDBConfig" doc:name="Database Config" doc:id="67f6f833-66f8-49ed-aaf4-de7a2b2e6876" >
<db:oracle-connection host="${secure::mercury.oracle.ebs.db.host}" port="${secure::mercury.oracle.ebs.db.port}" user="${secure::mercury.oracle.ebs.db.username}" password="${secure::mercury.oracle.ebs.db.password}" serviceName="${secure::mercury.oracle.ebs.db.serviceName}" >
<reconnection >
<reconnect frequency="30000" count="3" />
</reconnection>
</db:oracle-connection>
</db:config>
So, the question is, when the multiple database inserts are for different databases like this (Oracle and Mainframe), is XA transactions the only option when needing to rollback the first insert operation if the second insert operation fails?
Example XA pool warning and error mentioned above
WARN TaskScheduler [bitronix-task-scheduler] [event: ]: error running a PoolShrinkingTask scheduled for Sun Apr 05 07:14:52 CST 1970 on an XAPool of resource 1879394061-mrtfp-updateotm-mercuryOraclePooledDataSource with 1 connection(s) (1 still available)bitronix.tm.timer.TaskException
ERROR XAPool [bitronix-task-scheduler] [event: ]: Exception caught when growing the pool. 1 connections might be leakedjava.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:119) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
Tried switching over from XA transaction to single resource transaction.