Although transactions of business applications are typically small groups of atomic read and write operations, they can also be complex. For example, in a decentralized Order Entry system, the transaction may contain the following operations:
▪insert a row into CUST_ORDER table
▪insert multiple rows into ORDER_LINE table
▪update USED_CREDIT information of CUSTOMER table
▪insert an entry into the ACCOUNT_TRANSACTION table of the bookkeeping part of the application
▪for each type of product ordered, update the STOCK_BALANCE column of a row of the PRODUCT table to update the warehouse balance of the ordered product
In this example, various things can go wrong when the transaction is executed in the master database. For instance:
▪According to the master database, the customer does not have sufficient credit available for the order. Therefore the entire order is invalid or must be separately approved.
▪There is insufficient inventory given the amount of the products ordered. This makes part of the order incomplete. It may be that the entire order must be put on hold or another order (back order) is required for the missing product.
▪The accounting transaction is invalid because the transaction has been propagated after the month end when entries to the previous month are no longer allowed. This creates a mismatch between corporate bookkeeping and order systems.
There are different approaches to solving these challenges when using solidDB® Intelligent Transaction. Two are discussed here: pre-validation and compensation.
Pre-validation
The individual operations of the transaction can be split into two parts: validation operations and writing operations.
You can implement the transaction so that the validation parts are executed before any of the writing parts. In the parameter bulletin board, the validation parts leave all the necessary information for the writing parts to behave correctly.
In the above example, the validation part of the transaction could look as follows:
At this point, no write operations have been made yet, but the parameter bulletin board now has all the information that the write operations need in order make the whole transaction valid. The rest of the transaction would look as follows:
Another way to solve the above problem is to add compensating operations in the end of the transactions. They can be used, for instance, in a scenario where a product to be ordered in one of the order lines does not exist any more. Therefore the entire order becomes incomplete. However, the row to the ORDER table has already been inserted with STATUS column value “OK”.
In this case, the VALIDATE_AND_INSERT_ORDER_LINE must leave a parameter on the bulletin board that informs the last operation (COMPENSATE_ORDER) of the transaction required to change the status of the order to “INVALID”. The overall Intelligent Transaction implementation could in this example case look as follows: