The Transaction Processing feature in the Database V2 connector allows you to commit or roll back multiple inserts, upserts, updates, deletes, and stored procedure actions.
Note:
Transaction processing does not support stored procedures that contain SQL statements that start or end a transaction implicitly or explicitly. You can learn more in our help documentation.
Purpose of the document
This article provides a walkthrough of the Boomi Integration process examples, illustrating a few process designs that leverage the Database V2 connector’s Transaction Processing feature. To install these processes, see here.
Designing Transactions
Users can design processes that support transaction processing by ensuring that those operations that should be executed as a whole have the Join Transaction operation property selected. These operations should be enclosed at the beginning with a START TRANSACTION action and end with a COMMIT TRANSACTION. To ensure that cleanup activities are performed, also add a ROLLBACK TRANSACTION at the end. This generic structure can be used in conjunction with other integration steps as per the needs of the business.
All INSERT, DELETE, UPDATE, UPSERT, and STOREDPROCEDURE Database V2 actions that join a transaction must use the same connection as in the Database v2 step with the START TRANSACTION action. It follows that the Database v2 steps that have the COMMIT TRANSACTION and ROLLBACK TRANSACTION actions should also use the same connection.
Managing multiple documents in a transaction
Know the path of the documents traversal so that you can design your processes to take advantage of transaction processing.
Splitting the documents before or after the branch
Splitting before Try-Catch: Each document path is treated individually because the Try-Catch block receives multiple documents as input. The connector must iterate through all the documents.
Splitting After Try-Catch: All documents are considered together in a single path because the Try-Catch block does not receive any documents as input. Even in this scenario, the connector iterates through all the documents, depending on its implementation. However, the Try-Catch block executes only once since there are no documents to process.
Multiple documents reach the COMMIT TRANSACTION action
When multiple documents reach the COMMIT TRANSACTION action, all of the documents are committed. The transaction is not released after the first document is committed because in the Database v2 connector, there is no iteration over each document. The Database v2 COMMIT TRANSACTION operation commits only once, regardless of the number of documents.
Business Scenario:
Company Name: Acme Corporation
Business Requirement: Acme Corporation wants to build an end-to-end transaction process for their Order Management System. When a new order is placed, the following steps must be executed within a single transaction to ensure data consistency:
- Operation 1: Insert a new order record.
- Operation 2: Update the inventory to reflect the decreased stock.
- Operation 3: Insert a new entry in the financial ledger.
Acme Corporation needs to ensure that either all of these operations succeed together or none of them.
Process 1: Transaction using a Try-Catch Block
When using a Try-Catch step, the operations that should commit or rollback together i.e inserting a new order record, updating the inventory table, and inserting a new entry in the financial ledger are placed in the Try path. Each of these Database v2 steps have the Join Transaction selected. The Database v2 step with the COMMIT TRANSACTION action is placed in the Try path to commit the operations when they execute successfully. In the case of any errors, the Database v2 step with the ROLLBACK TRANSACTION action is placed in the Catch path to undo the operations. This use case processes a single document at a time.
Process Diagram
Process 2: Transaction using a Flow Control step
Use a Flow control step to process multiple documents in batches through a transaction. For example when Acme Corporation wants to process 200 documents in batches of 50, set the Run as Batches of x Documents option in Flow control step to 50. Use START TRANSACTION before the batching begins to initiate a transaction covering all batches. Within the batch processing, use Dynamic Update steps with Join Transaction selected. Use COMMIT TRANSACTION after all batches are processed to commit if all operations succeed. Use ROLLBACK TRANSACTION in the Catch path if any operation fails.
Note: To process each document individually, select the Run Each Document Individually option.
Process Diagram
Process 3: Transaction using Branch step
Shown below is an example of a process that includes a transaction where multiple incoming documents that are required for the transaction are passed directly to the Database v2 step that is joining the transaction.
The Database v2 step with the START TRANSACTION action returns a blank document. This means that if the documents reach the START TRANSACTION, then they are not passed on to the next step. To process the documents that are incoming to the transaction, they should be passed directly to the Database v2 steps that contain the DELETE, INSERT, UPDATE, UPSERT, or STOREDPROCEDURE action that is intended to be executed on those documents.
In this example, a Branch step is leveraged to design a transaction, where the Database v2 step with the START TRANSACTION action is placed on one branch. The documents to be processed traverse to the second branch on which the Database v2 operations that have joined the transaction have been placed within the Try-Catch logic step. This logic step enables the process to have a successful path and the COMMIT TRANSACTION action can be executed and an alternate path for the ROLLBACK TRANSACTION to be executed.
