Multiple Transaction handling optimum solution

, ,
1

Hi All,

can anyone suggest how do we implement multiple transactions in one service with same connection.

  1. first call procedure
  2. Insert in to Table
  3. call another Procedure

can some one tell me the best way to use three transactions, for every operation i need one transaction boudary.
One failure should not affet other operation…

How would be the solution…like 3 start transactions and 3 commit transactions and only one rollback…?? please suggest the way to achive it

Thanks
prabhakar

2

The practice I follow is for each action that needs a transaction boundary, I create separate FLOW service. In this case, I’d create 3. Each would have the form:

SEQUENCE (exit on success)
…SEQUENCE (exit on failure)
…startTransaction
…
…commitTransaction
…SEQUENCE (exit on done)
…getLastError
…
…rollback
…

Then in your “main” FLOW service, call each of these services. Wrap this in a try/catch if desired.

This approach keeps the services from being too cluttered with a bunch of sequences.

3

Thank you reamon, will try in this way. It seems to be the best optimum way.

4

IF one failure should not affect the other operation then they shouldn’t be in the same flow service at all, don’t make the flow service do more than is necessary to accomplish a given task. If they are related then Rob’s suggestion is the preferred way to handle multiple transactions within the same flow service.

5

I’m afraid (from just looking at it) this will not work since if ‘do DB call’ fails, the transaction ID will not be available in the rollback sequence. startTransaction should be called before the first (exit on success) sequence, not from within the ‘exit on failure’ one.

6

The transaction ID is available in the pipeline document returned by getLastError. That’s what I meant by "extract transaction ID from pipeline–meaning the var, not the pipeline directly.

You can move the startTransaction outside of the “try” sequence if desired. In that case, it will be available directly in the pipeline in the “catch” sequence.