What design choice (including choice of transactions) and order of steps addresses these requirements?

A Mule application is being designed to do the following:

Step 1: Read a SalesOrder message from a JMS queue, where each SalesOrder consists of a header and a list of SalesOrderLineltems.

Step 2: Insert the SalesOrder header and each SalesOrderLineltem into different tables in an RDBMS. Step 3: Insert the SalesOrder header and the sum of the prices of all its SalesOrderLineltems into a table In a different RDBMS.

No SalesOrder message can be lost and the consistency of all SalesOrder-related information in both RDBMSs must be ensured at all times.

What design choice (including choice of transactions) and order of steps addresses these requirements?
A . 1) Read the JMS message (NOT in an XA transaction)
2) Perform BOTH DB inserts in ONE DB transaction
3) Acknowledge the JMS message
B . 1) Read the JMS message (NOT in an XA transaction)
2) Perform EACH DB insert in a SEPARATE DB transaction
3) Acknowledge the JMS message
C . 1) Read the JMS message in an XA transaction
2) In the SAME XA transaction, perform BOTH DB inserts but do NOT acknowledge the JMS message
D . 1) Read and acknowledge the JMS message (NOT in an XA transaction)
2) In a NEW XA transaction, perform BOTH DB inserts

Answer: A

Explanation:

● Option A says "Perform EACH DB insert in a SEPARATE DB transaction". In this case if first DB insert is successful and second one fails then first insert won’t be rolled back causing inconsistency. This option is ruled out.

● Option D says Perform BOTH DB inserts in ONE DB transaction.

Rule of thumb is when one or more DB connections are required we must use XA transaction as local transactions support only one resource. So this option is also ruled out.

● Option B acknowledges the before DB processing, so message is removed from the queue. In case of system failure at later point, message can’t be retrieved.

● Option C is Valid: Though it says "do not ack JMS message", message will be auto acknowledged at the end of transaction. Here is how we can ensure all components are part of XA transaction: https://docs.mulesoft.com/jms-connector/1.7/jms-transactions

Additional Information about transactions:

● XA Transactions – You can use an XA transaction to group together a series of operations from multiple transactional resources, such as JMS, VM or JDBC resources, into a single, very reliable, global transaction.

● The XA (eXtended Architecture) standard is an X/Open group standard which specifies the interface between a global transaction manager and local transactional resource managers.

The XA protocol defines a 2-phase commit protocol which can be used to more reliably coordinate and sequence a series of "all or nothing" operations across multiple servers, even servers of different types

● Use JMS ack if

C Acknowledgment should occur eventually, perhaps asynchronously

C The performance of the message receipt is paramount

C The message processing is idempotent

C For the choreography portion of the SAGA pattern

● Use JMS transactions

C For all other times in the integration you want to perform an atomic unit of work

C When the unit of work comprises more than the receipt of a single message

C To simply and unify the programming model (begin/commit/rollback)