How to define the transaction boundry

Saravanan_Subramanian · June 2, 2020, 12:07am

Flow service A:
Flow service A has one Doc list input variable.

During runtime Doc list contains 4 elements (records).

In A service, I used Loop step to iterate the Doc List. Inside the Loop, I have used try catch sequence steps. In the try block I am invoking the insert JDBC service(Local Transaction).In the catch block appending the error msg from getlasterror in case if any error occurs.

The Input Doc list contains 4 records, assume 1st & 3rd records will get inserted successfully.2nd & 4th record has error.

In the above scenario after the execution of the flow service 1st & 3rd record insertion is commited.

I WANT TO ROLLBACK, if there is a error in one of the record.

Kindly tell me how can we do this? In the above scenario If I have used explicit transaction, then I can rollback the changes. I want to rollback without using explicit transaction.

I read the following in the JDBC manual

LOCAL_TRANSACTION With this transaction type, all of the operations on the same connection in one transaction boundary will be committed or rolled back together. A transaction boundary means the scope of the transaction, from the beginning to the end of a transaction. It can be in one adapter service, one flow service, one Java service, or several steps in a flow service.

Please tell me how to define a transaction boundry.

Thanks
Saravanan S

system · June 2, 2020, 12:07am

You don’t need all of that to accomplish a simple rollback. Remove all those try/catches from inside your loop and remove the explicit transaction, don’t need that either. A single parent level try/catch will handle catching the errors. Remember when you catch an error you have to do something with it correct? If you catch an error and then signal success as you exit the flow service, then your transaction will not auto rollback.

Saravanan_Subramanian · June 2, 2020, 12:07am

Thanks for your reply Mr. Mark Griffin.

If there is any one insert statement is failed then the service should automatically rollback the successful insertions.
I followed your approach and passed FAILURE signal using Exit(flow) step from the catch block, the rollback happened as expected.

By using this approach the service will be exited immediately when the 1st exception arises.

The input doc list has 4 records, 2nd & 4th has the exception. By using the above approach we able to capture the 2nd exception only. After the first run we will fix the problem in the 2nd record then again run the service that time we will come to know that 4th record also has the problem.

During the 1st run itself we need to find all the records which are having the exception.

Is that possible?

system · June 2, 2020, 12:07am

Yep shouldn’t be a problem. First I would try validating the data- ie structure,type etc before doing an insert so you don’t get the errors. I generally don’t like having the JDBC call be my exception handling.

There are multiple ways to solve your issue here is just one. I would probably just stick the JDBC call in a child flow service, wrap it in a try/catch. Each iteration of the loop append the error to a globally defined variable. When your loop is done you can then manually throw the service exception(i use a java service for this) passing in the variable note that this could have to be handled in the try portion or the catch depending on what success of the last record is. This will bring you to the parent catch statement where you can send out your notification with the variable and then you can signal failure and exit which will trigger the rollback. Similar to your first iteration you mentioned except lose the explicit transaction and the addition of the child service.
[HR][/HR]

Saravanan_Subramanian · June 2, 2020, 12:07am

Hi Mark,
Before reading your latest reply I have found one way to solve this issue.

Define the main try catch sequence with exit on as DONE,Failure,DONE.
Under the Failure sequence use a Loop to iterate the Doc list.
one more try catch sequence under the Loop step with exit on as DONE,FAILURE,FAILURE
In the try block invoke the insert JDBC service and append the row number of the successful row.
In the catch block append the row number & the error msg.
In the main catch block find the size of the error doc list, if the size is greater than 0 then Exit the flow service with Signal failure.

By using the above approach we can iterate the whole doc list & we can get the list of successful & error records details.

Thank you for your kind help to find this solution.

Regards
Saravanan S