HOW To Compensate a Transaction in a BizTalk Orchestration

• Download source files - 49 Kb
• Download Database backup - 146 Kb

What is Compensation?

Most of us are aware of an Atomic transaction, which can either be committed or rolled back. A Long Running (L-R) transaction is one which executes for several hours, days or even weeks and contains several atomic transactions. These smaller atomic transactions which are completed/committed, cannot be rolled back and hence need to be compensated. This undo operation which consists of deleting a record, in the case of an insert operation is known as Compensation and such a transaction is known as a Compensatory Transaction.

BizTalk Server and Compensation!

BizTalk provides the capabilities and facilities for the compensation to happen, but the developer is responsible for keeping track of all database changes and performing the appropriate undo operations. NOTE: BizTalk does NOT automatically perform compensation. BizTalk Server 2006 and Visual Studio 2005 has been used in this article.

Compensation - Points to remember...

• A Compensation section is generally written, in order to UNDO the effect of a transaction.
• The Compensation section for a scope gets activated only when the scope terminates normally or the scope completes its execution.
• Unlike exceptions, a compensation section can be written for a "Atomic" as well as a "L-R" transaction.
• A Compensation section needs to be explicitly invoked, using the Compensation Shape in order for it to execute.
• A Compensation section for a scope does NOT automatically undo the effect of a transaction. The compensation section must have the undo logic in place in order to have such an effect.
• A Compensation shape can be used to invoke/execute a compensation section. The control returns to the next operation after compensation shape, once the execution of the compensation section is complete.

Scenario 1 : Employee Leave Request Processing...

Consider a scenario where a Employee leave request needs to be processed. The following are the steps which describe it in more detail...

• An EmployeeLeaveHistory record is inserted into the database as soon as a request arrives in BizTalk.
• The number of leaves requested is calculated from the EmployeeLeaveHistory record and the columns SickLeaveBalance and EarnedLeaveBalance in the Employee table are updated.
• The Employee table update can fail, if the number of leaves in the columns SickLeaveBalance or EarnedLeaveBalance remaining for an employee is zero or less than the requested leaves.
• Once an exception occurs, the EmployeeLeaveHistory record created in the first step needs to be deleted/removed. This is the compensatory transaction.
• The following is the list of Stored Procedures used in this scenario.

• The Stored Procedures are called from a .NET Component called DBLib, which uses a data class known as LeaveHistoryData

• The Leave Application Schema - Observe the structure and the elements of the LeaveApplicationSchema

Leave Compensation Orchestration

In the Leave Compensation orchestration there are three scopes being used...

• Main Long Running Transaction Scope - Parent Scope
• Leave History Record Insertion Atomic Scope - Child Scope
• Employee Record Updation Atomic Scope - Child Scope

The Parent scope is a long running transaction which consists of two Atomic transactions. The First atomic transaction creates a new EmployeeLeaveHistory record. The updation scope updates the columns SickLeaveBalance or EarnedLeaveBalance in the Employee table. The Stored procedure raises an exception when the number of leaves remaining is zero or less than the requested number of leaves. Observe that the exception is caught by the Parent scope and the Compensation Shape invokes the Compensation section of the Leave History Record Insertion Atomic Scope and this section deletes the record or in other words performs an undo operation (Compensatory Transaction).

The RED arrow indicates the flow-of-control from the Compensation Shape to the Compensatory Section.

Event Log messages...

Observe the event log messages...

Sample Example using a Parallel Shape

The point to note in this example is that the exception raised in the Updation Atomic Scope branch does NOT interrupt the Insertion Atomic Scope branch. The exception is raised only afer the Insertion branch completes its execution.

The RED arrow indicates the flow-of-control from the Compensation Shape to the Compensatory Section.

About the Downloadable Code

• Unzip and Restore the database backup file EmployeeDB.zip into the SQL 2005 Database Server on your box. 
• Unzip the BizTalk project zip file with the folder names in the C:\ drive.
• Add the User Environment variable "DBLIB.EMPLOYEEUTIL" with the value as the Database Connection String.
• Place the LeaveRequestMessage in the In folder and check the Event Viewer.

Some Takeaways

1. Set the Synchronized property on the Scope shape to true, in the case you want to handle exceptions.
2. Compensation is an excellent mechanism to achieve database consistency during transactional failures.
3. Compensatory transactions must be widely used in all the transactions involving disparate databases and integration projects.