Understanding Atomic Transactions

Question

Hi,

I have one scenario. within an atomic transaction, there are mulitple operations that needs to be performed. Suppose, first transaction is of updating table in DB, second operation of moving file to a location and third is updating table on different sql server.

There occurs an error at third operation. What will happen now? Also what will happen to operations after the operation at which error occured, will they be performed at all or orchestration instance will be suspended.

My understanding says that the first two operations will not get roll back. Is it correct?

If yes then why we say that in atomic transaction operation will complete fully else roll back, no halfway.

Kindly clear this scenario and understanding. Thanks.

---

Ankur Seth

Answer 1

The best answer to your question has been provided by Richard Seroter in this blog post. Refer to it for answer.

---

Please mark the post answered your question as answer, and mark other helpful posts as helpful, it'll help other users who are visiting your thread for the similar problem, Regards -Rohit Sharma (http://rohitt-sharma.blogspot.com/)

Answer 2

Hi Rohit,

Thanks for the link but I am still unclear of what will happen in case physical movement of file is involved in an operation, will that file be deleted as part of rollback.

---

Ankur Seth

Answer 3

I guess they will not be deleted. You would need to add a compensation block and perform the deletion operation.

---

HTH,
Naushad Alam

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
alamnaushad.wordpress.com
My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

Answer 4

Hi Naushad,

If I am correct, I can't have compensation block for an atomic transaction.

Coming back to my scenario, only third operation will roll back, the previous two operations that were successfully completed will not roll back.

---

Ankur Seth

Answer 5

Hi Rohit,

Thanks for the link but I am still unclear of what will happen in case physical movement of file is involved in an operation, will that file be deleted as part of rollback.

---

Ankur Seth

No it will not be deleted and the following text from post make it clear that rollback will not be the case for most of the adapters:

When a message leaves an orchestration through an adapter, we simply publish a message to the messagebox. At that point, the actual adapter process may be running on a completely different machine, on a completely different thread. So in a scenario with two SQL adapters send shapes in an atomic scope, a transmission failure in the second shape does NOT mean that the first database operation automatically gets rolled back.

...

So a moral of the story is that by using delivery notification, and compensating actions, you can make your orchestrations appear to behave transactionally with respect to adapters. And of course this doesn't just apply to the database adapter, but any sort of application/technology adapter. If I replace "SQL Adapter" with "SAP Adapter", the same principles apply. An SAP rollback step would be included in any compensation shape to remove SAP changes if a subsequent action in the atomic scope failed. If you choose to *not* use delivery notification, just be aware of the consequences of adapter transmission failures.

---

Please mark the post answered your question as answer, and mark other helpful posts as helpful, it'll help other users who are visiting your thread for the similar problem, Regards -Rohit Sharma (http://rohitt-sharma.blogspot.com/)

Answer 6

Please watch this webcast from Alan.

Thanks

---

SKGuru

Answer 7

See this a little bit long article. And it does not answer your question as all prviously mentioned materials.

But the answer depends of the adapter you are using. Is it the old SQL adapter, WCF-SQL LOB adapter, or any other data base adapter (MySQL, Oracle)?

The WCF-SQL LOB adapter can turn on/off the "Ambient transaction".

I would suggest to create POC and test. Does adaper include the database transaction in the nested transaction, created by Atomic scope, or not? I don't know the answer.

I hope  the data base adapter could do the rollback for the transaction.

---

Leonid Ganeline [BizTalk MVP] BizTalkien: Naming Convention for BizTalk Solutions

Answer 8

Hi All,

I am getting to an understanding and it is below. Before, I go ahead, let me correct myself for one wrong point I quoted earlier; you have option of compensation block in atomic shape.

Now understanding is:

In case of failure of operation within an atomic transaction it would only be the local variables that would restore to original i.e. previous persisted state.

So if you have successfully performed SQL update or other file drop, before the operation gets failed, the previous operations will not be rolled back beacuase the area for them is outside of atomic scope. Only the local varaibles regarding the previous operations will have values roll back.

Leonid, I will do a POC and share result of what impact adapter will have on atomic transaction.

Thanks.

---

Ankur Seth

Answer 9

Hi

There is a very nice explanation of the use of atomic scope and using compensation block with an example HOW to Compensate a Transaction in a BizTalk Orchestration, Please refer this article, It also discuss the database operation as you have mentioned in your first post. I am sure this article will help you in your POC.

---

HTH,
Naushad Alam

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
alamnaushad.wordpress.com
My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

Answer 10

Hi All,

I worked a bit on POC.

I created schema (Add Generated Items- Add Adapter , SQL and used SP created for data insert, sending data) you will have request-reponse type port generated in orchestration.
Now, for an atomic transaction, we can't have request response send and receive shape. We get an error which is expected:

Error 2 an atomic scope may not contain or call a service or scope that contains both the send and the corresponding receive of a requestresponse operation on a 'uses' port or servicelink C:\Demo Project\Demo BizTalk Server Project1\BizTalk Orchestration.odx 192 22 

So my understanding is that with SQL adapter, insert data operation could not be achieved. Kindly confirm and correct if I have not followed correct steps.

I will continue with WCF-SQL, sqlBinding and WCF-LOB and share result for each scenario. Thanks.

---

Ankur Seth

Answer 11

Ankur,

It is not possible to use request response port in atomic transaction.

Atomic transaction commit at the end of scope. In case of req response you will get dead lock.

Also if you are sending message to n number of parties in atomic transaction, say in loop and after that you are evaluating some value and getting error like divided by zero exception. in that case also no message is going to be transmitted ... Because it use to commit at the end of the scope shape.

As you may be familiar with the persistance point...

In one case,  at the end of send shape..

but if you are sending multiple message , you need to keep all in atomic scope if possible, so that the number of persistance point will be one.

And in atomic transaction at the end of scope only it will persist or commit the thing..

Thanks,

:-)

Answer 12

Hi All,

Even for WCF-SQL, I am not allowed to use to an existing port, the orchestration wizard will give error as :

Operations: SP_InsertDetails
Notes: You cannot choose this port type because it does not have any operations that are compatible with the Send/Receive shape that the port is being created for.

So, now the only option left is to try for WCF-LOB but I don't think even that would allow for a sql update from within atomic transaction scope shape (not sure, will have to confirm).

So, my understanding says, we can't have sql updates from within atomic shape.

Kindly share your understanding also. Thanks.

---

Ankur Seth

Answer 13

Hi Rohit,

Thanks for the link but I am still unclear of what will happen in case physical movement of file is involved in an operation, will that file be deleted as part of rollback.

---

Ankur Seth

No, it will not be deleted. Only the orchestration variables will be restored. If your atomic schope sent the message with SQL port, the SQL transaction will be rollbacked also. But it is not 100%! make sure the SQL operation is nested in the atomic schope transaction.

---

Leonid Ganeline [BizTalk MVP] BizTalkien: Naming Conventions for the BizTalk Solutions

Answer 14

I have developed this issue for DB2 Adapter.

The critical operation that may fails, is connected to a port that the delivery Notification property is set to true. This operation is in an atomic scope.

If an exception is throw, I have a catch block that is defined in a long-running scope which includes the atomic one. With a compensate shape, I launch a roll back in the compensation block attached to the atomic one.

The orchestration is big because there is this system with atomic, compensate and catch blocks for each query to DB2.